LINQ Left Join on Max Date

Question

Ok so here are my tables in the database:

CREATE DATABASE Temp  
GO --------------------------  
USE Temp  
GO --------------------------  
CREATE TABLE Table1  
    (  
      Table1Id INT IDENTITY(1, 1) ,  
      Name VARCHAR(20) ,  
      CONSTRAINT pk_Table1 PRIMARY KEY ( Table1Id )  
    )  
GO --------------------------  
    CREATE TABLE Table2  
        (  
          Table2Id INT IDENTITY(1, 1) ,  
          Table1Id INT ,  
          NAME VARCHAR(20) ,  
          TheDate SMALLDATETIME ,  
          CONSTRAINT pk_Table2 PRIMARY KEY ( Table2Id ) ,  
          CONSTRAINT fk_Table2_Table1 FOREIGN KEY ( Table1Id ) REFERENCES Table1 ( Table1Id )  
        )  
    GO --------------------------  
    INSERT  INTO Table1  
            ( Name )  
    VALUES  ( 'Stack Overflow' )  
    GO --------------------------  
    INSERT  INTO Table1  
            ( Name )  
    VALUES  ( 'Expert Sex Change' )  
    GO --------------------------  
    INSERT  INTO Table1  
            ( Name )  
    VALUES  ( 'Code Project' )  
    GO --------------------------  
    INSERT  INTO dbo.Table2  
            ( Table1Id ,  
              NAME ,  
              TheDate   
            )  
    VALUES  ( 1 ,  
              'S1' ,  
              '11-01-2012'  
            )  
    GO --------------------------  
    INSERT  INTO dbo.Table2  
            ( Table1Id ,  
              NAME ,  
              TheDate   
            )  
    VALUES  ( 1 ,  
              'S2' ,  
              '11-01-2013'  
            )  
    GO --------------------------  
    INSERT  INTO dbo.Table2  
            ( Table1Id ,  
              NAME ,  
              TheDate   
            )  
    VALUES  ( 2 ,  
              'E1' ,  
              '10-01-2013'   
            )

And here's my LINQ:

from t1 in Table1s 
     join t2 in Table2s.OrderByDescending(x => x.TheDate)
        on t1.Table1Id equals t2.Table1Id into tt
     from t2 in tt.DefaultIfEmpty()
     select new 
     {
        t1.Table1Id,
        t1.Name,
        t2.NAME,
        t2.TheDate
     }

This one returns:

Table1Id - Name - NAME - TheDate
1 - Stack Overflow - S2 - 11/1/2013
2 - Expert Sex Change - E1 - 10/1/2013
1 - Stack Overflow - S1 - 11/1/2012
3 - Code Project - null - null

I want the LINQ query not to return the third line, as is from an older Date Value.

@Proka Any filter for dates, i mean on what basis you want to filter out your result i don't see any `Where` condition in your query ? — Suraj Singh, Nov 13 '13 at 08:49

score 0 · Answer 1 · answered Nov 13 '13 at 08:58

I think I got it, the answer is:

from    t1 in Table1s 
    join t2 in Table2s
    on t1.Table1Id equals t2.Table1Id 
    into tt
    from x in tt.DefaultIfEmpty()
    // where ... t1 && x ..
    orderby t1.Table1Id
    group x by new {t1.Table1Id,t1.Name} into g

    select new {
    Table1Id = g.Key.Table1Id,
    Name = g.Key.Name,
    TheDate = g.Max(c => c.TheDate)
    }

LINQ Left Join on Max Date

1 Answers1