c# - LINQ 查询以包含来自子表的一行的字段

Question

我正在尝试弄清楚如何使用 LINQ 查询从主实体返回详细信息，以及从 1 对多(或 0..1 对多)子实体的一行中返回详细信息实体。

这里的细节只是一个简化的例子。这更像是我正在寻找的概念。 Entity Framework 类可以定义为:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public string Department { get; set; }

    public ICollection<Login> Logins { get; set; }
}

public class Login
{
    public int Id { get; set; }
    public DateTime LoginTimestamp { get; set; }
    public string IpAddress { get; set; }
    public string UserAgent { get; set; }

    public virtual User User { get; set; }
}

所以开始查询，我想要这样的东西:

var results = from u in Users
    select new {u.Id, u.Name, u.Email, u.Department}

到目前为止一切都很好。假设我想添加上次登录日期和时间:

var results = from u in Users
    select new {u.Id, u.Name, u.Email, u.Department, u.Logins.Max(LoginTimestamp)}

还是不错的。但现在我想从最新的登录记录中添加更多详细信息。假设我还想包括上次登录的 IP 地址。我知道查询将不得不进行重大更改并使用“Max”以外的其他内容，但我不确定如何更改。加入？子查询？

我看到过这样的引用:

var results = from u in Users
    select new {u.Id, u.Name, u.Email, u.Department, 
        u.Logins.Max(LoginTimestamp), 
        u.Logins.OrderByDescending(LoginTimestamp).FirstOrDefault().IPAddress}

...但我怀疑那是非常低效的，而且对于从子实体添加的每个进一步的列，效率越来越低。

最好/最有效的方法是什么？

明确一点，我只希望每个用户在结果集中有一行，仅包含上次登录的详细信息。

谢谢

2016 年 6 月 16 日编辑:

我已经构建了这个示例，并使用 LINQPad 来测试查询。

对于这个 LINQ 查询:

from u in Users
    select new {u.Id, u.Name, u.Email, u.Department, 
        LastLogin = u.Logins.OrderByDescending(l => l.LoginTimestamp).FirstOrDefault().LoginTimestamp, 
        LastIP = u.Logins.OrderByDescending(l => l.LoginTimestamp).FirstOrDefault().IpAddress}

...它产生的SQL是...

SELECT 
    [Project2].[Id] AS [Id], 
    [Project2].[Name] AS [Name], 
    [Project2].[Email] AS [Email], 
    [Project2].[Department] AS [Department], 
    [Project2].[C1] AS [C1], 
    [Limit2].[IpAddress] AS [IpAddress]
    FROM   (SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name], 
        [Extent1].[Email] AS [Email], 
        [Extent1].[Department] AS [Department], 
        (SELECT TOP (1) [Project1].[LoginTimestamp] AS [LoginTimestamp]
            FROM ( SELECT 
                [Extent2].[LoginTimestamp] AS [LoginTimestamp]
                FROM [dbo].[Logins] AS [Extent2]
                WHERE [Extent1].[Id] = [Extent2].[User_Id]
            )  AS [Project1]
            ORDER BY [Project1].[LoginTimestamp] DESC) AS [C1]
        FROM [dbo].[Users] AS [Extent1] ) AS [Project2]
    OUTER APPLY  (SELECT TOP (1) [Project3].[IpAddress] AS [IpAddress]
        FROM ( SELECT 
            [Extent3].[LoginTimestamp] AS [LoginTimestamp], 
            [Extent3].[IpAddress] AS [IpAddress]
            FROM [dbo].[Logins] AS [Extent3]
            WHERE [Project2].[Id] = [Extent3].[User_Id]
        )  AS [Project3]
        ORDER BY [Project3].[LoginTimestamp] DESC ) AS [Limit2]

... 这不是特别有效，而且只会变得更糟，因为它似乎为(相同)子实体的每个字段输出添加了一个子查询。

如果我在 SQL 中这样做，我会使用:

SELECT Users.Id, Users.Name, Users.Email, Users.Department, Logins.LoginTimestamp, Logins.IpAddress, Logins.UserAgent
FROM (SELECT MAX(LoginTimestamp) AS LastLoginTimestamp, User_Id
          FROM Logins AS Logins_1
          GROUP BY User_Id) AS LastLogins INNER JOIN
      Logins ON LastLogins.LastLoginTimestamp = Logins.LoginTimestamp AND LastLogins.User_Id = Logins.User_Id INNER JOIN
      Users ON Logins.User_Id = Users.Id

一个子查询，您可以根据需要从子表中引入尽可能多的字段。

所以我想我的问题是如何在 LINQ 中复制此 SQL 查询？

或者，如果有更有效的方法通过 LINQ 执行此操作，我很乐意看到它。

感谢任何帮助!谢谢

Answer 1

您可以使用 let 避免双重子查询

var result = from u in Users
             let lastLogin = u.Logins.OrderByDescending(l => l.LoginTimestamp).FirstOrDefault()
             select new {u.Id, u.Name, u.Email, u.Department, 
                    LastLogin = lastLogin.LoginTimestamp,
                    LastIP = lastLogin.IpAddress};