Category Archives: LINQ

LINQ: building complex GROUPING queries utilizing joins deferred execution and anonymous types

http://jpcraighomebuilders.com/portfolio/lot-8-sold/?portfolioCats=46 var query =
from c in dc.Customers
join ct in dc.CustomerTypes on c.CustomerTypeId equals ct.CustomerTypeId
join pt in dc.PaymentTerms on c.PaymentTermId equals pt.PaymentTermId
//select ALL records variables for later use
select new { c, ct, pt  }

follow url if ( filterByCountry )
  query = query.Where ( q=>q.c.Country == countryFilterValue )
if ( filterPaymentTerms )
  query = query.Where( q=>q.pt.SomeField == paymentTermsFilterFieldValue )
if ( filterCustomerType )
  query = query.Where( q=>q.ct.SomeField == customerTypeFilterFieldValue )

follow //select the columns we need
var result =
from q in query
select new
{
   CustomerId = q.c.CustomerID,
   Name = q.c.CustomerName,
   Type = q.ct.CustomerType,
   PaymentTerm = q.pt.PaymentTerm
}

---------------------------------------------------------------------------

//joins of several tables

from c in dc.Customers

join o in dc.Orders on c.CustomerId equals o.CustomerId

join od in dc.OrderDetails on o.OrderId equals od.OrderId

join p in dc.Products on od.ProductId equals p.ProductId

join pc in dc.ProductCategories on

//grouping

group new { c, o, od, p, pc } by c.CustomerName into grp

select new

{

CustomerName = grp.Key,

BeveragesCount = grp.Count ( g=>g.pc.Category == "Beverages" )

StationeryCount = grp.Count ( g=>g.pc.Category == "Stationary" )

}