Cannot execute Raw Sql in .Net COre

Question

I am using Core 2.0 using entity framework. I have successfully generated context using scaffold DBContext. I have DBSet for table EMployee. I need to execute SToredProcedure which will give list of employee. I cannot see .FromSql nor.ExecuteCommand option.

I have added EntityFrameworkCore.SqlServer(2.0.1),EntityFrameworkCore.SqlServer.Design(1.1.5),Microsoft.VisualStudio.Web.CodeGeneration.Design(2.0.2) and EntityFrameworkCore.Tools(2.0.1) but to no awail.

Please guide for mentioned concerns.

What have you tried so far? What have you searched for, too? I searched "ef core call stored procedure" in Google and got many, many results (most of which are questions here with accepted answers) — Jamie Taylor, Feb 09 '18 at 13:26
Possible duplicate of [How to run stored procedures in Entity Framework Core?](https://stackoverflow.com/questions/28599404/how-to-run-stored-procedures-in-entity-framework-core) — nvoigt, Feb 09 '18 at 13:53

score 2 · Accepted Answer · answered Feb 09 '18 at 17:30

If you want to execute row SQL using EF Core, try the following.

var employees = context.Employees
.FromSql("SELECT * FROM dbo.Employees")
// If you want to execute a stored procedure, then below
// .FromSql("EXECUTE {SP_NAME}")
.ToList();

But note, there are certain limitations present as described here: https://learn.microsoft.com/en-us/ef/core/querying/raw-sql#limitations

score 1 · Answer 2 · answered Feb 09 '18 at 14:59

This is the only way to execute Raw SQL in .NET at the moment:

var conn = _context.Database.GetDbConnection();
try
{
    await conn.OpenAsync();
    using (var command = conn.CreateCommand())
    {
        command.CommandText = "SELECT * From Table1 WHERE sender = @sender";
        DbParameter sender = command.CreateParameter();
        sender.ParameterName = "sender";
        sender.Value = "Value";
        command.Parameters.Add(sender);
        DbDataReader reader = await command.ExecuteReaderAsync();

        if (reader.HasRows)
        {
            while (await reader.ReadAsync())
            {
                int SubscriptionID = reader.GetInt32(0);
            }
        }
        reader.Dispose();
    }
}
finally { conn.Close(); }

You can use it for stored procedures as well.

Cannot execute Raw Sql in .Net COre

2 Answers2