LINQ: How Can I Use Equals in query

Question

I need to use Equals method or something similar instead of using Contains method because i want to search in database for the exact values in selectedDeviceTypeIDs array not any of it.

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

Here is my full code:

 public HttpResponseMessage GetAvailableHospitalsByAjax(System.Guid? DirectorateOfHealthID = null, System.Guid? UnitTypeID = null, string DeviceTypeIDs = null)
    {

        Context db = new Context();
        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();
        return Request.CreateResponse(HttpStatusCode.OK, list);
    }

What SQL does your current Linq generate at runtime? Are you sure it isn't already using `IN`? — Dai, Apr 14 '17 at 18:06
Why do you ask this [twice](http://stackoverflow.com/q/43411268/861716)? If a question doesn't get the desired response you should improve it, not re-post it. — Gert Arnold, Apr 15 '17 at 21:55

score 0 · Answer 1 · answered Apr 14 '17 at 18:08

0

EF6 supports the SQL IN operator if the foo in foo.Contains( ) is an IList<T>, for example, this Linq:

Int32[] desired = new Int32[] { 1, 2, 3, 4 };

IQueryable<Item> itemsQuery = db.Items.Where( item => desired.Contains( item.SomeValue ) );

...will be converted into this:

SELECT Item.* FROM Items WHERE SomeValue IN ( 1, 2, 3, 4 )

I'm not entirely certain on the specifics, but I think if you convert your selectedDeviceTypeIDs from IQueryable<Guid> to Guid[] (or at least IList<Guid>) then EF will also generate an IN query for you.

answered Apr 14 '17 at 18:08

Dai

141,631
28
261
374

Thank you for your help :) Yes the generated t-sql is IN ... the problem with IN is when you SELECT Item.* FROM Items WHERE SomeValue IN ( 1, 2, 3, 4 ) if it finds ( 1 or 2 or 3 or 4 ) it will return result ... i don't know that.. i want SELECT Item.* FROM Items WHERE SomeValue equals ( 1, 2, 3, 4 ) got t? – Tom Apr 14 '17 at 19:34
@Tom Oh - you want the *entire sequence* of child items to match? – Dai Apr 14 '17 at 20:11
Yes Dai, Exactly – Tom Apr 14 '17 at 22:40

LINQ: How Can I Use Equals in query

1 Answers1