C#LINQ等效于稍微复杂的SQL查询 [英] C# LINQ equivalent of a somewhat complex SQL query

问题描述

所以我有一个如下的SQL查询

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

我正在尝试将其转换为C#LINQ语法，并使用2个表进行DataContext设置.我已经尝试了几次(下面的最新版本)，但是生成的sql看起来异常复杂并且超时. dtpBeginning是一个DateTimePicker.

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
};

我缺少一些简单的东西吗?关于如何重构LINQ语句以获得更好的东西的任何想法?

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

推荐答案

是否真的需要将其转换为LINQ?我建议您将该查询放入存储过程中，因为等效的LINQ查询非常难以读取和维护.

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.

这篇关于C#LINQ等效于稍微复杂的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！