locked
Entity Framework Query Where Clause Being Ignored RRS feed

  • Question

  • User-53740444 posted

    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

    Friday, September 28, 2018 7:11 PM

Answers

  • User-53740444 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, November 25, 2018 6:33 PM

All replies

  • User-821857111 posted

    Combine the where clauses into one method call, just like SQL:

    .Where(s => s.Status == "L" && s.DateCreated >= applicationFromDate)
    Friday, September 28, 2018 7:48 PM
  • User1120430333 posted

    There should be only one Where() using && (And) or || (Or) for multiple Where conditions within the Where() lambda expression.

    Friday, September 28, 2018 9:27 PM
  • User-53740444 posted

    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:

    .Where(s => s.Status == "L" && s.DateCreated >= applicationFromDate && 1==2)

    Friday, September 28, 2018 9:34 PM
  • User1120430333 posted

    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:

    .Where(s => s.Status == "L" && s.DateCreated >= applicationFromDate && 1==2)

    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.

    Saturday, September 29, 2018 2:51 PM
  • User753101303 posted

    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 ?

    Saturday, September 29, 2018 4:00 PM
  • User-53740444 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, November 25, 2018 6:33 PM