Geek Answers Handbook

LINQ query to include fields from a single row in a child table

I am trying to figure out how to use the LINQ query to return data from a main object, as well as single-row details from a child of a 1-to-Many (or 0..1-to-Many) object.

The details here are just a simplified example. This is more of a concept I'm looking for. Entity Framework classes can be defined as:

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; }
}

Therefore, when starting a query, I need something like:

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

All still. Let's say I wanted to add the date and time of the last login:

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

Still good. But now I want to add additional data from the last entry entry. Let's say I also want to include the IP address from the last login. I know that the request should change significantly and use something other than "Max", but I'm not sure how to do it. Joining? Sub queries?

:

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}

... , , , , .

/ ?

, , , .

16 2016 :

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

, .

, , , SQL- LINQ?

, LINQ, .

!

+4
4

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};
+2

var results = from u in Users
    select new {u.Id, u.Name, u.Email, u.Department,     
    Logins.Where(l=>l.User.Id==u.Id).LastOrDefault()}

, - . , LastOrDefault()

+1

...

, , , . , .

1:

from l in Logins
// where clause here
group l by l.User.Id into grp
let LastLogin = grp.Max(ul => ul.LoginTimestamp)
from l in grp
where l.LoginTimestamp == LastLogin
select new { l.User.Id, l.User.Name, l.User.Email, l.User.Department, LastLogin }

2:

from l in Logins
// where clause here
group l by l.User.Id into grp
let ul = grp.OrderByDescending(ul => ul.LoginTimestamp).FirstOrDefault()
select new { ul.User.Id, ul.User.Name, ul.User.Email, ul.User.Department, LastLogin = ul.LoginTimestamp }

SQL, 1, , 2, . - .

, , -.

0

SQL , ToString() Entity Framework. .

- Enumerable EF Queryable ( SQL-), .

-1

More articles:


All Articles