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.
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}")
.ToList();
But note, there are certain limitations present as described here: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql#limitations
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.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow