Let's say I have a list of integers.

int[] keys = { 14, 27, 32, 34 };

Is there a way to construct an Entity Framework query such that I find which, if any, of these integers does not equal the Id column in any row of my Users table? And I'd like to do this without actually loading any of the Users records.

So, for example, if my Users table contains the following data:

I would want the results to be { 27 }, because only one key value was not in the list of user IDs.

Is this possible without loading any rows from the Users table?

CodePudding user response：

You will possibly need to query some data from the DB, not entire User records, but their IDs. Provided the list of IDs and # of users being checked against is relatively manageable, something like this may be an option:

var minKey = keys.Min();
var maxKey = keys.Max();
var existingIds = _context.Users
    .Where(x => x.Id >= minKey && x.Id <= maxKey)
    .Select(x => x.Id)
    .ToList();

var IdsNotInDb = keys.Except(existingIds);

This would fetch only the IDs within the range of Keys from the DB, then you can use that to compare against the list of Keys to find items in your list that don't have corresponding records. Since this is only loading the IDs, this would be just hitting the Index rather than the User table itself.

If you are instead expecting to compare very large numbers of keys against very large numbers of users, then you would probably want to take a Temporary Table approach inserting the Keys values into an indexed temporary table, then using EF to query using a join between the user table and this new temp table. An approach to consider for something like that can be found here: (https://www.thinktecture.com/en/entity-framework-core/temp-tables-in-3-1/)

CodePudding user response：

I don't know how it can be done in a single line, but here's what i suggest you do :

1. You get an array the Ids of the users that do exist in the database, then you compare it to your first array 'keys'
2. Then you find the Ids that are not in the database.

But for some reason, using Linq expressions doesn't work for me, so here's how i did it :

//First your prepare a query that will return users which had their Id in 
the keys array
var query = from user in Database.Users
            where keys.Contains(user.id)
            select user.id;

//Then you execute the query
var users = query.ToArray();

Then, you execute a query on the original list of keys, the same way you do it on your database (using Linq expressions), to find the items that do not exit in your returned array (your database) :

var notInUsers = keys.Where(k => !users.Contains(k)).ToArray();

Now, you got a new array called notInUsers which contains the Ids that were present in your 'keys' array, but which are not in your database.
Here you go, you have an optimised solution [it doesn't store your entire table (that may contain millions of records) in-memory], an you can do whatever you want with that array.
Note, if you want to work with lists instead of arrays, replace 'ToArray()' with 'ToList()'.

CodePudding user response：

Yes, you can construct a query expression for the above case without loading the users.

var excludedIds = userContext
    .Where(user => !keys.Contains(user.Id))
    .Select(user => user.Id);

Nothing has executed at this point, and no user records have been loaded.