子查询实体框架的SQL到LINQ

本文关键字：SQL LINQ 框架 查询 实体 | 更新日期: 2023-09-27 18:16:12

我想把这个SQL转换为LINQ，但我不能编译它。这是我的sql转换

SELECT  u.UserID ,
        A.Username ,
        A.Password ,
        A.CreatedOn
FROM    dbo.tbl_User U
        INNER JOIN dbo.tbl_UserAuthDetail A ON A.UserID = U.UserID
                                               AND A.CreatedOn IN (
                                               SELECT TOP 1
                                                        CreatedOn
                                               FROM     dbo.tbl_UserAuthDetail U2
                                               WHERE    U2.UserID = U.UserID
                                               ORDER BY CreatedOn DESC )

这是我到目前为止的尝试

var q = from u in context.Users
                        join au in context.UserAuthDetails on u.UserID equals au.UserID && 
                        (from au2 in context.UserAuthDetails where au2.UserID == u.UserID orderby au2.CreatedOn descending select au2.CreatedOn).ToList().Contains(au.CreatedOn)

如有任何帮助，不胜感激。

TIA

子查询实体框架的SQL到LINQ

注意:此代码未经测试，但我认为您希望获得每个用户的最新凭据。

var query = context.Users    
            .GroupJoin(context.UserAuthDetails, 
                    u => u.UserID ,     
                    d => d.UserID,   
                  (u, d) => new 
                  { 
                    User = u, 
                    Details = d.OrderByDescending(x => x.CreatedOn).Take(1)
                  });

您可以删除.Take(1)以获得用户的所有记录，并且结果仍然按降序排序。

from u in context.Users
join au in context.UserAuthDetails on u.UserID equals au.UserID && 
                        context.UserAuthDetails.Where(au2 => au2.UserID == u.UserID)
                                        .OrderByDescending(au2 => au2.CreatedOn)
                                        .Select(au => au2.CreatedOn)
                                        //.Take(1) //you have this in SQL
                                        .Any(auc=>auc == au.CreatedOn)
相关文章：