CODEWITHSUNDEEP
sqllinqjoingrouping
Yushatak
Yushatak

Reputation: 771

LINQ vs. SQL Joins And Groupings (w/ Specific Case)

In SQL, when you do a bunch of joins, it treats all of the joined objects as one "super-object" to be selected from. This remains the case when you group by a particular column, as long as you include anything you select in the grouping (unless it is produced by the grouping, such as summing a bunch of int columns).

In LINQ, you can similarly do a bunch of joins in a row, and select from them. However, when you perform a grouping, it behaves differently. The syntax in query-style LINQ only allows for grouping a single table (i.e., one of your joins), discarding the others.

For an example case suppose we have a few tables:

Request
-------
int ID (PK)
datetime Created
int StatusID (FK)

Item
----
int ID (PK)
string Name

RequestItem
-----------
int ID (PK)
int ItemID (FK)
int RequestID (FK)
int Quantity

Inventory
---------
int ID (PK)
int ItemID (FK)
int Quantity    

LU_Status
---------
int ID (PK)
string Description

In our example, LU_Status has three values in the database:

1 - New
2 - Approved
3 - Completed

This is a simplified version of the actual situation that lead me to this question. Given this schema, the need is to produce a report that shows the number of requested items (status not "Completed"), approved items (status "Approved"), distributed items (status "Completed"), and the number of items in stock (from Inventory), all grouped by the item. If this is a bit vague take a look at the SQL or let me know and I'll try to make it clearer.

In SQL I might do this:

select i.Name,
Requested = sum(ri.Quantity), 
Approved = sum(case when r.StatusID = 2 then ri.Quantity else 0 end)
Distributed = sum(case when r.StatusID = 3 then ri.Quantity else 0 end)
Storage = sum(Storage)
from RequestItem as ri
inner join Request as r on r.ID = ri.RequestID
inner join Item as i on i.ID = ri.ItemID
inner join (select ItemID, Storage = sum(Quantity)
            from Inventory
            group by ItemID)
            as inv on inv.ItemID = ri.ItemID
group by i.Name

This produces the desired result.

I began to rewrite this in LINQ, and got so far as:

var result = from ri in RequestItem
             join r in Request on ri.RequestID equals r.ID
             join i in Item on ri.ItemID equals i.ID
             join x in (from inv in Inventory
                        group inv by inv.ItemID into g
                        select new { ItemID = g.Key, Storage = g.Sum(x => x.Quantity) })
                        on ri.ItemID equals x.ItemID
             group...????

At this point everything had been going smoothly, but I realized that I couldn't simply group by i.Name like I did in SQL. In fact, there seemed to be no way to group all of the joined things together so that I could select the necessary things from them, so I was forced to stop there.. I understand how to use the group syntax in simpler situations (see the subquery), but if there's a way to do this sort of grouping in LINQ I'm not seeing it, and searching around here and elsewhere has not illuminated me.

Is this a shortcoming of LINQ, or am I missing something?

Upvotes: 0

Views: 37

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205929

The easiest way is to use group new { ... } by ... construct and include all the items from the joins that you need later inside the { ... }, like this

var query =
    from ri in db.RequestItem
    join r in db.Request on ri.RequestID equals r.ID
    join i in db.Item on ri.ItemID equals i.ID
    join x in (from inv in db.Inventory
               group inv by inv.ItemID into g
               select new { ItemID = g.Key, Storage = g.Sum(x => x.Quantity) }
              ) on ri.ItemID equals x.ItemID
    group new { ri, r, i, x } by i.Name into g
    select new
    {
        Name = g.Key,
        Requested = g.Sum(e => e.ri.Quantity),
        Approved = g.Sum(e => e.r.StatusID == 2 ? e.ri.Quantity : 0),
        Distributed = g.Sum(e => e.r.StatusID == 3 ? e.ri.Quantity : 0),
        Storage = g.Sum(e => e.x.Storage)
    };

Upvotes: 2

Gert Arnold
Gert Arnold

Reputation: 109347

You can create an anonymous type in a grouping that contains all data you need:

var result = from ri in RequestItem
             join r in Request on ri.RequestID equals r.ID
             join i in Item on ri.ItemID equals i.ID
             join x in (from inv in Inventory
                        group inv by inv.ItemID into g
                        select new { ItemID = g.Key, Storage = g.Sum(x => x.Quantity) })
                        on ri.ItemID equals x.ItemID
             group new
             {
                 i.Name,
                 r.StatusId,
                 ri.Quantity,
                 x.Storage,
             }
             by i.Name into grp
             select new
             {
                 grp.Key,
                 Requested = grp.Where(x => x.StatusID == 2).Sum(x => x.Quantity),
                 Distributed = grp.Where(x => x.StatusID == 3).Sum(x => x.Quantity),
                 Storage = grp.Sum(x => x.Storage)
             }

(not tested, obviously, but it should be close).

Upvotes: 3

Related Questions