Cannot execute Raw Sql in .Net COre

.net-core entity-framework-core


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.

2/9/2018 1:19:56 PM

Accepted Answer

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}")

But note, there are certain limitations present as described here:

2/9/2018 5:30:01 PM

Popular Answer

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

var conn = _context.Database.GetDbConnection();
    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";
        DbDataReader reader = await command.ExecuteReaderAsync();

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

You can use it for stored procedures as well.

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow