来自包含表的 IN 子句未按预期工作

Question

我正在尝试这样做：

        var results = (from s in db.ExampleMaster
                           .Include("State")
                           .Include("Group")
                           .Where("it.Group.IdGroup in {1,2,3,4,5}")
                           .Where("it.IdState in {1,2}")
                            select s);

架构：

ExampleMaster:
    IdExampleMaster int,
    IdState int

State:
    IdState int,
    Description varchar(100)

Group:
    IdGroup int
    IdExampleMaster int

所需的 SQL 类似于：

SELECT * FROM ExampleMaster e
inner join State s on s.IdState = e.IdState
Inner join Group g on e.IdExampleMaster = g.IdExampleMaster 
where g.IdGroup in (1,2,3,4,5) and e.IdState in (1,2)

生成的 SQL 类似于：

SELECT *
    FROM ExampleMaster AS [Extent1]
    LEFT OUTER JOIN [dbo].[Group] AS [Extent2] ON [Extent1].[IdExampleMaster] = [Extent2].[IdGroup]
    LEFT OUTER JOIN [dbo].[Group] AS [Extent3] ON [Extent2].[IdGroup] = [Extent3].[IdGroup]
    LEFT OUTER JOIN [dbo].[Group] AS [Extent4] ON [Extent2].[IdGroup] = [Extent4].[IdGroup]
    LEFT OUTER JOIN [dbo].[Group] AS [Extent5] ON [Extent2].[IdGroup] = [Extent5].[IdGroup]
    LEFT OUTER JOIN [dbo].[Group] AS [Extent6] ON [Extent2].[IdGroup] = [Extent6].[IdGroup]
    LEFT OUTER JOIN [dbo].[Group] AS [Extent7] ON [Extent2].[IdGroup] = [Extent7].[IdGroup]
    LEFT OUTER JOIN [dbo].[State] AS [Extent8] ON [Extent1].[IdState] = [Extent8].[IdState]
    WHERE ([Extent1].[IdState] IN (1,2)) AND ([Extent3].[IdGroup] = 1 OR [Extent4].[IdGroup] = 2 OR [Extent5].[IdGroup] = 3 OR [Extent6].[IdGroup] = 4 OR [Extent7].[IdGroup] = 5) ) AS [Filter1]

我不明白为什么状态情况（不需要包含表）与 IN 子句配合良好，并且组进行了如此多的连接比列表中的项目要多，甚至它没有按预期工作。

有什么想法吗？

---

已编辑

我在我的数据库架构上发现了一个错误，现在我得到的错误

.Where("it.Group.IdGroup in {1,2,3,4,5}")

是

“IdGroup”不是“Transient.collection[myModel.Group(Nullable=True,DefaultValue=)]”的成员。要提取集合元素的属性，请使用子查询迭代集合

Answer 1

您可以尝试一下：

linq：

from e in ExampleMaster
join s in State on e.IdState equals s.IdState
join g in Group on e.IdExampleMaster = g.IdExampleMaster
where g.IdGroup.In(1,2,3,4,5) && e.IdState.In(1,2)
select e;

为 In 创建扩展方法：

public static class IntExtensions
{
    public static bool In(this int @this, params int[] values)
    {
      return values.Contains(@this);
    }
}

Answer 2

LINQPad 代码似乎根据问题给出了预期结果

（架构名称：'master'、'state'、'group' 由 LINQPad 本身复数）

Masters
    .Join(
        States, 
        master => master.Idstate, 
        state => state.Idstate,
        (master,state) => new
            {
                idmaster = master.Idmaster,
                idstate = state.Idstate,
                descr = state.Descr
            }
        )
    .Join(
        Groups,
        firstJoin => firstJoin.idmaster,
        groups=>groups.Idmaster,
        (firstJoin, groups) => new
            {
                masterid = firstJoin.idmaster,
                stateid = firstJoin.idstate,
                groupid = groups.Idgroup,
                descr = firstJoin.descr
            }
        )
    .Where(x => new int?[]{1,2,3,4,5}.ToList().Contains(x.groupid))
    .Where(x => new int?[]{1,2}.ToList().Contains(x.stateid))