Category: Linq

Linq Grouping examples

I have a collection of items, here it is:

AgencyID VendorID StateID Amount Fee
1        1        1       20.00  5.00
1        1        1       10.00  2.00
1        1        1       30.00  8.00    
2        2        1       20.00  5.00
2        2        1       5.00   5.00
1        1        2       20.00  5.00
2        2        2       20.00  5.00
2        2        2       40.00  9.00
1        2        2       35.00  6.00
1        2        2       12.00  3.00

I’d like these items to be grouped based on the AgencyID, VendorID, and StateID, and the Total calculated from Amount and Fee (Amount + Fee)

So using the data above, I’d like to have these results:

AgencyID VendorID StateID Total
1        1        1       75.00    
2        2        1       35.00
1        1        2       25.00
2        2        2       74.00
1        2        2       56.00
var agencyContracts = _agencyContractsRepository.AgencyContracts
    .GroupBy(ac => new
                   {
                       ac.AgencyContractID, // required by your view model. should be omited
                                            // in most cases because group by primary key
                                            // makes no sense.
                       ac.AgencyID,
                       ac.VendorID,
                       ac.RegionID
                   })
    .Select(ac => new AgencyContractViewModel
                   {
                       AgencyContractID = ac.Key.AgencyContractID,
                       AgencyId = ac.Key.AgencyID,
                       VendorId = ac.Key.VendorID,
                       RegionId = ac.Key.RegionID,
                       Amount = ac.Sum(acs => acs.Amount),
                       Fee = ac.Sum(acs => acs.Fee)
                   });

Second Example

public class ConsolidatedChild
{
    public string School { get; set; }
    public string Friend { get; set; }
    public string FavoriteColor { get; set; }
    public List<Child> Children { get; set; }
}

public class Child
{
    public string School { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string Friend { get; set; }
    public string Mother { get; set; }
    public string FavoriteColor { get; set; }
}
var consolidatedChildren =
    from c in children
    group c by new
    {
        c.School,
        c.Friend,
        c.FavoriteColor,
    } into gcs
    select new ConsolidatedChild()
    {
        School = gcs.Key.School,
        Friend = gcs.Key.Friend,
        FavoriteColor = gcs.Key.FavoriteColor,
        Children = gcs.ToList(),
    };

var consolidatedChildren =
    children
        .GroupBy(c => new
        {
            c.School,
            c.Friend,
            c.FavoriteColor,
        })
        .Select(gcs => new ConsolidatedChild()
        {
            School = gcs.Key.School,
            Friend = gcs.Key.Friend,
            FavoriteColor = gcs.Key.FavoriteColor,
            Children = gcs.ToList(),
        });

With and without Lambda Expression

var id = 1;
var query =
from user in database.Users
join invoice in database.Invoices on user.Id equals invoice.UserId
where user.Id == id
select new { User = user, Invoice = invoice };

Here’s the same query, using Lambda Expression LINQ extension methods:

var id = 1;
var query = database.Users // your starting point - table in the "from" statement
.Join(database.Invoices, // the source table of the inner join
user => user.Id, // Select the primary key (the first part of the "on" clause in an sql "join" statement)
invoice => invoice.UserId, // Select the foreign key (the second part of the "on" clause)
(user, invoice) => new { User = user, Invoice = invoice }) // selection
.Where(userAndInvoice => userAndInvoice.User.Id == id); // where statement