1

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:

My Sample data:

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

My desired output is:

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:

join

Here is my code:

Controller

    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());  
    }

Models

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
    }
Lwrnc Crz
  • 43
  • 1
  • 10
  • Hi. Please read & act on [mcve]. Give constraints (PKs, uniques, FKs) & input & desired output for that output. Explain in words how the output is a function of the input. Do you get what you expect before the left join? What does "because the two entities are not related to each other" mean? Please [use text, not images/links, for text (including code, tables & ERDs)](https://meta.stackoverflow.com/q/285551/3404097). Use an image only for convenience to supplement text and/or for what cannot be given in text. – philipxy Mar 20 '18 at 09:44
  • Hi. The tables help. "What happens here is this" is not clear. Before your left join you have removed rows with cmp ids that are not in both tables, via the joins. Work to get partial results. You want the join of two separate left joins, each of which does its own group/aggregate, but your query doesn't ask for that. By joining twice you generate every row that can be made from a combination of a row from the 1st join--*which itself does not have id as key*--when you go on to join *on a non-key*. View partial results! Find good subexpressions! PS [mcve] means we can cut & paste & run code. – philipxy Mar 23 '18 at 01:48
  • This seems a classic misprogramming of join of multiple aggregations. An SQL example wanting join of [two MAX/inner aggregations](https://stackoverflow.com/a/47804541/3404097). An SQL example wanting join of [two GROUP_CONCAT/left aggregations](https://stackoverflow.com/a/45252750/3404097)--more similar to your SUM since NULL adds 0. I didn't bother to find a more asp-oriented example. Force yourself to always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your strings & read many answers. You still haven't *clearly stated the problem*. – philipxy Mar 23 '18 at 02:17

1 Answers1

0

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
                      });
Abhilash Ravindran C K
  • 1,818
  • 2
  • 13
  • 22
  • sadly this returned an error for me: "the cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.", with the highlighted statement in my view "@foreach (var item in Model)" – Lwrnc Crz Mar 21 '18 at 06:20
  • It sounds like your model defines one of the properties as non-nullable, but the result has the corresponding column as nullable, and has a null value in it. When the generated reader attempts to populate the model, BOOM. Unfortunately, I can't tell you which property, and it doesn't need to even be one of those mentioned in that code. You need to check your model very carefully. Start with the properties / columns on whatever in SAPSummaryViewModel When you have found a mismatch: mark it as nullable, and retry. – Abhilash Ravindran C K Mar 21 '18 at 06:29