1

I want to do a query with Lambda expression to get values where result set is between a certain range. The value type string in the database and is used to store date values. In SQL the between operator works with this values but not with Lambda expression. I have used this question here for guidance.

Code:

IQueryable<BankmedStatments> q = db.BankmedStatments.Where(u => DateTime.Parse(u.fServiceDate) >= DateTime.Parse(newStartDate));

Error:

LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String)' method, and this method cannot be translated into a store expression.

What am I missing here?

Community
  • 1
  • 1
Gericke
  • 2,109
  • 9
  • 42
  • 71

2 Answers2

2

You can use SqlFunctions.DateDiff(String, String, String) method to check date difference on server side:

IQueryable<BankmedStatments> q = 
  db.BankmedStatments.Where(u => SqlFunctions.DateDiff("ms", u.fServiceDate, newStartDate) > 0);

In that case Entity Framework will be able to generate SQL query, which will look like:

 SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[fServiceDate] AS [fServiceDate]
 FROM [dbo].[BankmedStatments] AS [Extent1]
 WHERE (DATEDIFF(ms, [Extent1].[fServiceDate], @date)) > 0

Of course, consider to use appropriate data type to store dates in your database.

Sergey Berezovskiy
  • 232,247
  • 41
  • 429
  • 459
1

The problem here is that as you getting an IQueryable, the code is actually executing against the database, and is deferred. So the problem is that SQL does not have a method called DataTime.Parse.

stevethethread
  • 2,524
  • 2
  • 30
  • 29
  • How will I get it to work? – Gericke Jan 03 '14 at 10:31
  • I think the aticle @Vladmir refers to is certainly one option to think about. The problem is that your dates are exposed as strings and you are doing a DateTime.Parse on those. If you expose the properties as Dates, you can eliminate the DateTime.Parse and just use direct direct date comparison, which will work. – stevethethread Jan 03 '14 at 10:37