英文:
How to force delete COALESCE function in query. C# EntityFramework 7.0.2 LINQ
问题
以下是您提供的 SQL 代码的翻译部分:
DECLARE @documentId INT = 8
SELECT
i.[status] AS [status],
i.[barcode] AS [barcode],
i.[containerId] AS [containerTypeId],
(
SUM(ISNULL(lp.[Weight], 0) * il.[factCount]) +
ISNULL(lpt.[Weight], 0)
) AS [weight],
i.[comment] AS [comment]
FROM [Items] AS i WITH(NOLOCK)
LEFT JOIN [dbo].[ItemsLines] AS il WITH(NOLOCK) ON i.[id] = il.[itemsId]
LEFT JOIN [dbo].[LagerOptions] AS lp WITH(NOLOCK) ON lp.[lagerId] = il.[lagerId]
LEFT JOIN [dbo].[LagerOptions] AS lpt WITH(NOLOCK) ON lpt.[lagerId] = i.[containerId]
WHERE @documentId = i.[documentId]
GROUP BY i.[status], i.[barcode], i.[containerId], i.[comment], lpt.[Weight]
对于您提供的 C# LINQ 代码,以下是翻译的部分:
var lots = await (
from i in _dbContext.Items
join lpt in _dbContext.LagerOptions on i.ContainerId equals lpt.LagerId into lptTable
from lpt in lptTable.DefaultIfEmpty()
// 当前批次的所有仓库的总和
let lotWeight = (
from il in _dbContext.ItemsLines
join lp in _dbContext.LagerOptions on il.LagerId equals lp.LagerId into lpTable
from lp in lpTable.DefaultIfEmpty()
where il.ItemsId == i.Id
select (il.FactCount * lp.Weight.GetValueOrDefault(0))
).Sum()
where i.DocumentsId == documentId
select new
{
Status = i.Status,
Barcode = i.Barcode,
ContainerTypeId = i.ContainerId,
// 包括容器重量的总重量
Weight = lotWeight + lpt.Weight.GetValueOrDefault(0),
Comment = i.Comment
}
).ToListAsync();
对于您提供的 SQL 查询的翻译,以下是翻译的部分:
SELECT [t].[status] AS [Status], [t].[barcode] AS [Barcode], [t].[containerId] AS [ContainerTypeId], (
SELECT COALESCE(SUM([t0].[factCount] * COALESCE([l0].[Brutto], 0.0)), 0.0)
FROM [dbo].[ItemsLines] AS [t0]
LEFT JOIN [dbo].[LagerOptions] AS [l0] ON [t0].[lagerId] = [l0].[lagerId]
WHERE [t0].[itemsId] = [t].[id]) + COALESCE([l].[weight], 0.0) AS [Weight], [t].[comment] AS [Comment]
FROM [dbo].[Items] AS [t]
LEFT JOIN [dbo].[LagerOptions] AS [l] ON [t].[containerId] = [l].[lagerId]
WHERE [t].[documentId] = @__documentId_0
希望这些翻译对您有所帮助。如果您需要更多帮助,请随时提出问题。
英文:
Ok, I have such SQL code:
DECLARE @documentId INT = 8
SELECT
i.[status] AS [status],
i.[barcode] AS [barcode],
i.[containerId] AS [containerTypeId],
(
SUM(ISNULL(lp.[Weight], 0) * il.[factCount]) +
ISNULL(lpt.[Weight], 0)
) AS [weight],
i.[comment] AS [comment]
FROM [Items] AS i WITH(NOLOCK)
LEFT JOIN [dbo].[ItemsLines] AS il WITH(NOLOCK) ON i.[id] = il.[itemsId]
LEFT JOIN [dbo].[LagerOptions] AS lp WITH(NOLOCK) ON lp.[lagerId] = il.[lagerId]
LEFT JOIN [dbo].[LagerOptions] AS lpt WITH(NOLOCK) ON lpt.[lagerId] = i.[containerId]
WHERE @documentId = i.[documentId]
GROUP BY i.[status], i.[barcode], i.[containerId], i.[comment], lpt.[Weight]
The most similar code I've written in LINQ is:
var lots = await (
from i in _dbContext.Items
join lpt in _dbContext.LagerOptions on i.ContainerId equals lpt.LagerId into lptTable
from lpt in lptTable.DefaultIfEmpty()
// The sum of all lagers for current lot
let lotWeight = (
from il in _dbContext.ItemsLines
join lp in _dbContext.LagerOptions on il.LagerId equals lp.LagerId into lpTable
from lp in lpTable.DefaultIfEmpty()
where il.ItemsId == i.Id
select (il.FactCount * lp.Weight.GetValueOrDefault(0))
).Sum()
where i.DocumentsId == documentId
select new
{
Status = i.Status,
Barcode = i.Barcode,
ContainerTypeId = i.ContainerId,
// total weight with container Weight
Weight = lotWeight + lpt.Weight.GetValueOrDefault(0),
//
Comment = i.Comment
}
).ToListAsync();
For SQL query the results can contain empty lots or lots with null-weight lagers (problem lagers) that makes "Weight" field of lot is equal NULL that helps detect problem lots.
But when I check the C# LINQ code EntityFramework create a COALESCE() function over SUM() function
and the query converted to SQL looks like this:
SELECT [t].[status] AS [Status], [t].[barcode] AS [Barcode], [t].[containerId] AS [ContainerTypeId], (
SELECT COALESCE(SUM([t0].[factCount] * COALESCE([l0].[Brutto], 0.0)), 0.0)
FROM [dbo].[ItemsLines] AS [t0]
LEFT JOIN [dbo].[LagerOptions] AS [l0] ON [t0].[lagerId] = [l0].[lagerId]
WHERE [t0].[itemsId] = [t].[id]) + COALESCE([l].[weight], 0.0) AS [Weight], [t].[comment] AS [Comment]
FROM [dbo].[Items] AS [t]
LEFT JOIN [dbo].[LagerOptions] AS [l] ON [t].[containerId] = [l].[lagerId]
WHERE [t].[documentId] = @__documentId_0
As a result, the weight of the problem lot will be equal to the container weight of this lot.
I can solve the problem with crutch methods, but I'm sure that there is a simple solution, which, unfortunately, I did not find.
I tryed to add different checks on null and rewrite the query for different JOIN patterns, but the main problem - EntityFramework create a COALESCE() function over SUM() function. And I don't know how to fix it in root. I work with C# EF approximately 1 month. EF 7.0.2
Help me, please.
!!EDITED
Correct LINQ query looks like this (Thank, Svyatoslav Danyliv):
var query =
from i in _dbContext.Items
join il in _dbContext.ItemsLines on i.Id equals il.ItemsId into ilj
from il in ilj.DefaultIfEmpty()
join lp in _dbContext.LagerOptions on il.LagerId equals lp.LagerId into lpj
from lp in lpj.DefaultIfEmpty()
join lpt in _dbContext.LagerOptions on i.ContainerId equals lpt.LagerId into lptj
from lpt in lptj.DefaultIfEmpty()
where i.DocumentsId == documentId
group new { lp, il, lpt } by new { i.Status, i.Barcode, i.ContainerId, i.Comment, lpt.Weight } into g
select new
{
g.Key.Status,
g.Key.Barcode,
ContainerTypeId = g.Key.ContainerId,
Weight = g.Sum(x => ((decimal?)x.lp.Weight) ?? 0 * x.il.FactCount) + g.Key.Weight ?? 0
g.Key.Comment,
};
But the query converted to SQL looks like this:
SELECT
[t].[status] AS [Status],
[t].[barcode] AS [Barcode],
[t].[containerId] AS [ContainerTypeId],
COALESCE(SUM(COALESCE([l].[weight], 0.0) * [t0].[factCount])), 0.0) +
COALESCE([l0].[weight], 0.0) AS [Weight],
[t].[comment] AS [Comment]
FROM [dbo].[Items] AS [t]
LEFT JOIN [dbo].[ItemsLines] AS [t0] ON [t].[id] = [t0].[itemsId]
LEFT JOIN [dbo].[LagerOptions] AS [l] ON [t0].[lagerId] = [l].[lagerId]
LEFT JOIN [dbo].[LagerOptions] AS [l0] ON [t].[containerId] = [l0].[lagerId]
WHERE [t].[documentsId] = @__documentId_0
GROUP BY [t].[status], [t].[barcode], [t].[containerId], [t].[comment], [l0].[weight]
EntityFramework create a COALESCE() function over SUM() function.
How to remove this?
答案1
得分: 1
这应该是等效于你的SQL查询的LINQ查询。我不明白为什么你不是使用分组,而是强制LINQ翻译器生成另一个查询。
var query =
from i in _dbContext.Items
join il in _dbContext.ItemsLines on i.Id equals il.ItemsId into ilj
from il in ilj.DefaultIfEmpty()
join lp in _dbContext.LagerOptions on il.LagerId equals lp.LagerId into lpj
from lp in lpj.DefaultIfEmpty()
join lpt in _dbContext.LagerOptions on i.ContainerId equals lpt.LagerId into lptj
from lpt in lptj.DefaultIfEmpty()
where i.DocumentsId == documentId
group new { lp, il, lpt } by new { i.Status, i.Barcode, i.ContainerId, i.Comment, lpt.Weight } into g
select new
{
g.Key.Status,
g.Key.Barcode,
ContainerTypeId = g.Key.ContainerId,
Weight = g.Sum(x => ((double?)x.lp.Weight) ?? 0 * x.il.FactCount) + g.Key.Weight ?? 0,
g.Key.Comment,
};
英文:
This should equivalent LINQ query to your SQL. I do not understand why instead of grouping you have forced LINQ Translator to generate another query.
var query =
from i in _dbContext.Items
join il in _dbContext.ItemsLines on i.Id equals il.ItemsId into ilj
from il in ilj.DefaultIfEmpty()
join lp in _dbContext.LagerOptions on il.LagerId equals lp.LagerId into lpj
from lp in lpj.DefaultIfEmpty()
join lpt in _dbContext.LagerOptions on i.ContainerId equals lpt.LagerId into lptj
from lpt in lptj.DefaultIfEmpty()
where i.DocumentsId == documentId
group new { lp, il, lpt } by new { i.Status, i.Barcode, i.ContainerId, i.Comment, lpt.Weight } into g
select new
{
g.Key.Status,
g.Key.Barcode,
ContainerTypeId = g.Key.ContainerId,
Weight = g.Sum(x => ((double?)x.lp.Weight) ?? 0 * x.il.FactCount) + g.Key.Weight ?? 0
g.Key.Comment,
};
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论