这是您的查询所翻译的内容:
SELECT
[Limit1].[C1] AS [C1],
[Limit1].[C2] AS [C2],
[Limit1].[C3] AS [C3],
[Limit1].[C4] AS [C4],
[Limit1].[C5] AS [C5],
[Limit1].[C6] AS [C6],
[Limit1].[C7] AS [C7]
FROM ( SELECT TOP (2)
[Project13].[C1] AS [C1],
CASE WHEN ([Project13].[C2] IS NULL) THEN 0 ELSE [Project13].[C3] END AS [C2],
CASE WHEN ([Project13].[C4] IS NULL) THEN 0 ELSE [Project13].[C5] END AS [C3],
CASE WHEN ([Project13].[C6] IS NULL) THEN 0 ELSE [Project13].[C7] END AS [C4],
CASE WHEN ([Project13].[C8] IS NULL) THEN 0 ELSE [Project13].[C9] END AS [C5],
CASE WHEN ([Project13].[C10] IS NULL) THEN 0 ELSE [Project13].[C11] END AS [C6],
CASE WHEN ([Project13].[C12] IS NULL) THEN 0 ELSE [Project13].[C13] END AS [C7]
FROM ( SELECT
[Project12].[C1] AS [C1],
[Project12].[C2] AS [C2],
[Project12].[C3] AS [C3],
[Project12].[C4] AS [C4],
[Project12].[C5] AS [C5],
[Project12].[C6] AS [C6],
[Project12].[C7] AS [C7],
[Project12].[C8] AS [C8],
[Project12].[C9] AS [C9],
[Project12].[C10] AS [C10],
[Project12].[C11] AS [C11],
[Project12].[C12] AS [C12],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent13]
WHERE ([Extent13].[FruitID] IN (23, 215, 256, 643)) AND ([Project12].[C1] = 0)
AND (6 = [Extent13].[FruitType])) AS [C13]
FROM ( SELECT
[Project11].[C1] AS [C1],
[Project11].[C2] AS [C2],
[Project11].[C3] AS [C3],
[Project11].[C4] AS [C4],
[Project11].[C5] AS [C5],
[Project11].[C6] AS [C6],
[Project11].[C7] AS [C7],
[Project11].[C8] AS [C8],
[Project11].[C9] AS [C9],
[Project11].[C10] AS [C10],
[Project11].[C11] AS [C11],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent12]
WHERE ([Extent12].[FruitID] IN (23, 215, 256, 643))
AND ([Project11].[C1] = 0)
AND (6 = [Extent12].[FruitType])) AS [C12]
FROM ( SELECT
[Project10].[C1] AS [C1],
[Project10].[C2] AS [C2],
[Project10].[C3] AS [C3],
[Project10].[C4] AS [C4],
[Project10].[C5] AS [C5],
[Project10].[C6] AS [C6],
[Project10].[C7] AS [C7],
[Project10].[C8] AS [C8],
[Project10].[C9] AS [C9],
[Project10].[C10] AS [C10],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent11]
WHERE ([Extent11].[FruitID] IN (23, 215, 256, 643))
AND([Project10].[C1] = 0)
AND (5 = [Extent11].[FruitType])) AS [C11]
FROM ( SELECT
[Project9].[C1] AS [C1],
[Project9].[C2] AS [C2],
[Project9].[C3] AS [C3],
[Project9].[C4] AS [C4],
[Project9].[C5] AS [C5],
[Project9].[C6] AS [C6],
[Project9].[C7] AS [C7],
[Project9].[C8] AS [C8],
[Project9].[C9] AS [C9],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent10]
WHERE ([Extent10].[FruitID] IN (23, 215, 256, 643))
AND ([Project9].[C1] = 0)
AND (5 = [Extent10].[FruitType])) AS [C10]
FROM ( SELECT
[Project8].[C1] AS [C1],
[Project8].[C2] AS [C2],
[Project8].[C3] AS [C3],
[Project8].[C4] AS [C4],
[Project8].[C5] AS [C5],
[Project8].[C6] AS [C6],
[Project8].[C7] AS [C7],
[Project8].[C8] AS [C8],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent9]
WHERE ([Extent9].[FruitID] IN (23, 215, 256, 643))
AND ([Project8].[C1] = 0)
AND (4 = [Extent9].[FruitType])) AS [C9]
FROM ( SELECT
[Project7].[C1] AS [C1],
[Project7].[C2] AS [C2],
[Project7].[C3] AS [C3],
[Project7].[C4] AS [C4],
[Project7].[C5] AS [C5],
[Project7].[C6] AS [C6],
[Project7].[C7] AS [C7],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent8]
WHERE ([Extent8].[FruitID] IN (23, 215, 256, 643))
AND ([Project7].[C1] = 0)
AND (4 = [Extent8].[FruitType])) AS [C8]
FROM ( SELECT
[Project6].[C1] AS [C1],
[Project6].[C2] AS [C2],
[Project6].[C3] AS [C3],
[Project6].[C4] AS [C4],
[Project6].[C5] AS [C5],
[Project6].[C6] AS [C6],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent7]
WHERE ([Extent7].[FruitID] IN (23, 215, 256, 643))
AND ([Project6].[C1] = 0)
AND (3 = [Extent7].[FruitType])) AS [C7]
FROM ( SELECT
[Project5].[C1] AS [C1],
[Project5].[C2] AS [C2],
[Project5].[C3] AS [C3],
[Project5].[C4] AS [C4],
[Project5].[C5] AS [C5],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent6]
WHERE ([Extent6].[FruitID] IN (23, 215, 256, 643))
AND ([Project5].[C1] = 0)
AND (3 = [Extent6].[FruitType])) AS [C6]
FROM ( SELECT
[Project4].[C1] AS [C1],
[Project4].[C2] AS [C2],
[Project4].[C3] AS [C3],
[Project4].[C4] AS [C4],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent5]
WHERE ([Extent5].[FruitID] IN (23, 215, 256, 643))
AND ([Project4].[C1] = 0)
AND (2 = [Extent5].[FruitType])) AS [C5]
FROM ( SELECT
[Project3].[C1] AS [C1],
[Project3].[C2] AS [C2],
[Project3].[C3] AS [C3],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent4]
WHERE ([Extent4].[FruitID] IN (23, 215, 256, 643))
AND ([Project3].[C1] = 0)
AND (2 = [Extent4].[FruitType])) AS [C4]
FROM ( SELECT
[Project2].[C1] AS [C1],
[Project2].[C2] AS [C2],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent3]
WHERE ([Extent3].[FruitID] IN (23, 215, 256, 643))
AND ([Project2].[C1] = 0)
AND (1 = [Extent3].[FruitType])) AS [C3]
FROM ( SELECT
[Distinct1].[C1] AS [C1],
(SELECT
COUNT(1) AS [A1]
FROM [dbo].[Fruits]AS [Extent2]
WHERE ([Extent2].[FruitID] IN (23, 215, 256, 643))
AND ([Distinct1].[C1] = 0)
AND (1 = [Extent2].[FruitType])) AS [C2]
FROM ( SELECT DISTINCT
0 AS [C1]
FROM [dbo].[Fruits]AS [Extent1]
WHERE [Extent1].[FruitID] IN (23, 215, 256, 643)
) AS [Distinct1]
) AS [Project2]
) AS [Project3]
) AS [Project4]
) AS [Project5]
) AS [Project6]
) AS [Project7]
) AS [Project8]
) AS [Project9]
) AS [Project10]
) AS [Project11]
) AS [Project12]
) AS [Project13]
) AS [Limit1]
请注意,对于每个分组,IN操作符都会被重新评估,这样在ID列表很长的情况下会产生非常大的工作量。
你需要将任务分为两个步骤。
List<int> theFruitIDs = new List<int> { 23, 215, 256, 643 };
var theCounter = (from f in MyDC.Fruits
where theFruitIDs.Contains(f.FruitID)
group f by f.FruitType into theFruits
select new { fruitType = theFruits.Key, fruitCount = theFruits.Count() })
.ToList();
这将转换为更快的SQL。注意结尾处的ToList()会强制执行单个查询。
SELECT
[GroupBy1].[K1] AS [FruitType],
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
[Extent1].[FruitType] AS [K1],
COUNT(1) AS [A1]
FROM [dbo].[Fruits] AS [Extent1]
WHERE [Extent1].[FruitID] IN (23, 215, 256, 643)
GROUP BY [Extent1].[FruitType]
) AS [GroupBy1]
现在,您可以将生成的列表在内存中进行透视,以获取您的MyCounterMode。
var thePivot = new MyCounterMode
{
CountType1 = theCounter.Where(x => x.fruitType == 1).Select(x => x.fruitCount).SingleOrDefault(),
CountType2 = theCounter.Where(x => x.fruitType == 2).Select(x => x.fruitCount).SingleOrDefault(),
CountType3 = theCounter.Where(x => x.fruitType == 3).Select(x => x.fruitCount).SingleOrDefault(),
};
Count()
返回一个int
而不是int?
,即使转换为SQL;因此,空合并运算符是不必要的(可能甚至不起作用)。这是因为当强制转换为int?
时,它永远不会有一个空值,因为0的值不是空值。我很惊讶这个代码在没有将转换括在额外一层括号中就能编译通过,但这可能是复制粘贴的事情。 - JNYRanger