Update multiple records using EF Core

Question

I want to update some records in a SQL table based non their primary ID. It is a simple query:

context.Database.ExecuteSqlRawAsync("UPDATE [FileInfos] SET [DeleteTimeUtc] = {0} WHERE FileId IN ({1});", parameters: parameters);

The first parameter is a date time parameter, that I create like this:

object[] parameters = {
    new SqlParameter("@now", SqlDbType.DateTime)
    {
        Value = DateTime.UtcNow
    }
};

I do not know how to create the second parameter ( I assume I can pass them as string in the SQL query, but maybe there is a better solution ).

https://stackoverflow.com/questions/39167055/how-to-execute-raw-sql-with-in-statement-using-entity-framework-6 something similar. If parameter is anything except for string, I'd go with simple string join + string format. if they are strings, then would be better to use dynamic SqlParameters as in the answer in the link above — Yehor Androsov, Dec 02 '20 at 14:02
Does this answer your question? [Entity Framework Core- passing parameters to Where IN Clause Raw Query](https://stackoverflow.com/questions/58950476/entity-framework-core-passing-parameters-to-where-in-clause-raw-query) — Christian Gollhardt, Dec 02 '20 at 14:25

score 0 · Answer 1 · answered Dec 03 '20 at 15:02

Try this:

    var utcNow = new SqlParameter("@now", DateTime.UtcNow);
    var idIn = new SqlParameter("@idIn", "1,2,3");
context.Database.ExecuteSqlRawAsync("UPDATE [FileInfos] SET [DeleteTimeUtc] = @now WHERE FileId IN @idIn"),  parameters: new[] { utcNow, idIn });

score 0 · Accepted Answer · answered Dec 07 '20 at 13:14

I have created a table type parameter:

CREATE TYPE [dbo].[FileIdentifierType] AS TABLE (FileId uniqueidentifier)

When I call the update sql command I pass the list of files in a structured parameter:

        var table = new DataTable();
        table.Columns.Add("FileId", typeof(Guid));
        foreach (var fileId in fileIds)
        {
            table.Rows.Add(fileId);
        }

        var fileIdsParameter = new SqlParameter("@FileIds", SqlDbType.Structured)
        {
            Value = table,
            TypeName = "[dbo].[FileIdentifierType]"
        };

Update multiple records using EF Core

2 Answers2