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.

Thank you!

Answer 1

Hi Initri.

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),
    }

Answer 3

This was very helpful to me thanks. I had a similar issue I was trying to sort through only my case was much simpler as I didn't have any joins in it. I was simply trying to group one field, get the min of another, and the count. (min and count in the same query)

Here is the SQL I wanted to recreate in Linq syntax:

select t.Field1, min(t.Field2), COUNT(*)
from SomeTable t
group by t.Field1
order by t.Field1

Thanks to your post I eventually managed to come up with this:

from t in SomeTable
group t by new { t.Field1 } into g
orderby g.Key.Field1
select new
{ 
  g.Key.Field1,
  code = g.Min(c => c.Field2),
  qty = g.Count()
}

Which creates the following SQL behind the scenes:

SELECT [t1].[Field1], [t1].[value] AS [code], [t1].[value2] AS [qty]
FROM (
    SELECT MIN([t0].[Field2]) AS [value], COUNT(*) AS [value2], [t0].[Field1]
    FROM [SomeTable] AS [t0]
    GROUP BY [t0].[Field1]
    ) AS [t1]
ORDER BY [t1].[Field1]

Perfect, exactly what I was looking to do. The key for me was that you showed it possible to do this inside the new {} which is something I had never considered trying. This is huge, I now feel like I have a significantly better understanding going forward.