SQL到LINQ -左连接从同一个表使用值等于和大于

本文关键字:大于 同一个 LINQ 连接 SQL | 更新日期: 2023-09-27 18:16:36

我有以下SQL查询,我试图转换为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 

我已经尝试了以下,但我没有得到正确的数据回来。

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;

是否有人能够帮助得到这个SQL查询转换为LINQ正确?

SQL到LINQ -左连接从同一个表使用值等于和大于

   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 ;