SQL server where clause equals instead of IN

Question

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))
            )
        )

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))
        )

And here the Linq source code:

    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();

Thank you

the condition "equals exactly 10 AND 20" cannot be satisfied. show us the LINQ source and the intended result, so we get an idea what you need to achieve. — Cee McSharpface, Apr 14 '17 at 12:12
`AND ([Extent4].[DeviceTypeID] = 10,20) or ([Extent4].[DeviceTypeID] = 20)` — Ilyes, Apr 14 '17 at 12:13
AND ([Extent4].[DeviceTypeID] = 10 OR Extent4].[DeviceTypeID] =20)) — Ross Bush, Apr 14 '17 at 12:13
anyhow, add the c# linq source. there is little value showing the generated query. I would not expect anybody here willing to reverse-engineer a triple-nested exists to LINQ except when they were also active on puzzling.stackexchange.com — Cee McSharpface, Apr 14 '17 at 12:19
You probably mean to say that you want hospitals *only* having devices 10 and 20 (no more, no less), but yeah, as others have said, please elaborate. Show the LINQ statement, preferably the classes as well, and explain exactly what you're after. — Gert Arnold, Apr 14 '17 at 12:22
@Sami. Thank you for your help. See 10,20 it's an array so it can be 10,20,30,40 that means i can't write or ([Extent4].[DeviceTypeID] = 10) or ([Extent4].[DeviceTypeID] = 20) or ([Extent4].[DeviceTypeID] = 30) I think i need to loop through the whole array. kindly please show me a proper solution . Thank you — Alex, Apr 14 '17 at 12:26
@RossBush, Thank you for your help. See 10,20 it's an array so it can be 10,20,30,40 that means i can't write or ([Extent4].[DeviceTypeID] = 10) or ([Extent4].[DeviceTypeID] = 20) or ([Extent4].[DeviceTypeID] = 30) I think i need to loop through the whole array. kindly please show me a proper solution . Thank you — Alex, Apr 14 '17 at 12:26
Explain more clearly your desired outcome and exactly what "it" is. That `IN` clause would not act like `...LIKE %10% OR ... LIKE %20%` as your question implies. — Crowcoder, Apr 14 '17 at 12:27
do you mean a range? as in `WHERE DeviceTypeID BETWEEN 10 AND 20` and in `WHERE DeviceTypeID >= 10 AND DeviceTypeID <=20`? — Cee McSharpface, Apr 14 '17 at 12:28
I suspect that what you're struggling to explain is that you're looking for a `relational division` - you want to locate "devices" (not sure I'm picking out the right element) where there's one row with `DeviceTypeID` of 10 and *another* row with the value 20. If so, as others have said, we can help but we need to see the *original* code, not the generated stuff. Please [edit] your question. — Damien_The_Unbeliever, Apr 14 '17 at 12:33
@dlatikay, No ([Extent4].[DeviceTypeID] IN (10,20)) actually (10,20) is just an array that could be (10,20,30) or (10,20,30) or (10,20,30,....,....) — Alex, Apr 14 '17 at 12:35
so the definition would be: given a ´selectedDeviceTypeIDs` containing 10 and 20; when there is a project with devices 10 and 20 _only_, consider it a match. when there is a project with devices 10, 20, and 30, do not consider it a match? — Cee McSharpface, Apr 14 '17 at 13:04
@dlatikay 10, 20,30 that's just example..... selectedDeviceTypeIDs is an array — Alex, Apr 14 '17 at 13:20
I know. but is the definition correct? if we cannot clarify this here, I'd ask you to add sample input and desired output to the question. — Cee McSharpface, Apr 14 '17 at 13:24

NP3 · Answer 1 · 2017-05-04T10:39:34.807

0

Replace

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

with

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:

create a method

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

and then

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

edited May 04 '17 at 10:39

answered May 04 '17 at 09:55

NP3

1,114
1
8
15

I've just realized you asked the same question twice already http://stackoverflow.com/questions/43323185/linq-equal-instead-of-contains and http://stackoverflow.com/questions/43430023/how-to-use-the-exact-array-value-instead-of-contains, and people replied and you never accepted any answers and just posted the same thing again.This is not cool. – NP3 May 04 '17 at 13:57

SQL server where clause equals instead of IN

1 Answers1

Linked