entity framework: delete WHERE in c# syntax missing (sqlite)

Question

I am using entity framework to alter data on my remote tables.

One working call I do is this:

public async Task<List<PictureItems>?> GetUserThumbnailByuserId(string userid)
{
    await InitializeAsync();
    try
    {
        return await _table.GetAsyncItems().Where(x => x.BelongsToUserId == userid).ToListAsync();
    }
    catch (Exception e)
    {
        return null;
    }
}

Now, I am trying to delete an item where Ids match.

But all I can do to delete the correct item is:

await _table.DeleteItemAsync(item);

But this would only work if I inserted an identical item into the (). So I could get the right item, if the item is not null then insert into the delete and the item is deleted.

But I dont wanna get the item all the times when I have the ID already locally available, this would just create unneccissary traffic.

So what I would need is something like:

_table.DeleteItemAsync().Where(x => x.BelongsToUserId == userid).ToListAsync();

Anyone got any help for me how to approach this?

Best,

J

score 2 · Answer 1 · answered Jan 09 '23 at 13:53

If you have knowledge about the primary ID of the items to be deleted beforehand, you can simply instantiate an object only containing that ID and pass it to EF Core:

table.Remove(new Entity() { Id = <yourId> });

This removes the need to query data first - EF Core will delete the corresponding existing item with the respective ID property if it is already available on the DB.

Of course, whether or not this works in your case depends on whether BelongsToUserId is a key EF can work with, or whether it is only a regular property.

Sergey Kudriavtsev · Accepted Answer · 2023-01-09T14:10:09.607

You have several options:

As already mentioned in other answer, if you know the primary keys of the entities that you want to delete, you can construct empty entities with IDs without querying and call DbContext.RemoveRange(entities);
If you're up for trying or using EF Core v.7.0, then you can use The new ExecuteDelete method
If you know what you're doing and are confident in the quality of your inputs: use ExecuteSqlRaw and just remove the objects you want manually this way: DbContext.Database.ExecuteSqlRaw("DELETE FROM PictureItems WHERE ...");
Use a (commercial) extension library that supports bulk-delete on EF Core. I generally avoid recommending specific tools, but "ef core bulk delete" search in Google gives some relevant results. Search for an open-source analogue lead me to this small library that should fit your needs - taken from this answer.

entity framework: delete WHERE in c# syntax missing (sqlite)

2 Answers2