I am trying to fix someone else's code which has an Entity Framework query which is returning data but is not applying the conditions in the where clause and after spending ages trying different things I just can't work it out as it all seems correct.
This is the code in question:
studentLSFApplications = new List<StudentLSFApplication>();
applicationFromDate = new DateTime(2018, 08, 01);
using (var db = new StudentLSFModel())
{
var dataset = db.StudentApplications
.Include(s => s.Student)
.Include(s => s.vProvidedEvidences)
.Where(s => s.Status == "L")
.Where(s => s.DateCreated >= applicationFromDate)
.OrderBy(s => s.DateCreated).ToList();
//Do various stuff here }
The code is returning records where status is not L and where dates are before the applicationFromDate and the output is not ordered either (although ordering might be a bug elsewhere).
I have tried combining the where queries together into one .Where with && or putting them above the include sections but nothing I do makes any difference. However if I change the >= to == then I get no records at all as there are none for that date so its
like it was half working.
It just isn't making any sense and I have got a bit stuck with it.
Sorry it's taken some time to respond but the issue with the code was that I was attempting to add a where condition on a date field and the web application displayed the values for this field so when I added a where clause for a date range and it was showing
dates outside the date range I thought I must have done something wrong. However the issue turned out to be that the dates being displayed were all actually incorrect as the program was converting these from dates to strings, then back to dates, then doing
this conversion twice more and then converting to a custom type so the values just ended up messed up.
So to sum it up, my where clause was actually working as expected but I thought it wasn't due to errors with the data being displayed.
I fixed the output by just taking it straight from the data model and avoiding all the conversion and now everything works.
Member
19 Points
48 Posts
Entity Framework Query Where Clause Being Ignored
Sep 28, 2018 07:11 PM|robinwilson16|LINK
Hello
I am trying to fix someone else's code which has an Entity Framework query which is returning data but is not applying the conditions in the where clause and after spending ages trying different things I just can't work it out as it all seems correct.
This is the code in question:
studentLSFApplications = new List<StudentLSFApplication>(); applicationFromDate = new DateTime(2018, 08, 01); using (var db = new StudentLSFModel()) { var dataset = db.StudentApplications .Include(s => s.Student) .Include(s => s.vProvidedEvidences) .Where(s => s.Status == "L") .Where(s => s.DateCreated >= applicationFromDate) .OrderBy(s => s.DateCreated).ToList();
//Do various stuff here
}
The code is returning records where status is not L and where dates are before the applicationFromDate and the output is not ordered either (although ordering might be a bug elsewhere).
I have tried combining the where queries together into one .Where with && or putting them above the include sections but nothing I do makes any difference. However if I change the >= to == then I get no records at all as there are none for that date so its like it was half working.
It just isn't making any sense and I have got a bit stuck with it.
Thanks
Robin
All-Star
194434 Points
28074 Posts
Moderator
Re: Entity Framework Query Where Clause Being Ignored
Sep 28, 2018 07:48 PM|Mikesdotnetting|LINK
Combine the where clauses into one method call, just like SQL:
Contributor
4923 Points
4198 Posts
Re: Entity Framework Query Where Clause Being Ignored
Sep 28, 2018 09:27 PM|DA924|LINK
There should be only one Where() using && (And) or || (Or) for multiple Where conditions within the Where() lambda expression.
Member
19 Points
48 Posts
Re: Entity Framework Query Where Clause Being Ignored
Sep 28, 2018 09:34 PM|robinwilson16|LINK
Hello Mike
Thanks I thought it might need to be combined but have tried that way too and it still ignores the where condition and the sort.
However if I add 1 == 2 then it returns nothing so it is obviously partly working:
Contributor
4923 Points
4198 Posts
Re: Entity Framework Query Where Clause Being Ignored
Sep 29, 2018 02:51 PM|DA924|LINK
SELECT * FROM users WHERE username = 'admin' AND 1=0 --' fragment
The 1=0, 1=1, 1=2 equates to a SQL Injection attack, and I would think that in using Linq you couldn't get away with it.
All-Star
48490 Points
18071 Posts
Re: Entity Framework Query Where Clause Being Ignored
Sep 29, 2018 04:00 PM|PatriceSc|LINK
Hi,
Show maybe dataset.Count() and run the same query on the db to see if consistent? Could it be caused by something later in the code ?
Member
19 Points
48 Posts
Re: Entity Framework Query Where Clause Being Ignored
Nov 25, 2018 06:33 PM|robinwilson16|LINK
Hello
Sorry it's taken some time to respond but the issue with the code was that I was attempting to add a where condition on a date field and the web application displayed the values for this field so when I added a where clause for a date range and it was showing dates outside the date range I thought I must have done something wrong. However the issue turned out to be that the dates being displayed were all actually incorrect as the program was converting these from dates to strings, then back to dates, then doing this conversion twice more and then converting to a custom type so the values just ended up messed up.
So to sum it up, my where clause was actually working as expected but I thought it wasn't due to errors with the data being displayed.
I fixed the output by just taking it straight from the data model and avoiding all the conversion and now everything works.
Thanks
Robin