LINQ to Entities does not recognize the method 

Question

I'm a newbie to Linq and the below query keeps returning "does not recognize System.DateTime" error. I've tried Parse and Convert and neither works. Here's my query:

mrcEntities context = GetContext();

        var query = from c in context.tblClients
                    where (c.FirstName != null || c.LastName != null)
                       && c.EligibilityDate >= DateTime.Parse("10/01/2011")
                       && c.EligibilityDate <= DateTime.Parse("04/30/2012")
                      orderby c.ClientID
                    select new
                    {
                        ClientID = c.ClientID,
                        FirstName = c.FirstName,
                        LastName = c.LastName,
                        MiddleName = c.MidName,
                        SSN = c.SSN,
                        DOB = c.DOB,
                        Sex = c.Gender,
                        Ethnic = c.EthnicCode
                    };

        clientRowCnt = query.Count();

Any help would be appreciated.

Answer 1

It's because EF can't turn DateTime.Parse into a function available on the store. If you replace the results of the calls to DateTime.Parse() and use those variables in your query it should work fine.

var from = DateTime.Parse("10/01/2011");
var to = DateTime.Parse("04/30/2012");
var query = from c in context.tblClients
                    where (c.FirstName != null || c.LastName != null)
                       && c.EligibilityDate >= from
                       && c.EligibilityDate <= to
                      orderby c.ClientID
                    select new
                    {
                        ClientID = c.ClientID,
                        FirstName = c.FirstName,
                        LastName = c.LastName,
                        MiddleName = c.MidName,
                        SSN = c.SSN,
                        DOB = c.DOB,
                        Sex = c.Gender,
                        Ethnic = c.EthnicCode
                    };

        clientRowCnt = query.Count();

Answer 2

Just parse the date outside of your query. Or use the constructor to create the date without even parsing it (you know the values already it seems.

DateTime start = DateTime.Parse("10/01/2011");
DateTime emd = DateTime.Parse(04/30/2012);

var query = from c.....

Or use:

DateTime start = new DateTime(2011, 10, 01);
DateTime end = new DateTime(2012, 04, 30);

var query = from c.....

You can also safely parse the string in the projection part of your query, since that part is executed on the client side after the data has been retrieved. so

var query = from c...
select
    new {
        EligibilityDate = Datetime.Parse(c.EligibilityDate)
    }

It's only when the parsed value is needed in the selection criteria, when you need to supply it as a DateTime value.

A better solution, by far, is to fix the database schema, or even just add a calculated column to the existing schema which does the parsing on the server and exposes the correct Typed column.