1

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!

Jeff Camera
  • 5,324
  • 5
  • 44
  • 59
  • possible duplicate of [Equivalent of SQL ISNULL in LINQ?](http://stackoverflow.com/questions/413084/equivalent-of-sql-isnull-in-linq) – Andrew Hare Apr 03 '11 at 01:17

2 Answers2

2

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

Dan
  • 122
  • 2
  • 9
  • 1
    Thanks! I really thought there would be a more elegant way than using two different expressions but oh well! – Jeff Camera Apr 03 '11 at 01:25
  • 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. – Tom Padilla Apr 25 '14 at 14:37
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!

Filipe Cotrim Melo
  • 121
  • 5