CODEWITHSUNDEEP
c#sqllinqjoinleft-join
Bomo_HS
Bomo_HS

Reputation: 13

SQL to LINQ - left join from same table using values equal to and greater than

I have the following SQL query which I am trying to convert to LINQ.

SELECT   t1.* 
FROM   table1 t1 
LEFT JOIN   table1 t2 
ON  (t1.MusicId = t2.MusicId AND t1.MusicDetailId > t2.MusicDetailId) 
WHERE t2.MusicDetailId IS NULL and t1.SingerId = 2
ORDER BY t1.MusicId

I have tried the following but I am not getting the correct data back. 

var query =
    from   t1 in table1
    from t2 in table1
    where t1.MusicId == t2.MusicId && t1.MusicDetailId > t2.MusicDetailId 
    where t1.SingerId == 2 && t2.MusicDetailId == null
    orderby t1.MusicId 
    select t1;

Is anyone able to help to get this SQL query converted to LINQ correctly?

Upvotes: 0

Views: 497

Answers (1)

Rakin
Rakin

Reputation: 1279

   var query = from t1 in table1.Where(X=> X.SingerId == 2)
                    join t2 in table1.Where(X=>X.MusicDetailId ==null) on t1.MusicId  equals t2.MusicId 
where  t1.MusicDetailId > t2.MusicDetailId
                    select t1 ;

Upvotes: 1

Related Questions