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
}
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>(