Help needed on linq query

Question

Hi,

This is my SQL table:

i

d     person        datetime        status   description1  description2
----------- -------------------- ----------------------- ---------- --------------- ---------------
1      personA       2010-01-01 20:00:00.000 A     desc1      desc2

2      personA       2010-01-01 21:00:00.000 B     desc3      desc4

3      personA       2010-01-01 19:00:00.000 A     desc5      desc6

4      personB       2010-01-01 20:00:00.000 A     desc7      desc8

5      personB       2010-01-01 21:00:00.000 A     desc9      desc10

7      personB       2010-01-01 18:00:00.000 NULL    desc11     desc12

8      personB       2010-01-02 19:00:00.000 A     desc13     desc14

9      personB       2010-01-02 20:00:00.000 A     desc15     desc16

I'm using entity framework and try to create a LINQ query that returns all data (all columns) for the earliest datetime value per person where status is not equal to 'null'. For the current data set it should return the rows for id (3,4,8)

The SQL query would be something like this:

SELECT A.*
FROM MyTable A
INNER JOIN ( SELECT person, Min([datetime]) as mindate
       FROM MyTable
       WHERE [status] is not null
       GROUP BY person, convert(date, [datetime])) B
on A.person = B.person
and A.datetime = B.mindate

How do I express this in LINQ? Apart from the query as a whole I struggled with the fact that the datetime column allows null values (not shown in the example dataset). This makes the enityframework create a property of nullable type DateTime? which further complicaties my LINQ query.

Thanks in advance!

Answer 1

This probably isn't the most efficient LINQ but does what you want:

var query = 
from person in MyTable
let dates = from p in MyTable where p.Person == person.Person && p.Status != null orderby p.DateTime select p
let earliest = dates.First()
group earliest by earliest.Person into grouped 
select grouped.First();

and easy enough to add the code to handle the nullable date, depending on what you want to do with it.