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

本文是小编为大家收集整理的关于在一个单一的查询中加载实体列表上最新的相关实体记录的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述


我正在使用Pocos和dbContext的实体框架4.1,没有代理没有懒惰的加载.

我对实体框架和LINQ非常陌生,但到目前为止,它对它的工作非常简单印象深刻. 我对SQL有一些基本知识,并首先构建了数据库,然后创建了模型.

我的问题涉及3个表(我只留在相关的内容中):

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
)

pk是usres.id,userstatuses.id,userfriends.userid + userfriends.friends.friends.
用户ID也是用户的FK.ID.

每个用户可以拥有许多状态,并且许多朋友

我希望数据库结构很清楚.

我要做的是获取我的朋友及其最新状态的用户列表.

在SQL中,看起来像:

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)

这将为" 5"和他们最新的状态消息提供所有朋友. 我认为这是最有效的方法(内在加入朋友和用户,以及状态消息上的外部加入),但这不是问题.

我想使用实体框架来做到这一点. 我发现了如何获取我的朋友列表:

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

但是,如果我要添加.include(u => u.ustatuses)以获得状态,我将获得所有状态,我只想返回最新的.

我唯一要做的才能使它工作的是:

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

但是现在发生的事情是,对于每个朋友,我都会生成另一个SQL查询数据库,这似乎是(非常)不好的练习.

如何在单个查询中实现它?
将感谢有关如何加载最新插入相关实体的任何建议.

谢谢,

推荐答案

在单个数据库查询中获取此功能的唯一方法是一个投影:

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();

这为您提供了列表一个匿名类型对象,其中每个条目都有User属性和具有该用户最新状态的LastStatus属性.

如果您喜欢:

,您可以投射到命名类型中
public class UserWithLastStatus
{
    public User User { get; set; }
    public UserStatus LastStatus { get; set; }
}

,然后在new UserWithLastStatus ...的上面的查询中替换new UserWithLastStatus ...以获取List<UserWithLastStatus>作为查询的结果类型.

本文地址:https://www.itbaoku.cn/post/1556968.html

问题描述


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.