Where is the "LEFT" operator in LINQ?

Question

Using SQL Server 2005 I've run a query like this

SELECT * FROM mytable WHERE (LEFT (title, 1) BETWEEN @PREFIXFROM AND @PREFIXTO)

I use this to do alphabet filtering, so for example

PREFIXFROM = a PREFIXTO = c and I get all the items in mytable between a and c (inclusive)

How do I do this in linq?

Selecting all the records fine.. but 1) how do I do the "LEFT" operation and 2) How do I do a <= type operator with a non numeric field?

Any ideas appreciated!

Answer 1

Don't think of the SQL - think of what you're trying to achieve, and how you'd do it in .NET. So you're trying to get the first character, and work out whether it's between 'a' and 'c':

var query = from row in mytable
            where row.title[0] >= 'a' && row.title[0] <= 'c'
            select row;

or in dot notation:

var query = mytable.Where(row => row.title[0] >= 'a' && row.title[0] <= 'c');

Alternatively:

var query = mytable.Where(row => row.title.Substring(0, 1).CompareTo("a") >= 0 &&
                                 row.title.Substring(0, 1).CompareTo("c") <= 0));