我有一些可以在SQL中非常轻松地完成的操作，但是我只是想不通如何在LINQ中做到这一点。所以，我有3个表：Return，ReturnItem，和ReturnItemTest。Return具有1..n ReturnItems和ReturnItem具有0..1 ReturnItemTests。这些表如下所示：
Return
======
ReturnId int not null (PK)
ReturnName nvarchar(max) not null
ReturnItem
==========
ReturnItemId int not null (PK)
ReturnId int not null (FK)
ReturnItemStatus int not null
ReturnItemTest
==============
ReturnItemId int not null (PK, FK)
ReturnItemTestStatus int not null
每个退货都有退货项目，每个退货项目可能有0或1个测试。退货项目和退货项目测试均具有状态。我想计算出退货状态代码和退货测试状态代码的数量，并按两者的状态号进行分组。但是LEFT OUTER JOIN，由于退货商品可能没有测试，因此需要a。因此，在SQL中，我说：
SELECT
ri.[ReturnItemStatus] AS ItemStatus,
rit.[ReturnItemTestStatus] AS TestStatus,
COUNT([ReturnItem].[ReturnItemStatus]) as ComboCount
FROM
[Return] r
INNER JOIN [ReturnItem] ri ON r.ReturnId = ri.ReturnId
LEFT OUTER JOIN [ReturnItemTest] rit ON ri.ReturnItemId = rit.ReturnItemId
GROUP BY
ri.[ReturnItemStatus], rit.[ReturnItemTestStatus]
这给了我一个结果，显示了退货状态，退货测试状态的所有现有组合以及每种组合的计数。如何使用LINQ达到相同的目的？我已经走了这么远：
var returns =
(
from r in ctx.Returns
join ri in ctx.ReturnItems on r.ReturnID equals ri.ReturnID
join rit in ctx.ReturnItemTests on ri.ReturnItemID equals rit.ReturnItemTestID into ritJoined
from rit in ritJoined.DefaultIfEmpty()
select new {
ReturnItemStatus = ri.ReturnItemStatus,
ReturnItemTestStatus = rit == null ? null : (int?)rit.ReturnItemTestStatus
}
).ToList();
…向我显示了退货状态LEFT OUTER JOIN到测试状态，但是我不知道如何进行分组和计数。
这是我最终设法做到的方式：
var returns = (
// Grab from returns table
from r in ctx.Returns
// Inner join with return items
join ri in ctx.ReturnItems on r.ReturnID equals ri.ReturnID
// Filter down by return 'closed on' date
where (
r.ClosedOn > startDate &&
r.ClosedOn <= endDate
)
// Join with return item tests. The 'into' clause is powerful and should be used regularly for complex queries;
// really, the lack of an 'into' projection clause can usually be thought of as shorthand. Here, 'into' projects
// the 0..n join hierarchically as an IEnumerable in what is called a 'group join'.
join rit in ctx.ReturnItemTests on ri.ReturnItemID equals rit.ReturnItemID into ritGroupJoined
// 'Flatten out' the join result with the 'from' clause, meaning that group join results with eg. 3 matches will
// cause 3 items in the resultant enumeration, and group join results with zero matches will cause zero items
// in the resultant enumeration. The .DefaultIfEmpty() method means that these results will instead cause one
// item in the resultant enumeration, having the default value for that type (ie. null, as it's a reference type).
// Note that without the 'into' group join above, it's not possible to access the join results with zero matches as
// they are automatically discarded from the results during the default 'inner join'-style flattening.
from rit in ritGroupJoined.DefaultIfEmpty()
// Project these results into an intermediary object to allow ReturnItemTestStatus to be null (as a int? type);
// without this, we couldn't group them because any grouped items whose ReturnItemTestStatus was null would cause
// a type error, null being an invalid value for the ReturnItemTests.ReturnItemTestStatus property (an int type).
select new {
ReturnItemStatus = ri.ReturnItemStatus,
ReturnItemTestStatus = rit == null ? null : (TestStatusEnum?)rit.ReturnItemTestStatus,
} into retData
// Finally, we can now group this flattened data by both item status and item test status; to group by multiple
// fields in LINQ, use an anonymous type containing the fields to group by.
group retData by new { retData.ReturnItemStatus, retData.ReturnItemTestStatus } into retGrouped
// ... and project into an object to get our item status counts.
select new
{
ReturnItemStatus = retGrouped.Key.ReturnItemStatus,
ReturnItemTestStatus = retGrouped.Key.ReturnItemTestStatus,
Count = retGrouped.Count()
}
).ToList();
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句