联接子查询结果

Question

我有一个名为IssueStatuses的表和另一个名为Issues的表。 问题有一个 StatusID 和 SubStatusID ，两者都来自 IssueStatuses 表，该表有一个附加字段，说明它是否是SubStatus，如下所示：

问题状态

• 问题状态标识
• 问题状态
• 是子状态

我正在尝试获取特定问题列表的SubStatuses列表。 在 SQL 中，它是：

SELECT iss.IssueStatus, COUNT(iss.IssueStatus) AS Total
FROM Issues AS Issues 
INNER JOIN Rooms r ON Issues.RoomID = r.RoomID 
INNER JOIN Locations l ON l.LocationID = r.LocationID 
INNER JOIN Customers c ON l.CustomerID = c.CustomerID 
INNER JOIN (SELECT * FROM IssueStatuses WHERE IsSubStatus = 0) ist ON Issues.IssueStatusID = ist.IssueStatusID 
INNER JOIN (SELECT * FROM IssueStatuses WHERE IsSubStatus = 1) iss ON Issues.IssueSubStatusID = iss.IssueStatusID 
WHERE c.Customer = 'ABC' 
AND l.Location = 'MySite'
GROUP BY iss.IssueStatus

但我在将其转换为 LINQ 时遇到问题。 所需的输出如下所示：

问题状态 |总-------------------开盘 15延迟 25准时 8

以下是我使用 LINQ 尝试的方法：

var query = from i in Issues
join r in Rooms on i.RoomID equals r.RoomID
join l in Locations on r.RoomID equals l.LocationID
join c in Customers on l.CustomerID equals c.CustomerID
where i.IssueStatusID == (from ist in IssueStatuses
                            where ist.IsSubStatus == false
                            select ist)
&& i.IssueSubStatusID == (from iss in IssueStatuses
                            where iss.IsSubStatus == true
                            select iss)
&& c.Custome == "ABC"
&& l.Location == "MySite"
    group i by i.IssueStatus

但我知道这是错误的，因为LINQPad抛出一个错误，指出：

can't convert int to type Models.IssueStatus

我需要做的是使用iss.IssueStaus进行分组，但我无法访问它。 有人可以告诉我我做错了什么吗？

Answer 1

这个怎么样（未经测试，但我应该很接近）：

var query = from i in Issues
            join r in Rooms on i.RoomID equals r.RoomID
            join l in Locations on r.LocationID equals l.LocationID
            join c in Customers on l.CustomerID equals c.CustomerID
            join ist in IssueStatuses on i.IssueStatusID equals ist.IssueStatusID
            join iss in IssueStatuses on i.IssueSubStatusID equals iss.IssueStatusID
            where !ist.IsSubStatus && iss.IsSubStatus
                && c.Customer == "ABC"
                && l.Location == "MySite"
            group i by iss.IssueStatus into g
            select new {IssueStatus = g.Key, Total = g.Count()}

Answer 2

您的两个内部 from 语句返回对象而不是 ID ...选择您需要的 ID，如 ist.IssueStatusID ：

var query = from i in Issues
join r in Rooms on i.RoomID equals r.RoomID
join l in Locations on r.RoomID equals l.LocationID
join c in Customers on l.CustomerID equals c.CustomerID
where (from ist in IssueStatuses
                            where ist.IsSubStatus == false
                            select ist.IssueStatusID).Contains(i.IssusStatusID)
&& (from iss in IssueStatuses
                            where iss.IsSubStatus == true
                            select iss.IssueStatusID).Contains(i.IssueSubStatusID)
&& c.Customer == "ABC"
&& l.Location == "MySite"
    group i by i.IssueStatus