Vulisha Vulisha - 3 months ago 38
C# Question

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

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.

Answer

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();
Comments