What is the proper Linq expression for this left-outer-join query?

Question

I am having trouble translating this his sql query into a linq expression that returns the results as an IEnumerable of a class.

This is the query:

Select * from Posts left outer join Ownergroups on 
Posts.PostId=Ownergroups.PostID
Where Ownergroups.OwnerName = 'Group A' AND PostType = 'news'

This is the only expression that doesn't throw errors, but it also only returns a single result.

        NewsViewModel vm = new NewsViewModel();

       vm.NewsItems =  (from an in db.Posts.Where(g => g.PostType == "News")
       from og in an.OwnerGroups.Where(g => g.OwnerName == "Group A")
        select an).Distinct().OrderByDescending(bb 
        =>bb.PostDate).ToList();

If I try to project to a new selection, I get an error. When I try to group by the PostId I get the proper results, but cannot attach the result to my ViewModel; I get an error saying "cannot convert type systems.collections.generic list to systems.collections.IEnumerable"

I'd really appreciate suggestions.

Adding the class, as requested:

     public class Post
{
    public int PostId { get; set; }

    public string PostType { get; set; }

    [Display(Name = "Top Title")]
    [MaxLength(300)]
    public string Headline1 { get; set; }

    [Display(Name = "Subtitle")]
    [MaxLength(300)]
    public string Headline2 { get; set; }

    public string Headline3 { get; set; }

    [Display(Name = "By Organization or Person")]
    [MaxLength(250)]
    public string Byline { get; set; }

    [Display(Name = "Text For Your Post")]
    [MaxLength(4999)]
    [AllowHtml]
    public string PostText1 { get; set; }

    [Display(Name = "Additional Text")]
    [MaxLength(4999)]
    [AllowHtml]
    public string PostText2 { get; set; }

    public string AuthorGroup { get; set; }

    [Display(Name = "Link to Video (URL)")]
    [MaxLength(249)]
    public string AVurl { get; set; }

    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:d}")]
    public DateTime PostDate { get; set; }


    [Display(Name = "Date To Archive")]
    [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:d}")]
    public DateTime? StopDate { get; set; }

    [Display(Name = "Posted By")]
    public string PostedBy { get; set; }

    [Display(Name = "Last Edited")]
    public DateTime LastEditDate { get; set; }

    [Display(Name = "Last Edited By")]
    public string LastEditor { get; set; }

    public virtual ICollection<PostAsset> PostAssets { get; set; }

    public virtual ICollection<PostAlbum> PostAlbums { get; set; }

    public virtual ICollection<OwnerGroup> OwnerGroups { get; set; }

you are doing inner join .have you tried http://www.sqltolinq.com/ — Peru, Aug 14 '18 at 15:35
*"linq expression that returns the results as an IEnumerable of a class"* Which class? Can we see it? Because SQL `select *` returns fields from both tables, so in order to do the same with LINQ, you need to define some class having all these properties. In other words, what is the type `T` in `IEnumerable` that you need to return? — Ivan Stoev, Aug 14 '18 at 15:44
Btw, `Where Ownergroups.OwnerName = 'Group A'` effectively turns `left outer join` into `inner join`. — Ivan Stoev, Aug 14 '18 at 15:47
this works as well: Select Posts.* from Posts left outer join Ownergroups on Posts.PostId=Ownergroups.PostID and Ownergroups.OwnerName = 'Group A' Where PostType = 'News' — JaneH, Aug 14 '18 at 16:01
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) could help you? — NetMage, Aug 15 '18 at 01:13

Carlo Bos · Accepted Answer · 2018-08-24T00:29:52.743

The following will do a left join where the owners are OwnerName = 'Group A' and the PostType = 'news' joining, if possible, on PostId = PostId

void Main()
{
    var posts = 
        new List<Post>() 
        {
            new Post {PostId = 1, PostType = "news"},
            new Post {PostId = 2, PostType = "old"},
            new Post {PostId = 3, PostType = "news"},
        };

    var owners = 
        new List<OwnerGroup>()
        {
            new OwnerGroup {GroupId = 1, PostId = 1, OwnerName = "Group A" },
            new OwnerGroup {GroupId = 2, PostId = 1, OwnerName = "Group A" },
            new OwnerGroup {GroupId = 3, PostId = 2, OwnerName = "Group A" },
        };

    var leftJoinResult = posts
        .GroupJoin(
            owners.Where(o => o.OwnerName.Equals("Group A")), 
            r => r.PostId, rp => rp.PostId, 
            (l1, l2) => new { gjl1 = l1, gjl2 = l2 })
        .SelectMany(x => x.gjl2.DefaultIfEmpty(), (x, gjl2) => new { x.gjl1, gjl2 })
        .Where(x => x.gjl1.PostType.Equals("news") )
        // OPTIONAL: Add this line return the Post matches, not both the Post and the possible left joined OwnerGroup
        .Select(x => x.gjl1) 
        // OPTIONAL: Add this line to only get the distinct Post matches
        .GroupBy(p => p.PostId).Select(grp => grp.First());
}

public class Post
{
    public int PostId { get; set; }
    public string PostType { get; set; }
}

public class OwnerGroup
{
    public int GroupId { get;set; }
    public int PostId { get; set; }
    public String OwnerName { get; set; }
}

Thank you @Carlo Bos! I am working with your very elegant example. I haven't yet gotten it to work. The debugger is telling me "Method may only be called on a Type for which Type.IsGenericParameter is true" and I can't figure out to what that applies. Because I am very pressed for time this summer, I ultimately wrote a SqlQuery, which I'd never retreated to before and which took less than a minute. I am going to award this answered because I think with some tweaking of the model or the expression it will work. And it is a great learning tool! — JaneH, Aug 27 '18 at 14:01

What is the proper Linq expression for this left-outer-join query?

1 Answers1