CODEWITHSUNDEEP
c#linq
Nate58
Nate58

Reputation: 93

Linq grouping based on requirements

I have a sales data table that consists of individual item sales, multiple rows make up a single bill(BillID) in the system. The data looks like this:

CompanyID | StoreID |BillItemID| BillID | DateKey | Description | a bunch of other columns for amount sold and taxes etc.

All that matters is really the BillID, I need to find bills that contain both the items that are included in the linq statement where clause.

Here is my current linq statement

FactSales
    .Where(c => c.CompanyID == 433)
    .Where(c => c.StoreID == 360)
    .Where(c => c.Description == "2 Piece Combo" || c.Description=="3 Piece Combo")
    .GroupBy(m => new { m.BillID })

This selects all sales that were either 2 or 3 piece combos. But the group by doesn't provide the information I need because there are cases where where people buy multiples of the same item so those BillIDs are not representative of bills where both items were sold.

Is this possible with linq?

Upvotes: 0

Views: 62

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205729

If I understand correctly, you want to filter out the groups that contains both items from the Where.

There are several ways you could do that.

For instance, counting the distinct items in a group:

...
.Where(g => g.Select(c => c.Description).Distinct().Count() == 2)

or checking the group content for item existence (duplicating Where filter conditions):

...
.Where(g => g.Any(c => c.Description == "2 Piece Combo") &&
    g.Any(c => c.Description == "3 Piece Combo"))

Upvotes: 2

alikuli
alikuli

Reputation: 546

How about adding another condition, with an "&&" where you check for quantity sold is greater than 1. Note, the OR has been grouped together.

FactSales
.Where(c => c.CompanyID == 433)
.Where(c => c.StoreID == 360)
.Where(c => (c.Description == "2 Piece Combo" || c.Description=="3 Piece Combo" ) && c.purchaseQuantity > 1)
.GroupBy(m => new { m.BillID })

Upvotes: 0

Related Questions