在单个查询中将最新的相关实体记录加载到实体列表中 [英] Load most recent related entity record on a list of entities in a single query
问题描述
我使用的是带有POCO和DbContext的Entity Framework 4.1，没有代理，没有延迟加载.
I am using Entity Framework 4.1 with POCOs and DbContext, no proxies no lazy loading.
我对Entity Framework和LINQ还是陌生的，但是到目前为止，使用它的简单性给我留下了深刻的印象. 我有一些SQL的基本知识，并且先构建了数据库，然后创建了模型.
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.
我的问题涉及3个表(我只留了相关的内容):
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
)
PK是Usres.Id，UserStatuses.Id，UserFriends.UserId + UserFriends.FriendId.
UserId也是Users.Id的FK.
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.
在SQL中，它看起来像:
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)
这将使所有好友获得"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);
但是，如果我添加.Include(u => u.UserStatuses)来获取状态，我将获取所有状态，我只想返回最新状态.
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();
}
但是现在发生的是，对于每个朋友，我向数据库生成另一个SQL查询，这似乎是(非常)不好的做法.
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.
谢谢
推荐答案
在单个数据库查询中获得此结果的唯一方法是投影:
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();
这为您提供了一个匿名类型对象列表，其中每个条目都具有一个User属性和一个具有该用户最新状态的LastStatus属性.
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; }
}
然后在new ...以上的查询中替换为new UserWithLastStatus ...以获取List<UserWithLastStatus>作为查询的结果类型.
And then replace in the query above new ... by new UserWithLastStatus ... to get a List<UserWithLastStatus> as the result type of the query.
这篇关于在单个查询中将最新的相关实体记录加载到实体列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
