LINQ: how to get an intersection of two sets of ints?

Question

There must be a way to compare two sets of results while staying in LINQ. Here's my existing code that uses a HashSet to do the comparison after two separate queries:

    public static void AssertDealershipsShareTransactionGatewayCredentialIds(long DealershipLocationId1,
        long DealershipLocationId2)
    {
        using (var sqlDatabase = new SqlDatabaseConnection())
        {
            var DealershipCredentials1 =
                sqlDatabase.Tables.DealershipLocationTransactionGateway
                    .Where(x => x.DealershipLocationId == DealershipLocationId1)
                    .Select(x => x.TransactionGatewayCredentialId);
            var DealershipCredentials2 =
                sqlDatabase.Tables.DealershipLocationTransactionGateway
                    .Where(x => x.DealershipLocationId == DealershipLocationId2)
                    .Select(x => x.TransactionGatewayCredentialId);
            var doSetsOfCredentialsMatch = new HashSet<int>(DealershipCredentials1).SetEquals(DealershipCredentials2);
            Assert.IsTrue(doSetsOfCredentialsMatch,
                "The sets of TransactionGatewayCredentialIds belonging to each Dealership did not match");
        }
    }

Ideas? Thanks.

Possible duplicate of [Intersect LINQ query](http://stackoverflow.com/questions/2381049/intersect-linq-query) — Robert McKee, Oct 28 '15 at 18:34
What's wrong with your way? Looks perfect to me. Otherwise, see http://stackoverflow.com/questions/33245613/whats-the-best-way-to-determine-whether-two-listt-objects-contain-the-same-se and http://stackoverflow.com/questions/3669970/compare-two-listt-objects-for-equality-ignoring-order — Ivan Stoev, Oct 28 '15 at 18:46
It makes two database calls that returns multiple records instead of just a boolean. — Robert McKee, Oct 28 '15 at 19:05

Robert McKee · Answer 1 · 2015-10-28T19:07:01.983

Easy answer (This will make 1, possibly 2 database calls, both of which only return a boolean):

if (list1.Except(list2).Any() || list2.Except(list1).Any()) 
{
   ... They did not match ...
}

Better answer (This will make 1 database call returning a boolean):

var DealershipCredentials1 =
  sqlDatabase.Tables.DealershipLocationTransactionGateway
    .Where(x => x.DealershipLocationId == DealershipLocationId1)
    .Select(x => x.TransactionGatewayCredentialId);
var DealershipCredentials2 =
  sqlDatabase.Tables.DealershipLocationTransactionGateway
    .Where(x => x.DealershipLocationId == DealershipLocationId2)
    .Select(x => x.TransactionGatewayCredentialId);
if (DealershipCredentials1.GroupJoin(DealershipCredential2,a=>a,b=>b,(a,b)=>!b.Any())
    .Union(
      DealershipCredentials2.GroupJoin(DealershipCredential1,a=>a,b=>b,(a,b)=>!b.Any())
    ).Any(a=>a))
{
... They did not match ...
}

The second method works by unioning a left outer join that returns a boolean indicating if any unmatching records were found with a right outer join that does the same. I haven't tested it, but in theory, it should return a simple boolean from the database.

Another approach, which is essentially the same as the first, but wrapped in a single LINQ, so it will always only make 1 database call:

if (list1.Except(list2).Union(list2.Except(list1)).Any())
{
}

And another approach:

var common=list1.Intersect(list2);
if (list1.Except(common).Union(list2.Except(common)).Any()) {}

Wouldn't be easier `Intersect`? In any case both approaches (or `Contains`) are not applicable to any class. — varocarbas, Oct 28 '15 at 18:40
Intersect would only return the records that are in both sets, not tell you if any records were in one, but not both sets. — Robert McKee, Oct 28 '15 at 18:58

LINQ: how to get an intersection of two sets of ints?

1 Answers1