The Problem

For the last few days I've been trying to figure out how to do this SQL in linq:

   1: select from customers where substring(customers.Name, 1, 1) IN ('a', 'b', 'c', 'd');

I had a lot of failed queries, and failed code, before giving up and just looping through the letters for that particular page and manually building up what I wanted.

But that's kind of a hack. OK, its not kind of a hack, its a big hack.

The Solution

This LINQ goodness (using the Customers db in LINQPad) will pull back all the customers who's names begin with a t, d, or j (note the .ToList() on line 4, I'll discuss it next).

   1: string[] letters = { "t", "d", "j" }; 

   2:  

   3: var customers = (from c in Customers

   4:     select c).ToList(); 

   5:  

   6: var results = from c in customers

   7:     join letter in letters on c.Name.First().ToString().ToUpper() equals letter.ToUpper()

   8:     select c; 

   9:  

  10: customers.Dump("Customers");

  11: results.Dump("Filtered by first letter");

And LINQPad renders this for us:

Limitations

LINQ to SQL doesn't support querying a SQL database with if one of your sources is an in-memory store, except if you use the Contains operator.  So, to work around that, you have to pull back ALL the results from the db, and then convert it to a List before you use it in the above query.

I'm my opinion, this is kind of a painful limitation, but for my particular purpose, I'm willing to live with it because my particular database will only ever have around 100 or so rows, so pulling everything back in memory isn't such a big deal...

P.S. if you are reading this in a feed reader, let me know how it renders, I've updated my code snippet plugin to try and fix the previous rendering problems.