SQL Server where子句等于而不是IN [英] SQL server where clause equals instead of IN

问题描述

这是从LINQ生成的T-SQL

Here is the generated T-SQL from LINQ

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Name] AS [Name]
    FROM [dbo].[Hospital] AS [Extent1]
    WHERE ( EXISTS (SELECT 
        1 AS [C1]
        FROM ( SELECT 
            [Extent2].[ID] AS [ID]
            FROM [dbo].[HospitalDepartment] AS [Extent2]
            WHERE [Extent1].[ID] = [Extent2].[HospitalID]
        )  AS [Project1]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM ( SELECT 
                [Extent3].[ID] AS [ID]
                FROM [dbo].[Unit] AS [Extent3]
                WHERE [Project1].[ID] = [Extent3].[HospitalDepartmentID]
            )  AS [Project2]
            WHERE  EXISTS (SELECT 
                1 AS [C1]
                FROM [dbo].[Device] AS [Extent4]
                WHERE ([Project2].[ID] = [Extent4].[UnitID]) AND ([Extent4].[DeviceTypeID] IN (10,20))
            )
        ) 

我需要在下面的代码中使用equal而不是IN，因为当我使用IN时，如果包含 10或20，它将返回结果，但是我想要的是如果它 >完全等于 10和20

I need to use equal instead of IN in the following code because when i used IN it returns result if it contains 10 or 20 but what i want is i need to get result if it equals exactly 10 and 20

WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM [dbo].[Device] AS [Extent4]
            WHERE ([Project2].[ID] = [Extent4].[UnitID]) AND ([Extent4].[DeviceTypeID] IN (10,20))
        )

这是Linq源代码:

    var query = db.Hospitals.AsQueryable();


    if (DeviceTypeIDs != null)
    {

     IEnumerable<Guid> selectedDeviceTypeIDs = DeviceTypeIDs.Split(',').Select(Guid.Parse).AsEnumerable();
       query = query.Where(j => j.HospitalDepartments.Any(jj => jj.Units.Any(m => m.Devices.Any(w => selectedDeviceTypeIDs.Contains(w.DeviceTypeID)))));
    }

    if (UnitTypeID != null)
    {
        query = query.Where(j => j.HospitalDepartments.Any(www => www.Units.Any(u => u.UnitTypeID == UnitTypeID)));
    }

    if (DirectorateOfHealthID != null)
    {
        query = query.Where(h => h.DirectorateHealthID == DirectorateOfHealthID);
    }


    query = query.Where(j => j.HospitalDepartments.Any(u => u.Units.Any(d => d.Devices.Any(s => s.Status == Enums.DeviceStatus.Free)))
    && j.HospitalDepartments.Any(hd => hd.Units.Any(u => u.Beds.Any(b => b.Status == Enums.BedStatus.Free))));





    var list = query.ToList();

谢谢

推荐答案

替换

m => m.Devices.Any(w => selectedDeviceTypeIDs.Contains(w.DeviceTypeID))

使用

m => m.Devices.All(w => selectedDeviceTypeIDs.Contains(w.DeviceTypeID)) && selectedDeviceTypeIDs.All(w => m.Devices.Any(d => d.DeviceTypeID = w))

您也可以像这样重构它:

You can also refactor it like this:

创建方法

private bool areEquivalent(List<int> a, List<int> b) { return (a.Count == b.Count) && !a.Except(b).Any(); }

private bool areEquivalent(List<int> a, List<int> b) { return (a.Count == b.Count) && !a.Except(b).Any(); }

然后

m => { var deviceTypes = m.Devices.Select(d => d.DeviceType).ToList(); return areEquivalent(selectedDeviceTypeIDs, deviceTypes);}

这篇关于SQL Server where子句等于而不是IN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！