CODEWITHSUNDEEP
entity-frameworklinq
Jamie Ide
Jamie Ide

Reputation: 49251

Entity Framework LINQ Query match all members of child collection

I have a Site table that has a many-to-many relationship with a UtilityServiceConnection table using a linking table called LinkSiteUtilityServiceConnection. Given a set of ServiceConnectionIds, I need to locate the Site that is exclusively linked to all of them and no more. I think I should be able to write the query using All on the collection but it's not working as expected.

var serviceConnectionIds = new[] { 546892, 546911, 546923 };

var sites1 = db.Sites
               .Where(x => x.LinkSiteUtilityServiceConnections.All(y => serviceConnectionIds.Contains(y.UtilityServiceConnectionId)))
               .ToList();
Assert.AreEqual(1, sites1.Count); //fails

This produces the query below that returns ~250,000 records when I expect to get one.

SELECT [Extent1].*
FROM   [dbo].[Site] AS [Extent1]
WHERE  NOT EXISTS (SELECT 1 AS [C1]
                   FROM   [dbo].[LinkSiteUtilityServiceConnection] AS [Extent2]
                   WHERE  ([Extent1].[SiteId] = [Extent2].[SiteId])
                          AND ((NOT ([Extent2].[UtilityServiceConnectionId] IN (546892, 546911, 546923)))
                                OR (CASE
                                      WHEN ([Extent2].[UtilityServiceConnectionId] IN (546892, 546911, 546923)) THEN cast(1 as bit)
                                      WHEN (NOT ([Extent2].[UtilityServiceConnectionId] IN (546892, 546911, 546923))) THEN cast(0 as bit)
                                    END IS NULL)))

Why isn't All working as I expect? What's the best way to write this query?

Upvotes: 1

Views: 340

Answers (1)

Mohammad Akbari
Mohammad Akbari

Reputation: 4766

check this code:

query 1:

var sites1 = db.Sites
    .Where(x => serviceConnectionIds.All(y =>             
                x.LinkSiteUtilityServiceConnections
                    .Select(u => u.UtilityServiceConnectionId).Contains(y)))
    .ToList();

query 2:

var query = db.Posts.AsQueryable();
var sites1 = serviceConnectionIds.Aggregate(query, 
    (current, item) => current.Where(e => e.LinkSiteUtilityServiceConnections
        .Any(c => c.UtilityServiceConnectionId== item))).ToList();

Upvotes: 1

Related Questions