Linq-to-Entities Left JOIN

Question

This is my request:

from forum in Forums join post in Posts on forum equals post.Forum into postGroup from p in postGroup where p.ParentPostID==0 select new { forum.Title, forum.ForumID, LastPostTitle = p.Title, LastPostAddedDate = p.AddedDate }).OrderBy(o=>o.ForumID) 

Currently, the association has not remained, which means that if there is no message on a forum that belongs to it, it will not be returned. A silent forum should be returned with zero (or default) message property values.

UPDATE

The result set should look something like this:

 ForumId | ForumTitle | LastPostTitle | LastPostAddedDate --------+------------+---------------+------------------ 4 | Sport | blabla | 12/4/2010 4 | Sport | blabla | 15/4/2010 6 | Games | blabla | 1/5/2010 7 | Flame | | 

Answer 1

  var allforums = from f in context.Fora.Include("Posts") select f; 

This query gives the same results as

  var allForums = from f in context.Fora select new ForumPosts { Forum = f, Posts = context.Posts.Where(x=> x.ForumId == f.ForumId) 

Answer 2

Here is some code to help you deal with Left Join with Link

  private class EntityRole { public int EntityId { get; set; } public int RoleId { get; set; } } private IList<EntityRole> GetSourceEntityRole() { var list = new List<EntityRole>() {new EntityRole(){EntityId = 123, RoleId = 1}, new EntityRole(){EntityId = 123, RoleId = 2}, new EntityRole(){EntityId = 123, RoleId = 3}, new EntityRole(){EntityId = 123, RoleId = 4}}; list.Reverse(); return list; } private IList<EntityRole> GetEmptyEntityRole() { var list = new List<EntityRole>(); return list; } public void TestToDelete() { var source = this.GetSourceEntityRole(); var destination = this.GetEmptyEntityRole(); this.TestLeftJoin(source, destination); } private void TestLeftJoin(IList<EntityRole> source, IList<EntityRole> destination) { var inserting = this.GetMissing(source, destination); var deleting = this.GetMissing(destination, source); this.Enumerate("Source", source); this.Enumerate("Destination", destination); this.Enumerate("Deleting", deleting); this.Enumerate("Inserting", inserting); } private IEnumerable<EntityRole> GetMissing(IList<EntityRole> sourceEntities, IList<EntityRole> destinationEntities) { return from source in sourceEntities join dest in destinationEntities on source.RoleId equals dest.RoleId into joined from source2 in joined.DefaultIfEmpty() where source2 == null select source; } private void Enumerate(string source, IEnumerable<EntityRole> roles) { foreach (var item in roles) { Console.WriteLine("{0}:{1}", source, item.RoleId); } } 

Answer 3

 Forums .GroupJoin(PostGroup, f => f.ID, p => p.ForumID, (f, p) => new { Forum = f, PostList = p }) .Where(anon => anon.PostList.Any(pl => pl.ParentPostID.Equals(0))) .OrderBy(anon => anon.Forum.ForumID) .Select(anon => new { Title = anon.Forum.Title, ForumID = anon.Forum.ForumID, LastPostTitle = anon.PostList.FirstOrDefault().Title, LastPostAddedDate = anon.PostList.FirstOrDefault().AddedDate, }); 

Something like that. I was not too sure, because in fact I did not consider the data model, but GroupJoin should be very similar to the LEFT OUTER JOIN, even if it does not really do it in SQL.

Answer 4

Try something like this:

 from forum in Forums join post in Posts on forum equals post.Forum into postGroup // from p in postGroup // where p.ParentPostID==0 select new { forum.Title, forum.ForumID, LastPostTitle = postGroup.FirstOrDefault(p => p.ParentPostID==0).Title, LastPostAddedDate = (DateTime?)postGroup.FirstOrDefault(p => p.ParentPostID==0).AddedDate }).OrderBy(o=>o.ForumID) 

properties returning empty from the left join should also be null. So int => int? and DateTime => DateTime? etc..

Answer 5

if not error:

 var list = from forum in Forums.DefaultItIfEmpty() from post in Posts.DefaultItIfEmpty() where forum.forum_id == post.forum_id && post.ParentPostID==0 select new { forum.Title, forum.ForumID, LastPostTitle = p.Title, LastPostAddedDate = p.AddedDate }).OrderBy(o=>o.ForumID) 

Answer 6

You tried something like:

 from forum in Forums from posts in (Posts.Where(qPosts=> forum.ForumId == qPosts.ForumId)).DefaultIfEmpty() where posts.ParentPostID == 0 orderby forum.ForumId select new { forum.Title, forum.ForumID, LastPostTitle = posts.Title, LastPostAddedDate = posts.AddedDate } 

Answer 7

  public class ForumPosts { public Forum Forum { get; set; } public IQueryable<Post> Posts { get; set; } } public class DisplaySet { public string Name { get; set; } public string PostTile { get; set; } } //left outer join using (ClassLibrary1.Entities context = new Entities()) { var allForums = from f in context.Fora select new ForumPosts { Forum = f, Posts = context.Posts.Where(x=> x.ForumId == f.ForumId) }; List<DisplaySet> ds = new List<DisplaySet>(); foreach (var forum in allForums) { if (forum.Posts.AsEnumerable().Count() != 0) { foreach (var post in forum.Posts) { ds.Add(new DisplaySet(){ Name = forum.Forum.Name, PostTile = post.PostValue}); } } else ds.Add(new DisplaySet(){ Name = forum.Forum.Name, PostTile = string.Empty}); } foreach (var item in ds) { Console.WriteLine(string.Format("{0} || {1}",item.Name,item.PostTile)); } } //This produces the following LINQ query which is right SELECT [Project1].[ForumId] AS [ForumId], [Project1].[Name] AS [Name], [Project1].[C1] AS [C1], [Project1].[PostId] AS [PostId], [Project1].[PostValue] AS [PostValue], [Project1].[ForumId1] AS [ForumId1] FROM ( SELECT [Extent1].[ForumId] AS [ForumId], [Extent1].[Name] AS [Name], [Extent2].[PostId] AS [PostId], [Extent2].[PostValue] AS [PostValue], [Extent2].[ForumId] AS [ForumId1], CASE WHEN ([Extent2].[PostId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM [dbo].[Forum] AS [Extent1] LEFT OUTER JOIN [dbo].[Post] AS [Extent2] ON [Extent2].[ForumId] = [Extent1].[ForumId] ) AS [Project1] ORDER BY [Project1].[ForumId] ASC, [Project1].[C1] ASC