LINQ to SQL: Complicated query with aggregate data for a report from multiple tables for an ordering system

Question

I want to convert the following query into LINQ syntax. I am having a great deal of trouble managing to get it to work. I actually tried starting from LINQ, but found that I might have better luck if I wrote it the other way around.

SELECT
    pmt.guid,
    pmt.sku,
    pmt.name,
    opt.color,
    opt.size,
    SUM(opt.qty) AS qtySold,
    SUM(opt.qty * opt.itemprice) AS totalSales,
    COUNT(omt.guid) AS betweenOrders
FROM
    products_mainTable pmt 
        LEFT OUTER JOIN 
            orders_productsTable opt ON opt.products_mainTableGUID = pmt.guid
            LEFT OUTER JOIN orders_mainTable omt ON omt.guid = opt.orders_mainTableGUID AND
                (omt.flags & 1) = 1
GROUP BY
    pmt.sku, opt.color, opt.size, pmt.guid, pmt.name
ORDER BY
    pmt.sku

The end result is a table that shows me information about a product as you can see above. How do I write this query, in LINQ form, using comprehension syntax ?

Additionally, I may want to add additional filters (to the orders_mainTable, for instance).

Here is one example that I tried to make work, and was fairly close but am not sure if it's the "correct" way, and was not able to group it by size and color from the orders_productsTable.

from pmt in products_mainTable
let Purchases = 
        from opt in pmt.orders_productsTable
        where ((opt.orders_mainTable.flags & 1) == 1)
        where ((opt.orders_mainTable.date_completedon > Convert.ToDateTime("01/01/2009 00:00:00")))
        select opt
orderby pmt.sku
select new {
    pmt.guid,
    pmt.sku,
    pmt.name,
    pmt.price,  
    AvgPerOrder = Purchases.Average(p => p.qty).GetValueOrDefault(0),
    QtySold = Purchases.Sum(p => p.qty).GetValueOrDefault(),
    SoldFor = Purchases.Sum(p => p.itemprice * p.qty).GetValueOrDefault()           
}

*Edit:

To be a little more explicit so you can understand what I am trying to do, here is some more explanation.

Products are stored in products_mainTable Orders are stored in orders_mainTable Products That Have Been Ordered are stored in orders_productsTable

I want to create several reports based on products, orders, etc. drilling into the data and finding meaningful bits to display to the end user.

In this instance, I am trying to show which products have been purchased over a period of time, and are the most popular. How many sold, for what price, and what is the breakout per order. Maybe not the best order, but I'm just experimenting and picked this one.

All of the tables have relationships to other tables. So from the product table, I can get to what orders ordered that product, etc.

The largest problem I am having, is understanding how LINQ works, especially with grouping, aggregate data, extensions, subqueries, etc. It's been fun, but it's starting to get frustrating because I am having difficulty finding detailed explanations on how to do this.

Answer 1

I'm also a beginner in LINQ. I don't know if this is the right way of grouping by several fields but I think you have to transform these grouping fields into a representing key. So, assuming that all your grouping fields are strings or ints you can make a key as follows:

        var qry = from pmt in products_mainTable
                  join opt in orders_productsTable on pmt.guid equals opt.products_mainTableGUID
                  join omt in orders_mainTable on opt.orders_mainTableGUID equals omt.guid
                  where (opt.orders_mainTable.flags & 1) == 1
                  group omt by pmt.sku + opt.price + opt.size + pmt.guid + pmt.name into g
                  orderby g.sku
                  select new
                  {
                      g.FirstOrDefault().guid,
                      g.FirstOrDefault().sku,
                      g.FirstOrDefault().name,
                      g.FirstOrDefault().color,
                      g.FirstOrDefault().price,
                      AvgPerOrder = g.Average(p => p.qty).GetValueOrDefault(0),
                      QtySold = g.Sum(p => p.qty).GetValueOrDefault(),
                      SoldFor = g.Sum(p => p.itemprice * p.qty).GetValueOrDefault()
                  };

I didn't test this so please see if this helps you in any way.

Answer 2

Bruno, thank you so much for your assistance! The FirstOrDefault() was probably the largest help. Following some of what you did, and another resource I came up with the following that seems to work beautifully! This LINQ query below gave me nearly an exact replication of the SQL I posted above.

Here's the other resource I found on doing a LEFT OUTER JOIN in LINQ: Blog Post

Final Answer:

 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 {
        g.FirstOrDefault().guid,
        g.Key.sku,
        g.Key.size, 
        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),
    }