两个不相关实体的联接语句以获得不同的总和 [英] Join Statement for Two Unrelated Entities to Get Distinct Sum

问题描述

我的join语句需要很多帮助，因为它会使我要加入的两个表的行成倍增加:

I need a lot of help with my join statement, as it multiplies the rows of the two tables that I'm trying to join:

我的样本数据:

SAPId CompendiumId Seats
----- ------------ -----
1     443          21
2     443          22
3     443          23
4     443          24
5     443          25
6     571          25
7     352          20

QBId  CompendiumId  Slots
----- ------------ -----
1     443          26
2     443          27
3     571          25
4     571          23

我想要的输出是:

CompendiumId Seats Slots
------------ ----- -----
443          115   53
571          25    48
352          20    0

但是我的代码的结果是:

but the result of my code is:

CompendiumId Seats Slots
------------ ----- -----
443          230   265
571          50    48

我认为这里会发生什么，用红色突出显示的单元格是重复的单元格:

I think what happens here is this, where the cells highlighted with red are the ones that get duplicated:

这是我的代码:

控制器

    private MyContext db = new MyContext();

    public ActionResult Index()
    {           
        var sapsummarylist = (from cmp in db.Compendia
                              join sp in db.SAPs on cmp.Id equals sp.CompendiumId
                              join qb in db.QualificationBatches on cmp.Id equals qb.CompendiumId
                              group new { cmp.Id, sp.Seats, qb.Slots } by new { cmp.Id } into mgrp
                              from grp in mgrp.DefaultIfEmpty()
                              select new SAPSummaryViewModel
                              {
                                  Id = grp.Id,
                                  Seats = mgrp.Sum(x => x.Seats),
                                  Slots = mgrp.Sum(x => x.Slots)
                              });

        return View(sapsummarylist.Distinct());  
    }

模型

public class Compendium
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<QualificationBatch> QualificationBatches { get; set; }        
    public virtual ICollection<SAP> SAPs { get; set; }
}


public class QualificationBatch
{
    public int Id { get; set; }
    public int CompendiumId { get; set; }
    public int Slots { get; set; }

    public virtual Compendium Compendium { get; set; }
}

public class SAP
{
    public int Id { get; set; }
    public int CompendiumId { get; set; }
    public int Seats { get; set; }

    public virtual Compendium Compendium { get; set; }
}

ViewModel

public class SAPSummaryViewModel
    {        
        public int Id { get; set; } //Compendium
        public int Slots { get; set; } //QualificationBatch
        public int Seats { get; set; } //SAP
    }

推荐答案

以下代码将对您有所帮助，

Following code will be helpful to you,

var sapsummarylist = (from sp in Saps
                      group sp by new { sp.CompendiumId } into grp
                      select new SAPSummaryViewModel
                      {
                         Id = grp.FirstOrDefault().CompendiumId,
                         Seats = grp.Sum(x => x.Seats),
                         Slots = QualificationBatches.Where(x=>x.CompendiumId == grp.FirstOrDefault().CompendiumId).Sum(x => x.Slots)??0
                      });

这篇关于两个不相关实体的联接语句以获得不同的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！