Linq to entities - first letter of string between 2 keys

Question

I'm using Entity Framework and have come across an issue with the lack of support for certain extensions.

I have 2 keys (both char) fromKey and toKey and am looking to construct a Where statement that will search a list of Stores, and select where the first letter of Store.Name is between fromKey and toKey.

string[0] does not work, Substring does not work - anyone got any ideas how to get round this?

Code for substring method:

public static IQueryable<Store> FindActiveByName()
{
    var r = new ReadRepo<Store>(Local.Items.Uow.Context);

    Tuple<string, string> range = Tuple.Create("0", "9");
    return r.Find().Where(s => range.Item1 <= s.Name.Substring(0, 1) &&
                               s.Name.Substring(0, 1) <= range.Item2);
}

Gives me an error:

Operator <= cannot be applied to string

UPDATE

Anwser provided by nemesV

    public static IQueryable<Store> FindActiveByName()
    {
        Tuple<char, char> range = Tuple.Create('R', 'S');

        var r = new ReadRepo<Store>(Local.Items.Uow.Context);

        var keys = Enumerable
            .Range(range.Item1, (int)range.Item2 - (int)range.Item1 + 1)
            .Select(k => ((char)k).ToString());

        return r.Find(s => keys.Contains(s.Name.Substring(0, 1)));
    }

Answer 1

I don't have EF at hand so maybe this is also not working, but you can try the following workaround:

First generate a string with the from to letters. And then you can use Contains to check whether the first character of the Name is in the generated letters:

public static IQueryable<Store> FindActiveByName()
{
    var r = new ReadRepo<Store>(Local.Items.Uow.Context);

    Tuple<char, char> range = Tuple.Create('0', '9');

    var letters = Enumerable
                     .Range(range.Item1, (int)range.Item2 - (int)range.Item1 + 1)
                     .Select(x => ((char)x).ToString()

    return r.Find().Where(s => letters.Contains(s.Name.Substring(0, 1)));
}

Or you have the obviously working workaround:

• Write the query in SQL (EF has API to execute any arbitrary query and build entities form it)
• Insert a .ToArray() before the Where which will force the query evaluation and the Name.Substring comparison will be done on client side.