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

Question

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!

possible duplicate of Equivalent of SQL ISNULL in LINQ?

Andrew Hare
– Andrew Hare

2011-04-03 01:17:46 +00:00
Commented Apr 3, 2011 at 1:17 — Andrew Hare
– Andrew Hare, Commented Apr 3, 2011 at 1:17

Dan · Accepted Answer · 2011-04-03 01:14:30Z

2

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

answered Apr 3, 2011 at 1:14

Dan

1222 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff Camera Over a year ago

Thanks! I really thought there would be a more elegant way than using two different expressions but oh well!

Tom Padilla Over a year ago

The link is dead and the other question mentioned above does not address my issue. The best I can gather from the above comment and the answer below is the need for two different LINQ statements - one if "foo" is null and a second if "foo" is not.

Filipe Cotrim Melo · Accepted Answer · 2014-02-04 15:03:05Z

1

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!

answered Feb 4, 2014 at 15:03

Filipe Cotrim Melo

1215 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related