C# LINQ equivalent of a somewhat complex SQL query

Question

So I have a SQL Query as Follows

SELECT P.Date, P.CategoryName, P.ProductName, SUM(Quantity) Quantity, SUM(Sales) TotalSales, IsLevelThree
FROM Products P LEFT JOIN LevelThreeTracking LTT
 ON P.Date = LTT.Date AND P.CategoryName = P.CategoryName AND P.SecurityID = LTT.SecurityID
WHERE P.Date = '12-31-2007' AND P.CategoryName= 'CategoryName'
GROUP BY P.Date, P.CategoryName, P.ProductName, LTT.IsLevelThree
HAVING SUM(Quantity) <> 0
ORDER BY P.ProductName

I'm Trying to Convert it to C# LINQ syntax and have the DataContext setup with the 2 tables. I've tried a couple times at it (latest revision below) but the sql that gets generated looks monstrously complex and times out. dtpBeginning is a DateTimePicker.

var results = from p in dbFAS.Products
group p by new {p.Date, p.CategoryName, p.ProductName}
into gp
join ltt in dbFAS.LevelThreeTracking on
new {gp.Key.Date, gp.Key.CategoryName, gp.Key.ProductName} equals
new {ltt.Date, ltt.CategoryName, ltt.ProductName} into everything
from e in everything.DefaultIfEmpty()
where gp.Key.Date == dtpBeginning.Value.Date && gp.Key.CategoryName == "CategoryName" && gp.Sum(p=>p.Quantity) != 0
select new
{
    gp.Key.Date,
    gp.Key.CategoryName,
    gp.Key.ProductName,
    Quantity = gp.Sum(hp=>hp.Quantity),
    TotalSales = gp.Sum(hp=>hp.Sales),
    e.Level3
};

Is there something simple I'm missing? Any Ideas on how to refactor the LINQ statement to get something better?

Answer 1

Does it really need to be converted to LINQ? I would suggest you put that query in a stored procedure because the equivalent LINQ query is painfully unreadable and unmaintainable.

Answer 2

Try this query and let me know if it works. I changed the join into a where clause, this should eliminate all of the complex sub queries that LINQ generates when translating to SQL.

I'm not sure if I got the LEFT OUTER JOIN part right. I just included an OR condition that tests if one side exists.

from p in dbFAS.Products
from ltt in dbFAS.LevelThreeTracking
where p.CategoryName == "CategoryName"
    && (p.Date == ltt.Date || p.Date)
    && (p.CategoryName == ltt.CategoryName || p.CategoryName)
    && (p.ProductName == ltt.ProductName || p.ProductName)
    && p.Quantity > 0
group p by new {p.Date, p.CategoryName, p.ProductName, p.Quantity, p.Sales, ltt.Level3}
into gp
select new
{
    gp.Key.Date,
    gp.Key.CategoryName,
    gp.Key.ProductName,
    Quantity = gp.Sum(hp=>hp.Quantity),
    TotalSales = gp.Sum(hp=>hp.Sales),
    ltt.Level3
};

EDIT: I thought about it some more, and this might be a little more clear and it could even compile! (The last one won't because of the || clauses)

from gp in
    (from p in dbFAS.Products
    join ltt in dbFAS.LevelThreeTracking on 
     new {p.Date, p.CategoryName, p.ProductName}
     equals new {ltt.Date, ltt.CategoryName, ltt.ProductName}
     into temp
    where p.CategoryName == "CategoryName"
     && p.Quantity > 0
    from t in temp.DefaultIfEmpty()
    select new
    {
     p.Date,
     p.CategoryName,
     p.ProductName,
     p.Quantity,
     p.Sales,
     t.Level3
    })
group gp by new {gp.Date, gp.CategoryName, gp.ProductName, gp.Level3}
select new
{
    gp.Key.Date,
    gp.Key.CategoryName,
    gp.Key.ProductName,
    Quantity = gp.Sum(hp=>hp.Quantity),
    TotalSales = gp.Sum(hp=>hp.Sales),
    gp.Level3
}

Answer 3

I agree with Andrew. The Linq equivalent is a mess and unreable. It should be in a stored proc.