CODEWITHSUNDEEP
c#linqlinq-to-sql
Jeff Camera
Jeff Camera

Reputation: 5554

How can I get LINQ to SQL to use IS NULL when querying with a null variable?

I'm sorry if this has been asked before, but I looked through the related questions and couldn't find anything pertaining to my situation.

I've got a query that looks like this.

var tempFoo = "";
var foo = tempFoo != "" ? tempFoo : null;

var result = Entities.Where(x => x.Bar == foo);

Bar is a string and a nullable varchar

The problem is that when foo is null the SQL generated by LINQ to SQL is:

([t0].[Bar] = @p0)

where is should be:

([t0].[Bar] IS NULL)

If I substitute foo with null in the expression LINQ to SQL uses the correct IS NULL syntax. Sometimes, however, foo isn't null so I have to use a variable.

So how can I get LINQ to SQL to use IS NULL when foo is null and = when foo is not null ?

PS: The reason for the strange variable assignment is because tempFoo is referencing a named regex capture. If the capture is empty the value is "" so I have to check for an empty value and assign null instead.

Thanks!

Upvotes: 1

Views: 2541

Answers (2)

Filipe Cotrim Melo
Filipe Cotrim Melo

Reputation: 121

As the link that Dan posted, to solve you can use:
var result = Entities.Where(x => ((foo == null && x.Bar == null) || (x.Bar == foo));

It works for me!

Upvotes: 1

Dan
Dan

Reputation: 122

There's an article on this topic here: http://blog.linqexchange.com/index.php/how-to-use-is-null-with-linq-to-sql/

Upvotes: 2

Related Questions