.Net Core Entity Framework FromSqlRaw perform insert and return Identity

May 26, 2023

I am doing an Asp.Net Core Application and I am using Entity Framework to performs Select, Insert and Update to the database.

I am using

_dbContext.Database.ExecuteSqlRaw(" insert into TEST3( Field) Values ('ok')");

and works fine…

But I need to retrieve the @@Identity generated. So I run

query = "insert into TEST3( Field) Values ('ok'); Select @@IDENTITY as Id;"
T aa = await _dbContext.Set<T>().FromSqlRaw(query).FirstOrDefaultAsync();

T is a Model to get the Id

 public class InsertModel
    {
        [Key]
        public Int32 Id{ get; set; }
    }

I have this error

System.InvalidOperationException: ''FromSql' or 'SqlQuery' was called with non-composable SQL and with a query composing over it. Consider calling 'AsEnumerable' after the method to perform the composition on the client side.'

How can I perform the insert and return the ID?

Thanks

>Solution :

You can do this with ExecuteSqlRaw and an output parameter.

var pId = new SqlParameter("@rv", SqlDbType.Int);
pId.Direction = ParameterDirection.Output;
db.Database.ExecuteSqlRaw(" insert into TEST3( Field) Values ('ok'); set @id = scope_identity();", pId);
var id = (int)pId.Value;