9

I have a Query in SQL Server : 

SELECT * FROM MyTable t
WHERE ISNULL(t.Status,'') = ''

How I can do it in Entity Framework?

EDIT: Oh Sorry my code was like 

WHERE ISNULL(t.Status,'') = ''
Usman Khalid
  • 3,032
  • 9
  • 41
  • 66
  • 4
    You just need `WHERE t.Status = 'CO'`. The `ISNULL` does not affect the semantics and makes it unsargable. – Martin Smith Dec 19 '13 at 14:02
  • @MartinSmith yeah I understand that but I mistakenly submitted the question as WHERE ISNULL(t.Status,'') = 'CO', Actually it was WHERE ISNULL(t.Status,'') = '' – Usman Khalid Dec 22 '13 at 04:28
  • That's still unsargable. You should just use `WHERE t.Status = '' OR t.Status IS NULL` – Martin Smith Dec 22 '13 at 15:43
  • I am not working with SQL Statement. I just used it as an example here. I needed Entity Framework statement. I got it. Anyhow Thanks. – Usman Khalid Dec 23 '13 at 05:18
  • 1
    Granted this is useless in a `WHERE` clause, but it's mighty handy in `ORDER BY` where `NULL` should be treated as greater-than-anything-else. Since googling the general question of "EF SQL isnull()" (and probably variants) gives this as a top result, I'll throw in my +1 to balance the scales. – Marc L. Sep 16 '16 at 16:02

2 Answers2

26

Try something like

MyTable.Where( t => (t.Status ?? "") == "CO" )

TJ Bandrowsky
  • 842
  • 8
  • 12
  • This WHERE t.Status = 'CO' should be fine. Why you need to check for ?? (null) ? – Prasad Kanaparthi Dec 19 '13 at 14:12
  • Although it is unnecessary to do so, it is what the OP requested. I don't think it generates `ISNULL` but `COALESCE(...)` but that shouldn't matter. – Silvermind Dec 19 '13 at 14:32
  • It's to guard against NULL propagation. In this example the OP could get away with it, but it just seems like better practice. – TJ Bandrowsky Dec 19 '13 at 14:45
  • 2
    It is definitely not better practice. It can convert an efficient seek into a full table scan. Unfortunately [Code analysis rule SR0007](http://stackoverflow.com/a/7471822/73226) does (or at least did) recommend it which is maybe why the OP has it. – Martin Smith Dec 19 '13 at 14:50
  • 1
    It's not the best for fast results, that's true. But, null propogation in unexpected places can mean broken results. The real fix is to not use NULLs on searchable columns. – TJ Bandrowsky Dec 19 '13 at 16:07
  • 1
    The real fix is to understand the technologies you are using. Three valued logic is not difficult to get to grips with. – Martin Smith Dec 19 '13 at 16:38
  • Null is not a value, but the absence of one. – TJ Bandrowsky Dec 19 '13 at 17:59
6

Although the question is ok, the logic isn't sound. Because if a value is equal to CO, it can never be equal to either NULL or ''. In this case you could just easily call it like this:

SQL: 

SELECT * FROM MyTable t
WHERE t.Status = 'CO'

Linq:

var items = (from t in db.MyTable
             where t.Status == "CO"
             select t);

However if you would need it to have a default value when NULL and compare to that value it would make more sense (see example):

SQL:

SELECT * FROM MyTable t
WHERE ISNULL(t.Status, 'CO') = 'CO'

Linq:

var items = (from t in db.MyTable
             where (t.Status ?? "CO") == "CO"
             select t);

This would give you all items where t.Status is NULL or equal to CO. This is, of course, just an example.

Note: The generated sql would probably be slightly different, but the result is the same. It would probably look something like this:

SELECT * FROM MyTable t
WHERE COALESCE(t.Status, 'CO') = 'CO'
Silvermind
  • 5,791
  • 2
  • 24
  • 44