0

A trick to avoiding filtering by nullable parameters in SQL was something like the following:

select * from customers where (@CustomerName is null or CustomerName = @CustomerName)

This worked well for me in LINQ to SQL:

string customerName = "XYZ";
var results =
   (from c in ctx.Customers 
    where (customerName == null || (customerName != null && c.CustomerName == customerName)) 
    select c);

But that above query, when in ADO.NET EF, doesn't work for me; it should filter by customer name because it exists, but it doesn't. Instead, it's querying all the customer records. Now, this is a simplified example, because I have many fields that I'm utilizing this kind of logic with. But it never actually filters, queries all the records, and causes a timeout exception. But the wierd thing is another query does something similarly, with no issues.

Any ideas why? Seems like a bug to me, or is there a workaround for this? I've since switched to extension methods which works.

Thanks.

Brian Mains
  • 50,520
  • 35
  • 148
  • 257
  • This query is fine (if redundant!), as written. Try it in LINQPad. Your problem is in the code you *didn't* put in the question. – Craig Stuntz Nov 08 '10 at 16:51
  • Redundant yes; actually though, with LINQ to SQL, there were times where if I didn't have a redundant not null check with a nullable type, it would error, and when I put it in, it would work fine. Probably not as much of an issue with a string, but I got in a habit just in case. – Brian Mains Nov 08 '10 at 21:11

2 Answers2

0

Have you tried it with a ternary operator in the where clause?

where (customerName == null ? true : c.CustomerName == customerName)
AJ.
  • 16,368
  • 20
  • 95
  • 150
0

I still didn't figure it out, but rewriting it as a proc fixed the issue, so that was my workaround, as bad as that is.

Brian Mains
  • 50,520
  • 35
  • 148
  • 257