LINQ-to-SQL – Love it, or Hate It

| 1 Comment | No TrackBacks

I choose to love it.

Here is a post that I posted on Stack Over Flow, asking for some assistance in generating the following query seen below: 

Complicated query with aggregate data for a report from multiple tables for an ordering system

from pmt in products_mainTable
join opt in orders_productsTable on pmt.guid equals opt.products_mainTableGUID into tempProducts
from orderedProducts in tempProducts.DefaultIfEmpty()
join omt in orders_mainTable on orderedProducts.orders_mainTableGUID equals omt.guid into tempOrders
from ordersMain in tempOrders.DefaultIfEmpty()
group pmt by new { pmt.sku, orderedProducts.color, orderedProducts.size } into g
orderby g.FirstOrDefault().sku
select new {
    QTY = g.FirstOrDefault().orders_productsTable.Sum(c => c.qty),
    SUM = g.FirstOrDefault().orders_productsTable.Sum(c => c.itemprice * c.qty),
    AVG = g.FirstOrDefault().orders_productsTable.Average(c => c.itemprice * c.qty),
    Some = g.FirstOrDefault().orders_productsTable.Average(p => p.qty).GetValueOrDefault(0),

I’ve got to say that working with LINQ-TO-SQL has been an absolute pleasure.  I even fired up LINQPad and have really been enjoying the use of that program.  The $19 for the IntelliSense was definitely worth it.  What a great tool.

I can now take the query above into my code, and render it into reports, grids, etc. and whatever else I need it for using my DevExpress framework.  Exciting stuff, to be sure.

I’ll write some more on this topic when I have some additional time, but since I had a difficult time Googling for the answer on my Stackoverflow question, I thought I’d write about it here.

The above represents how to do a full LEFT OUTER JOIN in LINQ and C# while performing GROUPING and AGGREGATE functions.  Hopefully this will be a good starting place for someone else looking to do the same thing.

The important bits here are DefaultIfEmpty() which helps to perform the LEFT OUTER JOIN and FirstOrDetaulf() which allows you access to the underlying type.  While I knew about DefaultIfEmpty() I did not know about FirstOrDefault().. using FoD was the real key to making this query work the way that I wanted it to.

Now I am able to quickly and easily query my sales data.  This query for instance queries the products table, finds products that have sold and generates a quick report that I can use as a DataSource for other components.

Viva la LINQ!

Matthew MacSuga

No TrackBacks

TrackBack URL:

1 Comment

You don't need FirstOrDefault to access the key values after a group by. The group variable 'g' has a 'Key' property.

Instead of g.FirstOrDefault().sku use g.Key.sku

Leave a comment

About this Entry

This page contains a single entry by Matthew M. published on February 4, 2009 8:44 PM.

Automatically Generating Dynamic Enumerations was the previous entry in this blog.

LINQ-To-SQL: Updating Disconnected Entity Objects is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.