WHERE clause on null in linq

Question

I have two tables which, to simplify, look like this:

TheFiles

FileID | UserID
 23    |   342
 53    |   352

TheData

UserID |  DateCreated
  352  |    7/22/2014
  245  |    7/25/2014  
  589  |    7/28/2014

I'm looking to return a nullable int that represents the latest UserID from the table TheData that's not in the table TheFiles. So for example, with this sample data, the return value would be 589 because it's the latest entry in TheData and it's not in the table TheFiles.

I'm having some trouble on the where when there's no data. This is what I have:

var TheNullableInt = (from d in MyDC.TheData
                      where d.UserID doesn't exist in MyDC.TheFiles
                      orderby d.DateCreated descending
                      select d.UserID).FirstOrDefault();

I'm using linq-to-SQL. How can I do the where clause?

AD.Net · Accepted Answer · 2014-07-30 19:43:50Z

1

from d in MyDC.TheData
     where  !MyDC.TheFiles.Any(tf=>tf.UserID == d.UserID)

or do a join

from d in MyDC.TheData
join tf in MyDC.TheFiles on tf.UserID equals tf.UserID into j
from x in j.DefaultIfEmpty()
where x == null
select.....

answered Jul 30, 2014 at 19:43

AD.Net

13.4k2 gold badges30 silver badges47 bronze badges

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

1 Comment

AD.Net Over a year ago

@frenchie, not me, there are too many downvoters out there! I just upvoted it!

James · Accepted Answer · 2014-07-30 19:46:09Z

1

var lastest = MyDC.TheData.Where(d => !MyDC.TheFiles.Any(f => f.UserID == d.UserID))
                          .OrderByDescending()
                          .FirstOrDefault();

Or if you really want to use LINQ

var latest = (from d in MyDC.TheData
              where !MyDC.TheFiles.Any(f => f.UserID == d.UserID)
              orderby d.DateCreated descending
              select d
             ).FirstOrDefault();

edited Jul 30, 2014 at 19:46

answered Jul 30, 2014 at 19:41

James

82.4k19 gold badges172 silver badges245 bronze badges

2 Comments

frenchie Over a year ago

Great answer; ADNet beat you by just 1 min. Upvoted nonetheless.

James Over a year ago

@frenchie I had actually answered first but I misread the question initially. I thought you had a where clause but you just wanted to handle scenarios where there was no records....my edit meant I re-posted late.

Collectives™ on Stack Overflow

WHERE clause on null in linq

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related