Potential Optimised alternative linq query for finding non Existent records within a set of Ids

Question

Let's say I have been given an array of Ids [integers] and I would like to know which one of them does not exist within a particular Db table [Table1 for instance]. Currently I use the following EF query [My version of EF is EF6 but I assume in this particular scenario using EF6 or EF Core would not matter]:

// Lets assume record with id = 9999 doesnt exist in the database 
IEnumerable<int> Ids = new [] { 1 ,2 ,3, 4, 9999}

var nonExistentIds = Ids.Except(myDbContext.Table1.Select(x => x.Id)
                                            .Intersect(Ids));

Above example will return [9999] which is the the record in the given array that doesn't exist in the database.

Could there be a potential better linq alternative [I am not looking for a native sql alternative] for discovering non existent records?

Answer 1

As long as Ids is reasonably small, I would use Contains:

var existingIds = myDbContext.Table1.Select(x => x.Id)
                                    .Where(id => Ids.Contains(id))
                                    .ToHashSet();

var nonExistentIds = ids.Where(id => !existingIds.Contains(id));

It should translate to something like

SELECT 
    [Extent1].[Id] AS [Id]
    FROM [dbo].[Table1] AS [Extent1]
    WHERE  [Extent1].[Id] IN (1, 2, 3, 4)

and then you reverse the result with LINQ to Objects.

You could also reverse with Except:

var nonExistentIds = ids.Except(myDbContext.Table1.Select(x => x.Id)
                                                  .Where(id => Ids.Contains(id)));

The benefit of Contains over Intersect is the way the list of ids is sent to SQL Server - Intersect constructs a table very verbosely, with UNION ALL, which more quickly overflows SQL.