sql-server - 如何使用聚合函数正确应用 GroupJoin？

Question

我对使用数据库有点老派，对 LINQ 不太熟悉。我找到了一个 LINQ 扩展来实现我所需要的，但我坚持正确使用它。它是GroupJoin

我真正需要做的是“每个帖子的类别和该类别的数量”

楷模

发布内容

public class PostContent
{
    [Key]
    public Guid Id { get; set; }
    public string title { get; set; }
    public string subTitle { get; set; }
    public string description { get; set; }
    public DateTime postDate { get; set; } = DateTime.Now;
    public string post { get; set; }

    public Guid? postCatId { get; set; }
    [ForeignKey("postCatId")]
    public virtual PostCategory postCategory { get; set; }
}

帖子类别

public class PostCategory
{
    [Key]
    public Guid Id { get; set; }
    public string categoryName { get; set; }

    public virtual List<PostContent> postContents { get; set; }

}

我的尝试“但一无所获”

    var NumberOfCategoriesForEachPost = context.postCategories 
    .GroupJoin( context.postContents,
    cat => cat.Id,
    con => con.postCatId, 
    (Category, Content) => new  
    {
        Cate = Category.categoryName,
        Cont = Content.Count()
    });

标签： sql-serverasp.net-mvcentity-frameworklinqasp.net-core

Answer 1

感谢@Nikhil Patil，您启发了我正确地完成它。

我找到了正确的方法如下

        var query = from cat in context.postCategories
                    join con in context.postContents
                    on cat.Id equals con.postCatId
                    group new { cat, con } by new { con.postCatId, cat.categoryName } into g
                    select new
                    {
                        //g.Key,
                        CategoryName = g.Key.categoryName,
                        CategoryID = g.Key.postCatId.ToString(),
                        Count = g.Count()
                    };

结果