Databases
StackOverflow2013
Postsdbo
POLoad most recent related entity record on a list of entities in a single query
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLoad most recent related entity record on a list of entities in a single query
    text
    copied!<p><br /> I am using Entity Framework 4.1 with POCOs and DbContext, no proxies no lazy loading.</p> <p>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.</p> <p>My problem involves 3 tables (I only left in what is relevant):</p> <pre class="lang-sql prettyprint-override"><code>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 ) </code></pre> <p>The PK are Usres.Id, UserStatuses.Id, UserFriends.UserId + UserFriends.FriendId.<br /> UserId is also a FK to Users.Id .</p> <p>Each user can have many statuses and many friends</p> <p>I hope the database structure is clear.</p> <p>What I am trying to do is get a list of users who are my friends and their most recent status.</p> <p>In SQL, it will look like:<br /></p> <pre class="lang-sql prettyprint-override"><code>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) </code></pre> <p>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.</p> <p>I would like to do that using entity framework. I found out how to get a list of my friends:</p> <pre class="lang-c# prettyprint-override"><code>var friends = db.UserFriends.Where(f =&gt; f.FriendId.Equals(userId)).Select(f =&gt; f.User); </code></pre> <p>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.</p> <p>The only thing I managed to do in order to get it to work is:</p> <pre class="lang-c# prettyprint-override"><code>var friends = db.UserFriends.Where(f =&gt; f.FriendId.Equals(userId)).Select(f =&gt; f.User); foreach (Model.User friend in friends) { db.Entry(friend).Collection(f =&gt; f.UserStatuses).Query().OrderByDescending(s =&gt; s.Id).Take(1).Load(); } </code></pre> <p>But what happens now is that for each friend I generate another SQL query to the database, this seems like (very) bad practice.</p> <p>How can I make it happen in a single query?<br /> Would appreciate any advice on how to load the latest inserted related entity.</p> <p>Thanks,</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload