CODEWITHSUNDEEP
entity-frameworklinqgroup-bysumleft-join
NewBDueB
NewBDueB

Reputation: 11

Entity Framework group by left join query

The Result should look like this result should look like this

I've started to use Entity Framework for one month so I am not familiar with linq queries. The query I wrote in SQL is:

SELECT 
    om0001.CUSTOMER, om0001.ITEM_CODE, 
    SUM(om0001.AMOUNT) AS AMOUNT, 
    SUM(ep0001.EXPORT_AMOUNT) AS EXPORT_AMOUNT
FROM
    om0001
LEFT OUTER JOIN 
    ep0001 ON om0001.ID = ep0001.om0001_ID
GROUP BY 
    om0001.CUSTOMER, om0001.ITEM_CODE;

When I run this query in SQL, it runs well so I tried to convert it to linq queries.

What I made so far is 

var testjoin = from om0001 in EF.om0001
               join ep0001 in EF.ep0001 on om0001.ID equals ep0001.om0001_ID
               into jointable
               from z in jointable.DefaultIfEmpty()
               group z by new {om0001.CUSTOMER, om0001.ITEM_CODE } into g
               select new
                      {
                          CUSTOMER = g.Key.CUSTOMER,
                          ITEM_CODE = g.Key.ITEM_CODE,
                          om0001SUMamount = g.Sum(x => x.AMOUNT),
                          ep0001EXPORTsumAmount = g.Sum(y => y.EXPORT_AMOUNT)
                      };

The problem over this linq query is I can not get om0001SUMamount. I get only ep0001 column data. Please help

Upvotes: 1

Views: 61

Answers (2)

Harald Coppoolse
Harald Coppoolse

Reputation: 30474

So you have a table with Oms (actually Om00001, but I'm not going to write all those 0001 over and over again), and a table with Eps, and you have a one-to-many relation between Oms and Eps: Every Om has zero or more Eps, and every Ep belongs to exactly one Om, namely the Om that foreign key EpId refers to.

If you have followed Entity Framework code first conventions you will have classes similar to the following:

class Om
{
    public int Id {get; set;}
    public string Customer {get; set;}
    public string ItemCode {get; set;}
    ...

    // Every Om has zero or more Eps (one-to-many)
    public virtual ICollection<Ep> Eps {get; set;}
}

class Ep
{
    public int Id {get; set;}
    public int Amount {get; set;}
    public int ExportAmount {get; set;}
    ...

    // every Ep belongs to exactly one Om, using foreign key
    public int OmId {get; set;}
    public virtual Om Om {get; set;}
}

This is enough for entity framework to detect your one-to-many relationship. Because I followed the conventions, there is no need for Attributes, nor fluent API. If you want different table names, or columns, you need fluent API / attributes.

In entity framework the non-virtual properties represent the columns of your tables, the virtual properties represent the relations between the tables (one-to-many, many-to-many, ...)

Solution using GroupJoin

var result = dbContext.Oms.GroupJoin(dbContext.Eps,
    om => om.Id,                   // from every Om take the primary key
    ep => ep.OmId,                 // from every ep take the foreign key to the Om

    (om, epsOfThisOm) => new       // from every om and all eps having the correct foreign key
    {                              // make one new object
        // Select only the properties that you plan to use:
        Customer = om.Customer,
        ItemCode = om.ItemCode,

        Amount = epsOfThisOm.Sum(ep => ep.Amount);
        ExportAmount = epsOfThisOm.Sum(ep => ep.ExportAmount);
    });

Solution using the virtual ICollection

Instead of executing a GroupJoin, you could also use the virtual ICollection.

Requirement: from every Om, give me the Customer and the ItemCode, and the sum of all Amounts of its Eps, and the sum of all ExportAmounts of its Eps.

var result = dbContext.Oms.Select(om => new
{
    Customer = om.Customer,
    ItemCode = om.ItemCode,

    Amount = om.Eps.Sum(ep => ep.Amount);
    ExportAmount = om.Eps.Sum(ep => ep.ExportAmount);
});

This looks much neater, and it matches more directly your requirement. Entity framework knows the relations, and will do the correct GroupJoin for you.

Upvotes: 0

pwilcox
pwilcox

Reputation: 5763

Obviously, I cant peek into your EF database, so I created some sample data (the 'item' class structures are implied):

var EF = new efClass {
    om0001 = new List<om0001item> {
        new om0001item { ID = 0, CUSTOMER = 0, ITEM_CODE = 0, AMOUNT = 10 },
        new om0001item { ID = 1, CUSTOMER = 0, ITEM_CODE = 0, AMOUNT = 20 },
        new om0001item { ID = 2, CUSTOMER = 1, ITEM_CODE = 1, AMOUNT = 30 },
        new om0001item { ID = 3, CUSTOMER = 1, ITEM_CODE = 1, AMOUNT = 40 }
    },
    ep0001 = new List<ep0001item> {
        new ep0001item { om0001_ID = 0, EXPORT_AMOUNT = -20 },
        new ep0001item { om0001_ID = 1, EXPORT_AMOUNT = -20 }        
    }
};

With this data, I created a query that frankly feels inelegant and left me disappointed, but that's the nature of left joins in LINQ:

var testjoin = from om0001 in EF.om0001
    join ep0001 in EF.ep0001 on om0001.ID equals ep0001.om0001_ID into jointable
    select new { om0001, ep0001 = jointable.DefaultIfEmpty() } into combined
    group combined by new { 
        combined.om0001.CUSTOMER,
        combined.om0001.ITEM_CODE
    } into g
    select new {
       CUSTOMER = g.Key.CUSTOMER,
       ITEM_CODE = g.Key.ITEM_CODE,
       om0001SUMamount = g.Sum(x => x.om0001.AMOUNT),
       ep0001EXPORTsumAmount = g.Sum(x => x?.ep0001.Sum(y => y?.EXPORT_AMOUNT ?? 0)) 
    };

Bottom line is that when you group by 'jointable', you've lost ep0001 references. So select both ep0001 and om0001 into a new 'combined' object, and then group based off of it.

When I created a javascript library (fluent-data) that had some LINQ-like functionality, I developed a lot of respect and compassion for the LINQ developers. Nevertheless, I don't know why they don't just create a left join operator to add so much more value to all the C# developers who use LINQ.

Upvotes: 1

Related Questions