Get Sums of All Instances of an Id From Two Tables

Question

I want to display the Sum.Slots of all instances of CompendiumId in QualificationBatch and likewise, all the Sum.Seats of all instances of CompendiumId in SAP, and display it in a table like this:

Compendium |Sum.Seats |Sum.Slots

Id1 |Seats1 |Slots1

Id2 |Seats2 |Slots2

After a lot of reading, I think using GroupBy could do this, but I couldn't get it to work as I'm fairly new with everything, even with the basics such as manipulating a List or an IEnumerable. This is what I have so far:

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
    }

Controller

public ActionResult Index()
        {
            List<SAPSummaryViewModel> SAPSummaryViewModelList = new List<SAPSummaryViewModel>();
            var sapsummarylist = (from SP in db.SAPs
                                  join Comp in db.Compendia on SP.CompendiumId equals Comp.Id                                  
                                  select new {Comp.Id, SP.Seats });
            sapsummarylist.GroupBy(i => i.Id).Select(group =>
                                              new
                                              {
                                                  Id = group.Key,
                                                  Sum = group.Sum(item => item.Seats)
                                              });          
            foreach (var item in sapsummarylist)
            {                
                SAPSummaryViewModel objcvm = new SAPSummaryViewModel();              
                objcvm.Id = item.Id;
                objcvm.Seats = item.Seats;                
                SAPSummaryViewModelList.Add(objcvm);
            }
            return View(SAPSummaryViewModelList);
        }

I'm open to other ways of doing this, I'm just putting the code for my controller above to show what I've been trying. In here, I'm trying to get it to work for one table first, but I already got stuck here since it's not working.

Abhilash Ravindran C K · Accepted Answer · 2018-03-20T06:09:40.890

0

Following code will be helpful to you,

var sapsummarylist = ( from cmp in Compendium
                       join sp in SAP on cmp.Id equals sp.CompendiumId
                       join qb in QualificationBatch on  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)                                    
                        });

edited Mar 20 '18 at 06:09

answered Mar 20 '18 at 05:11

Abhilash Ravindran C K

1,818
2
13
22

I'm also having trouble in displaying a dbquery, how do I implement this if I have a view that's like this? @model IEnumerable – Lwrnc Crz Mar 20 '18 at 05:55
I don't know the result yet because I don't know how to convert this query to an IEnumarable for my view – Lwrnc Crz Mar 20 '18 at 06:09
try to use return View(sapsummarylist); – Abhilash Ravindran C K Mar 20 '18 at 06:12
your updated answer got my view to work. well at first I tried without any code with slots, so just the sum for seats, and it returned the correct sum but repeated the sum as many as there were instances. when I included the slots, it doubled the sums for seats, and an incorrect value for slots, I'm still trying to figure out how it arrived with those values if there was an error on my side – Lwrnc Crz Mar 20 '18 at 06:35
There is no chance to double the seats as you include slots. Anyway check your entries. If it is working for you please accept it as your answer. – Abhilash Ravindran C K Mar 20 '18 at 06:43
I should probably mention that in my model, SAP and QB has no relation to each other, meaning that Seats may exist with 0 Slots, so I removed the join to display correct values at least for seats, I just need to remove the repeating sums and I will try how to include the sum for slots later, but thanks I'm planning on accepting your answer as soon as possible – Lwrnc Crz Mar 20 '18 at 06:50
I was able to remove the duplicate sums with return View(sapsummarylist.Distinct()); I'm going to accept your answer and will just post a new question because I think getting the sums from two unrelated tables is now an entirely different problem, thank you very much – Lwrnc Crz Mar 20 '18 at 07:29

score 0 · Answer 2 · answered Mar 20 '18 at 08:36

So every QualificationBatch has properties CompendiumId an int property Slots.

Every SAP also has a property CompendiumId. It also has an int property Seats.

Now apparently you have sequences of QualificationBatches and SAPs, and you want per compendiumId the values of Slots and Seats

In small steps:

IQueryable<QualificationBatch> qualificationBatches = db.QualificationBatches;
IQueryable<SAP> saps = db.Saps;

var result = qualificationBatches    // join qualificationbatches
    .Join(saps,                      // with saps
    qualificationBatch => qualificationBatch.CompendiumId,
                                     // from every q-batch take the CompendiumId,
    sap => sap.CompendiumId,         // from every sap take the CompendiumId,
    (qualificationBatch, sap)  => new SapSummaryViewModel()
    {                                // when they match make a new SapSummaryViewModel
         Id = qualificationBatch.Id,
         Slots =  qualificationBatch.Slots,
         Seats = qualificationBatch.Seats,
     });

TODO: make one statement

Be aware, that this is an inner join: if you have a SAP with a CompendiumId that is not used by any QualificationBatch you won't get it in your end result. If you also want those items in your end result you'll need a left outer join or a full outer join depending on what you want. LINQ doesn't support them, but you can extend LINQ by writing a method for it. See Stackoverflow LINQ full outer join

Thank you for your very insightful response. This is exactly my problem now, as I'm not getting rows for instances of CompendiumId with sp.Seats but without sp.Slots, I don't know if it's appropriate but I already made a new question here describing my results specifically: https://stackoverflow.com/questions/49379715/join-statement-for-two-unrelated-entities-to-get-distinct-sum — Lwrnc Crz, Mar 20 '18 at 08:58

Get Sums of All Instances of an Id From Two Tables

2 Answers2