在一个单一的查询中加载实体列表上最新的相关实体记录[英] Load most recent related entity record on a list of entities in a single query

问题描述

I am using Entity Framework 4.1 with POCOs and DbContext, no proxies no lazy loading.

I am very new to Entity Framework and LINQ, but so far very impressed with how simple it is to work with it. I have some basic knowledge of SQL and have built the database first, and then created the model.

My problem involves 3 tables (I only left in what is relevant):

CREATE TABLE [dbo].[Users](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL
)

CREATE TABLE [dbo].[UserFriends](
    [UserId] [int] NOT NULL,
    [FriendId] [int] NOT NULL
)

CREATE TABLE [dbo].[UserStatuses](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [Message] [nvarchar](max) NOT NULL
)

The PK are Usres.Id, UserStatuses.Id, UserFriends.UserId + UserFriends.FriendId.
UserId is also a FK to Users.Id .

Each user can have many statuses and many friends

I hope the database structure is clear.

What I am trying to do is get a list of users who are my friends and their most recent status.

In SQL, it will look like:

SELECT * FROM Users U 
OUTER APPLY 
(
    SELECT TOP 1 *
    FROM UserStatuses
    WHERE UserId = U.Id
    ORDER BY Id DESC
) S
WHERE U.Id IN (SELECT FriendId FROM UserFriends WHERE UserId = 5)

This will get all the friends for '5' and their latest status message. I think this is the most efficient way (inner join on friends and users and outer join on the status messages), but this is not the question.

I would like to do that using entity framework. I found out how to get a list of my friends:

var friends = db.UserFriends.Where(f => f.FriendId.Equals(userId)).Select(f => f.User);

But if I will add the .Include(u => u.UserStatuses) to get the statuses, I will get all of them, I would like to return just the most recent one.

The only thing I managed to do in order to get it to work is:

var friends = db.UserFriends.Where(f => f.FriendId.Equals(userId)).Select(f => f.User);

foreach (Model.User friend in friends)
{
    db.Entry(friend).Collection(f => f.UserStatuses).Query().OrderByDescending(s => s.Id).Take(1).Load();
}

But what happens now is that for each friend I generate another SQL query to the database, this seems like (very) bad practice.

How can I make it happen in a single query?
Would appreciate any advice on how to load the latest inserted related entity.

Thanks,

推荐答案

Only way to get this in a single database query is a projection:

var friendsWithLastStatus = db.UserFriends
    .Where(f => f.FriendId.Equals(userId))
    .Select(f => new 
    {
        User = f.User,
        LastStatus = f.User.UserStatuses
                           .OrderByDescending(s => s.Id).FirstOrDefault()
    })
    .ToList();

This gives you a list a anonymous type objects where each entry has a User property and the LastStatus property with the latest status of that user.

You can project into a named type if you prefer:

public class UserWithLastStatus
{
    public User User { get; set; }
    public UserStatus LastStatus { get; set; }
}

And then replace in the query above new ... by new UserWithLastStatus ... to get a List<UserWithLastStatus> as the result type of the query.