Linq two table with sum column

Question

My RowMultiplevaluw table is

public class RowMultipleValues
{
    public int ID { get; set; }       
    public String Year{ get; set; }
    public string country      { get; set; }
    public decial Admin { get; set; }
    public  decimal Finance { get; set; }
    public virtual ICollection<UsedAmount> UsedAmount { get; set; }        
}

My used amount table is

public class UsedAmount
{
    public int ID { get; set; }       
    public string  Year{ get; set; }
    public string country      { get; set; }     
    public decial  UsedAmount { get; set; }

    public int RowMultipleValues ID { get; set; } 
    Public virtual RowMultibleValue RowMultibleValue { get; set; }

 }

My query is

var query = from mtv in context.multiplerowvaluetable
            join usd in dbcontext.usedtsble on mtv.year equal usd.year group g by mtv.country into g
            select new { country =g.key,sumadmincolumn =g.sum(Admin),sumfinancecolumn = g.sum(finance) }).tolist();

Result which I want is

    ID    Year    Country      Admin.   UsedAdmin    Finance    UsedFinance
    1.   2017     USA           100         50         200         300
    2.   2017     China         300        300         500         400
    Total.                      400        350         700         700

Please help me my model design and query for result.Thank.

how about sanitizing your code first, and include an input and the output you expect — Dan Dohotaru
This isn't gonna work: context and dbcontext in one LINQ statement. — Gert Arnold

Harald Coppoolse Harald Coppoolse · Accepted Answer · 2018-01-10T12:40:36

So you want to join every MultipleValue with the UsedAmount on equal year value. Then group the result into groups of joined items with same country. Finally from every group create one object with the country, the sum of all Admin values and the sum of all finance values.

// first join the two collections on same year.
// we only need properties Country, Admin, Finance:
var result = myDbContext.MultipleRowValueTable.Join(myDbContext.UsedAmountTable,
    multipleRow => multipleRow.Year,    // from every multipleRow take the year
    usedAmount => usedAmount.Year,      // from every usedAmount take the year
    (multipleRow, usedAmount) => new    // when they match make a new object
    {
        Country = multipleRow.Country,
        Admin = multipleRow.Admin,
        UsedAdmin = usedAmount.Admin,
        Finance = multipleRow.Finance,
        UsedFinance = usedAmount.Finance,
    })
    // group the elements from this join table into groups with same Country
    .GroupBy(joinedItem => joinedItem.Country,  // all items in the group have this Country
         joinedItem => new                      // the elements of the group
         {
             Admin = joinedItem.Admin,
             UsedAdmin = joinedItem.UsedAdmin,
             Finance = joinedItem.Finance,
             UsedFinance = joinedItem.UsedFinance,
         })

         // finally: from every group take the Key (which is the Country) 
         // and the sum of the Admins and Finances in the group
         .Select(group => new
         {
              Country = group.Key,
              SumAdminColumn = group
                  .Select(groupElement => groupElement.Admin)
                   .Sum(),
              ... // others are similar
         });


    // from every group take the elements and sum the properties
    .Select(group => new
    {
        Id = multipleRowValue.Id,
        Year = multipleRowValue.Year,
        Country = multipleRowValue.Country,
    }

Linq two table with sum column

1 Answers