Get output 'inserted' on update with Entity Framework

Question

SQL Server provides output for inserted and updated record with the 'inserted' keyword.

I have a table representing a processing queue. I use the following query to lock a record and get the ID of the locked record:

UPDATE TOP (1) GlobalTrans
SET LockDateTime = GETUTCDATE()
OUTPUT inserted.ID
WHERE LockDateTime IS NULL

This will output a column named ID with all the updated record IDs (a single ID in my case). How can I translate this into EF in C# to execute the update and get the ID back?

why not use trigger ? Do you connect to mssql database through that unity? — Whencesoever, Apr 16 '18 at 13:07
See https://stackoverflow.com/questions/49859510/return-output-from-stored-procedure — paparazzo, Apr 16 '18 at 14:31

score 2 · Answer 1 · answered Apr 16 '18 at 13:17

Entity Framework has no way of doing that.

You could do it the ORM way, by selecting all the records, setting their LockDateTime and writing them back. That probably is not safe for what you want to do because by default it's not one single transaction.

You can span your own transactions and use RepeatableRead as isolation level. That should work. Depending on what your database does in the background, it might be overkill though.

You could write the SQL by hand. That defeats the purpose of entity framework, but it should be just as safe as it was before as far as the locking mechanism is concerned.

You could also put it into a stored procedure and call that. It's a little bit better than the above version because at least somebody will compile it and check that the table and column names are correct.

Personally, I would go with the stored procedure method. I always encourage non-coupling of the application and data layers (to whatever extent is possible.) A sproc allows you to apply arbitrary and custom logic to your inserts, and can manage them in a batch as well. — Travis Manning, Apr 18 '18 at 02:18

Craig · Answer 2 · 2020-07-30T14:31:12.157

Simple Example #1 to get a data table:

I did this directly against the connection: Changed the command.ExecuteNonQuery() to command.ExecuteReader()

        var connection = DbContext().Database.Connection as SqlConnection;

        using (var command = connection.CreateCommand())
        {
            command.CommandText = sql;
            command.CommandTimeout = 120;
            command.Parameters.Add(param);

            using (var reader = command.ExecuteReader())
            {
                var resultTable = new DataTable();
                resultTable.Load(reader);
                return resultTable;
            }
        }

FYI, If you don't have an OUTPUT clause in your SQL, it will return an empty data table.

Example #2 to return entities:

This is a bit more complicated but does work. using a SQL statement with a OUTPUT inserted.*

         var className = typeof(T).Name;
         var container = ObjContext().MetadataWorkspace.GetEntityContainer(UnitOfWork.ObjContext().DefaultContainerName, DataSpace.CSpace);
         var setName = (from meta in container.BaseEntitySets where meta.ElementType.Name == className select meta.Name).First();

         var results = ObjContext().ExecuteStoreQuery<T>(sql, setName, trackingEnabled ? MergeOption.AppendOnly : MergeOption.NoTracking).ToList();

T being the entity being worked on

Get output 'inserted' on update with Entity Framework

2 Answers2