StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POQuery the Unioned Results of Multiple Tables With Entity Framework
text
Body
copied!<p>I want to query 3 tables and grab to most recent activity over all of the tables, as determined by a CreatedDate time stamp. Each of the tables is represented by an entity in my domain model, and I'm using Entity Framework Code First (no data migrations) to map my domain. </p> <p>I know what the query should look like in SQL, but I'm not sure how to craft it in LinqToEntities or EntitySql. Can you please tell me how to do this in Entity Framework, and even if it's appropriate to perform this query with Entity Framework query methods? </p> <p>Thanks in advance for your help. </p> <p>Here are my entities (primary keys are on a base class): </p> <pre><code>public class Group : EntityThatBelongsToApartmentComplex<int> { public string GroupName { get; set; } public string GroupDescription { get; set; } [Required] public DateTimeOffset CreatedDate { get; protected set; } public int CreatedById { get; set; } public virtual UserProfile CreatedBy { get; set; } } public class Activity : EntityThatBelongsToApartmentComplex<int> { [StringLength(150)] public string Name { get; set; } [StringLength(150)] public string Description { get; set; } public int CreatedById { get; set; } public virtual UserProfile CreatedBy { get; set; } [Required] public DateTimeOffset CreatedDate { get; protected set; } } public class Comment : EntityBase<int> { [StringLength(200)] public string Text { get; set; } public int CreatedById { get; set; } public virtual UserProfile CreatedBy { get; set; } [Required] public DateTimeOffset CreatedDate { get; protected set; } } </code></pre> <p>Here's my feeling of what the query should look like in SQL: </p> <pre><code>WITH NewsFeed AS ( SELECT g.Id AS ItemId ,'Group' AS ItemType ,g.GroupName AS HeaderText ,g.CreatedDate ,g.CreatedById AS CreatorId ,u.UserName AS CreatorName FROM Groups g INNER JOIN UserProfiles u on g.CreatedById = u.Id UNION SELECT a.Id AS ItemId ,'Activity' AS ItemType ,a.Name AS HeaderText ,a.CreatedDate ,a.CreatedById AS CreatorId ,u.UserName FROM Activities a INNER JOIN UserProfiles u on a.CreatedById = u.Id UNION SELECT c.Id AS ItemId ,'Comment' AS ItemType ,c.Text AS HeaderText ,c.CreatedDate ,c.CreatedById AS CreatorId ,u.UserName FROM Comments c INNER JOIN UserProfiles u on c.CreatedById = u.Id ) SELECT TOP 10 * FROM NewsFeed ORDER BY CreatedDate </code></pre>

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