Linq not finding matching record that is there

Question

I'm trying to find a record that I know exist in the database. When searching for it using this query nothing is found.

List<OrganizationALE> ales =
                _ctx.OrganizationALEs.Where(c => c.OrganizationId.Equals(organizationId) && c.LastModified.Equals(modified) && c.StartDate.Equals(start)).ToList();

But when I search using this query it is clearly there in the result using watch. enter image description here

And if I try pull it out on the very next line using

var found = ales.First(a => a.LastModified == modified);

I get an exception say Sequence contains no matching element

Have you tried inspecting the resulting SQL statement and run it manually against the database? — Dave Swersky, Sep 09 '13 at 20:23
Yes, it would be good if you can monitor the database session and get the SQL statement generated. — Hugo Hilário, Sep 09 '13 at 20:24

score 2 · Accepted Answer · edited May 23 '17 at 12:30

I bet it's because modified and/or start contains milliseconds that are not stored with the same precision in the database (datetime in SQL Server?) so that the comparison fails in the database.

Possible solutions are here (the second and third point apply to your situation). Basically supply less precise DateTime values (cut off the millisecond) or use datetime2(7) in SQL Server or avoid using equality comparisons with DateTime values and use >= and <= instead.

Linq not finding matching record that is there

1 Answers1