SqlQuerry ToList() works but Linq(Iqueryable) does not?

Question

I have weired problem, and don't get it at all. So I have this SqlQuerry

var blogs = context.Meal.SqlQuery("SELECT * FROM dbo.Meal WHERE PersonID=" + id.ToString() + "AND DATEDIFF(day,GETDATE(),Datetime) <= 7 ").ToList();

And that works perfectly, but I tried to transfer that expression to linq, and i can't get ToList() to work

var blogs1 = from c in context.Meal
            where c.PersonID.Equals(id)
            where (DateTime.Now.Date - c.Datetime).Days <= 7
            select c;
List<Meal> blogs = blogs1.ToList();

I get this error:

An unhandled exception of type 'System.ArgumentException' occurred in EntityFramework.SqlServer.dll

Additional information: DbComparisonExpression requires arguments with comparable types.

I googled a lot, tried first with var blogs, then tried with ToList<Meal> tried DateTime.Now but i copied expression from net and then because of Deferred Execution I wrote this

var blogs = (from c in context.Meal
            where c.PersonID.Equals(id)
            where (DateTime.Now.Date - c.Datetime).Days <= 7
            select c).ToList();

But no, it won't work :/ I am using System.Linq, and I read here on StackOverflow that Linq supports Iqueryable ToList. Is it possible that my Linq is wrong it looks simple and i got it from net so it shouldn't be wrong?

If you need any more info type in comments I will add it. Thanks!

EDIT1: Changed .TotalDays to .Days as @garethb suggested, but error persists. EDIT2: I have tried this as Matias suggested

var now = DateTime.Now.Date;
var blogs1 = from c in context.Meal
             where c.PersonID.Equals(id)
             where EntityFunctions.DiffDays(now, c.Datetime) <= 7
             select c;
List<Meal> blogs = blogs1.ToList();

And got this error: (same with SqlFunctions)

An unhandled exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll

Additional information: LINQ to Entities does not recognize the method 'System.Nullable`1[System.Int32] DiffDays(System.Nullable`1[System.DateTime], System.Nullable`1[System.DateTime])' method, and this method cannot be translated into a store expression.

For the finish I accidentally marked this question as duplicate but it isn't. I didn't get where is the problem, but main problem is in that Microsoft changed namespace of DiffDays method from EntityFunctions to DbContext in EF 6.x and that is why my Linq didn't work. In this example it maybe is comparison of dates, but it is on different way, so it is not duplicate.

Does Meal.Datetime allow null values? I think you have some. Also, what is the definition for "now" ? — Wesley Long, Aug 10 '16 at 01:18
@WesleyLong No it doesn't it is auto generated by adding any meal, no user input.There definition for now is added. But I solved the problem. Thanks anyway! — Vulisha, Aug 10 '16 at 01:25
or just compare with 2 dates http://stackoverflow.com/questions/21000287/date-difference-logic-in-linq — Slai, Aug 10 '16 at 02:00

Igor · Answer 1 · 2016-08-10T01:42:48.730

1

// add to top of file
using System.Data.Entity;

// code
var today = DateTime.Now.Date;
var blogs1 = from c in context.Meal
            where c.PersonID == id
            && DbFunctions.DiffDays(today, c.Datetime) <= 7
            select c;
List<Meal> blogs = blogs1.ToList();

See DbFunctions, these are c# expressions that translate into sql server functions.

edited Aug 10 '16 at 01:42

answered Aug 10 '16 at 01:31

Igor

60,821
10
100
175

Tried that to before EntityFunctions, same problem – Vulisha Aug 10 '16 at 01:32
@Vulisha - are you sure its not the `c.PersonID.Equals(id)` ? This should be `c.PersonID == id`. Primitive types are not treated as objects in EF expressions when they are converted to sql statements. – Igor Aug 10 '16 at 01:35
oh i didn't see that, sec I will try it now! – Vulisha Aug 10 '16 at 01:37
@Vulisha - That in combination with the date comparison should fix it. Also you can pic either `EntityFunctions` or `SqlFunctions`, both work on Sql Server but should you ever change the db provider then the former would be better. – Igor Aug 10 '16 at 01:38
again same error as in edit2 – Vulisha Aug 10 '16 at 01:39
@Vulisha - sorry, I have updated my answer. SqlFunctions and EntityFunctions are considered obsolete, use DbFunctions instead. – Igor Aug 10 '16 at 01:43
Nono, i removed that in this try, you can see whole code here https://github.com/Vulisha/CalorieCounter – Vulisha Aug 10 '16 at 01:49
yes yes, I really don't know why they do that stuff... makes developing very very hard. And one more sad news is it still doesn't work, it looks like `DbFunctions.DiffDays(today, c.Datetime) <= 7` does not support `<=` operator it just gets all items – Vulisha Aug 10 '16 at 02:09

score 1 · Accepted Answer · answered Aug 10 '16 at 01:45

You are most likely using EntityFramework 6+. Which means you need to use the DbFunctions class. I have a few checkins I want to do to EF mainline to fix this problem, but for now use this code.

https://msdn.microsoft.com/en-us/library/system.data.entity.dbfunctions.diffdays(v=vs.113).aspx

var today = DateTime.Now.Date;
var blogs1 = from c in context.Meal
             where c.PersonID.Equals(id)
             where DbFunctions.DiffDays(today, c.Datetime) <= 7
             select c;
List<Meal> blogs = blogs1.ToList();

Yes, this is it! it works now, it does count all data currently but I will bypass that minor bug, but Linq works finaly, thank you a lot Aron! — Vulisha, Aug 10 '16 at 01:54

SqlQuerry ToList() works but Linq(Iqueryable) does not?

2 Answers2