C # LINQ equivalent of a somewhat complex SQL query

Question

So I have 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 am trying to convert it to C # LINQ syntax and set a DataContext with two tables. I tried a couple of times on it (latest revision below), but the sql that is generated looks monstrously complex from time to time. 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 reorganize the LINQ statement to get something better?

Answer 1

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

Answer 2

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

I'm not sure if I got the LEFT OUTER JOIN correctly. I just included an OR condition that checks 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 this a little more, and it can be a little more clear, and it can even compile! (The latter will not be due to the proposals ||)

 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 }