Cannot execute Raw Sql in .Net COre

Question

Cannot execute Raw Sql in .Net COre

.net-core entity-framework-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.

sachin kulkarni

Popular Answer

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.

pitaridis

View more on Stack Overflow

Jaliya Udagedara · Accepted Answer · 2/9/2018 5:30:01 PM

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://docs.microsoft.com/en-us/ef/core/querying/raw-sql#limitations

Cannot execute Raw Sql in .Net COre

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related