Efficient way to delete/update entities in EF Core 7 by a list of conditions

Question

I need to delete/update a given number of rows of a table.

In one I solved it like this:

  await context.MyItems
    .Where(x => itemsIds.Contains(x.Id))
    .ExecuteUpdateAsync(f => f.SetProperty(x => x.State, state));

which allows me to update the items ina given list of ids (1,2,5,7, ...)

But I am not sure how to do the same in a table where I need to filter not by a list of ids, but by a list of multiple properties:

var itemsToUpdate = new List<Identifier>(){ ....}

And identifier is

public class Identifier
{
    DateTime date {get;set;}
    string code {get;set;}
    string owner {get;set;}
    
}

Any idea of how to do this efficienly in EF Core? Cause I am thinking that I got not other solution but a Stored Procedure :-(

To make it clearer, original function:

public async Task UpdateItemsAsync(IList<long> Ids, ItemStatus state)
{
    await context.myItems
        .Where(x => Ids.Contains(x.Id))
         .ExecuteUpdateAsync(f => f.SetProperty(x => x.State, state));
}

New function:

public async Task UpdateItemsAsync(IList<Identifier> Ids, ItemStatus state)
{
   //can´t figure out how to apply filter here
}

Answer 1

You can use a Table Valued Parameter for this.

First create a table type

CREATE TYPE dbo.Identifier AS TABLE (
    date datetime(7),
    code nvarchar(100),
    owner nvarchar(100)
)

On EF Core 7 and earlier, because it's a multi-column type, you can't use context.Database.SqlQuery on this, you need to add an entity to your model.

modelBuilder.Entity<Identifier>().HasNoKey().ToView(null);

Then you can use it like this:

var table = new DataTable { Columns = {
    { "date", typeof(DateTime) },
    { "code", typeof(string) },
    { "owner", typeof(string) },
};
foreach (var item in items)
{
    table.Add(item.date, item.code, item.owner);
}

// must create this part in a separate step
var itemsTvp = context.Set<Identifier>().FromSqlRaw(
    "SELECT * FROM @tmp",
    new SqlParameter("@tmp", table) { TypeName = "dbo.Identifier", Value = table }
);
//

await context.MyItems
    .Where(x => itemsTvp.Any(t => t.date = x.date && t.code == x.code && t.owner == x.owner)
    .ExecuteUpdateAsync(f => f.SetProperty(x => x.State, state));

In EF Core 8 you don't need an entity, you can just use

var itemsTvp = context.Database.SqlQueryRaw<Identifier>(

Answer 2

You could use BulkExtensions for that: https://github.com/borisdj/EFCore.BulkExtensions

For example:

 await context.MyItems
.Where(x => itemsIds.Contains(x.Id))
.BatchUpdateAsync(f => new Item { State = state });

In EF7 you have ExecuteUpdate function https://learn.microsoft.com/de-de/ef/core/saving/execute-insert-update-delete :

  await context.MyItems
.Where(x => itemsIds.Contains(x.Id))
.ExecuteUpdateAsync(setters => setters.SetProperty(b =>b.State, state));

Answer 3

Disclaimer: I'm the owner of Entity Framework Extensions

This library is not free, but the WhereBulkContains method does exactly what you want:

await context.MyItems
    .WhereBulkContains(itemsIds, new { x.Id1 , x.Id2 })
    .UpdateFromQuery(f => new Item { State = state });

It support multiple kind of list for you itemsIds such as

• List
• Entity Type
• Anonymous Type
• Expando Object

And you can use more than one key to make the JOIN See custom key