Sql Server Where Clause Equals Instead Of In

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

Solution 1:

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