CODEWITHSUNDEEP
c#linqlinq-to-entities
ChrisCurrie
ChrisCurrie

Reputation: 1639

Adding WHERE to a Simple LINQ Query returns zero results (but shouldn't)

I've got the following query: 

var Query = db.ReportCommon
              .Where(x => x.ReportType == category)
              .Where(x => x.ReportDateTime >= dateStart)
              .Where(x => x.ReportDateTime < dateEnd);

category is a variable that I pass in (i.e. 'Short', 'Standard' etc); dateStart and dateEnd are DateTime values.

This query returns results as expected (approximately 300 odd).

But when I add the following line I get zero results when in reality I should get approximately 2 or 3 less results:

              .Where(x => x.PartnerRef.ToUpper() != "TEST");

There are only about 3 entries where the PartnerRef field does contain 'Test' or 'test' or 'TEST' the others are either NULL or contain different Partner Refs (like 'DSW').

Why is this happening and how can I fix it?

Upvotes: 1

Views: 175

Answers (4)

James Gaunt
James Gaunt

Reputation: 14793

You need to check the SQL generated, but in SQL terms NULL != "TEST" evaluates to UNKNOWN, not TRUE, and so those results wouldn't be returned.

You may be hoping that EF is clever enough to spot this pitfall and emit NULL checks into the SQL, but given everything in your question it may appear that it isn't.

Can't explain why the other PartnerRef values aren't being returned - it all points to some other external factor you haven't identified in the question.

Upvotes: 1

Jim Wooley
Jim Wooley

Reputation: 10418

LINQ to Databases doesn't need the .ToUpper because sql queries are not case sensitive. You might try profiling the generated SQL to find out why your query is failing. Post your SQL if you still need help.

Upvotes: 0

Jeff Ogata
Jeff Ogata

Reputation: 57823

Are you sure the values of category, dateStart, and dateEnd are the same?

Also, if you use

.Where(x => x.PartnerRef.ToUpper() == "TEST");

do you get the 2 or 3 records you expect?

You might also want to try running this in LINQPad, which will allow you to experment and see the SQL that EF is generating.

Upvotes: 1

Svarog
Svarog

Reputation: 2218

I'm not sure what is happening with your code. However, if in a certain entry "PartnerRef" is null, than PartnerRef.ToUpper() would throw a null reference exception.

Can it be you are handling that exception somewhere, but result remains empty, because the query was never fulfilled?

Upvotes: 0

Related Questions