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 }
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 )
//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"
)
}