Get items from input list that do not exist in the database using EF Core 2.1

user_10339036 0
Viewed 753

I want to get items from input list that do not exist in the database table.

I pass a list of IDs, then I want to return those IDs that do not exist in my table.

This is what I have for now:

var input = new List<string>() // list of Ids, for example count of 10

var itemsThatExistInDb = await DbContext.Set<Data>() // in table exist 100k+ records,
        .AsQueryable()                               // can't use simple !Contains()
        .Where(x => input.Contains(x.Id))
        .Select(x => x.Id)
        .ToListAsync();

var itemsThatNotExistInDb = input.Except(itemsThatExistInDb).ToList();

How to write a query in EF Core 2.1 to get items from my input list that do not exist in my database without using linq extensions like Except()? If it's possible, I want to get those Ids straight from my database query to DbContext

entity-framework-core linq c#
2 Answers

I think you can query it like this:

var input = new List<string>() // list of Ids

       List<string> itemsThatExist = new List<string>();

       var itemsThatNotExist  = await DbContext.Set<Data>()
                .AsQueryable()
                .ToDictionaryAsync(_ => _.ID, _ => _);

        foreach (var item in input)
        {
            if(AllItems.ContainsKey(item)){
                itemsThatExist.Add(item);
                itemsThatNotExist.Remove(item);
            }
        }
user_113685390

If you don't want to use !contain you can use logical method as below:

datatTable is the content of your table.

List<string> fullList = new List<string>() { "3", "1", "2" }; //or any dynamic list content or class list based on requirement.
List<string> itemsThatNotExistInDb = new List<string>(); //New container list same as fullList
            foreach (var item in fullList)
            {
                var isRemoved = dataTable.RemoveAll(a => a.Id == item) == 1 ? true : false;
                if (isRemoved)
                {
                    itemsThatNotExistInDb.Add(item);
                }
            }
user_17403990
Related