Я использую EF7 (ядро сущности) в основном приложении asp.net. Не могли бы вы указать мне правильный способ выполнения хранимых процедур? Старый метод с ObjectParameters и ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction не работает.
Как запустить хранимые процедуры в Entity Framework Core?
Ответ 1
Поддержка хранимой процедуры в EF7 теперь разрешена, это также поддерживает отображение нескольких наборов результатов.
И вы можете назвать это следующим образом: var userType = dbContext.Set().FromSql("dbo.SomeSproc @Id = {0}, @Name = {1}", 45, "Ada");
Ответ 2
Поддержка хранимых процедур еще не реализована (в версии 7.0.0-бета3) в EF7. Вы можете отслеживать прогресс этой функции, используя проблему № 245.
На данный момент вы можете сделать это по старинке, используя ADO.NET.
var connection = (SqlConnection)context.Database.AsSqlServer().Connection.DbConnection;
var command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "MySproc";
command.Parameters.AddWithValue("@MyParameter", 42);
command.ExecuteNonQuery();
Ответ 3
Чтобы выполнить хранимые процедуры, используйте FromSql метод, который выполняет запросы RAW SQL
например.
var products= context.Products
.FromSql("EXECUTE dbo.GetProducts")
.ToList();
Для использования с параметрами
var productCategory= "Electronics";
var product = context.Products
.FromSql("EXECUTE dbo.GetProductByCategory {0}", productCategory)
.ToList();
или
var productCategory= new SqlParameter("productCategory", "Electronics");
var product = context.Product
.FromSql("EXECUTE dbo.GetProductByName @productCategory", productCategory)
.ToList();
Существуют определенные ограничения для выполнения запросов RAW SQL или хранимых процедур. Вы не можете использовать его для INSERT/UPDATE/DELETE. если вы хотите выполнить запросы INSERT, UPDATE, DELETE, используйте ExecuteSqlCommand
var categoryName = "Electronics";
dataContext.Database
.ExecuteSqlCommand("dbo.InsertCategory @p0", categoryName);
Ответ 4
"(SqlConnection)context"
- Этот тип-литье больше не работает. Вы можете сделать: "SqlConnection context;
".AsSqlServer()"
- Не существует.
"command.ExecuteNonQuery();"
- Не возвращает результаты. reader=command.ExecuteReader() работает.
С dt.load(reader)... тогда вам нужно переключить фреймворк из 5.0 и вернуться к 4.51, так как 5.0 не поддерживает типы данных/наборов данных. Примечание: это VS2015 RC.
Ответ 5
Поддержка хранимых процедур в EF Core аналогична более ранним версиям EF Code.
Вам нужно создать свой класс DbContext, унаследовав класс DbContext от EF. Хранимые процедуры выполняются с использованием DbContext.
Первый шаг - написать метод, который создает DbCommand из DbContext.
public static DbCommand LoadStoredProc(
this DbContext context, string storedProcName)
{
var cmd = context.Database.GetDbConnection().CreateCommand();
cmd.CommandText = storedProcName;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
return cmd;
}
Для передачи параметров в хранимую процедуру используйте следующий метод.
public static DbCommand WithSqlParam(
this DbCommand cmd, string paramName, object paramValue)
{
if (string.IsNullOrEmpty(cmd.CommandText))
throw new InvalidOperationException(
"Call LoadStoredProc before using this method");
var param = cmd.CreateParameter();
param.ParameterName = paramName;
param.Value = paramValue;
cmd.Parameters.Add(param);
return cmd;
}
Наконец, для отображения результата в список пользовательских объектов используйте метод MapToList.
private static List<T> MapToList<T>(this DbDataReader dr)
{
var objList = new List<T>();
var props = typeof(T).GetRuntimeProperties();
var colMapping = dr.GetColumnSchema()
.Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
.ToDictionary(key => key.ColumnName.ToLower());
if (dr.HasRows)
{
while (dr.Read())
{
T obj = Activator.CreateInstance<T>();
foreach (var prop in props)
{
var val =
dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
prop.SetValue(obj, val == DBNull.Value ? null : val);
}
objList.Add(obj);
}
}
return objList;
}
Теперь все было готово для выполнения хранимой процедуры с помощью метода ExecuteStoredProc и отображает его в список List, тип которого передается как T.
public static async Task<List<T>> ExecuteStoredProc<T>(this DbCommand command)
{
using (command)
{
if (command.Connection.State == System.Data.ConnectionState.Closed)
command.Connection.Open();
try
{
using (var reader = await command.ExecuteReaderAsync())
{
return reader.MapToList<T>();
}
}
catch(Exception e)
{
throw (e);
}
finally
{
command.Connection.Close();
}
}
}
Например, чтобы выполнить хранимую процедуру с именем "StoredProcedureName" с двумя параметрами, названными "firstparamname" и "secondparamname", это реализация.
List<MyType> myTypeList = new List<MyType>();
using(var context = new MyDbContext())
{
myTypeList = context.LoadStoredProc("StoredProcedureName")
.WithSqlParam("firstparamname", firstParamValue)
.WithSqlParam("secondparamname", secondParamValue).
.ExecureStoredProc<MyType>();
}
Ответ 6
В настоящее время EF 7 или EF Core не поддерживает старый метод импорта хранимых процедур в дизайнере и вызывает их напрямую. Вы можете взглянуть на дорожную карту, чтобы увидеть, что будет поддерживаться в будущем: Основная карта EF.
Итак, теперь лучше использовать SqlConnection для вызова хранимых процедур или любого необработанного запроса, так как для этого задания вам не нужен весь EF. Вот два примера:
Вызовите хранимую процедуру, которая возвращает одно значение. Строка в этом случае.
CREATE PROCEDURE [dbo].[Test]
@UserName nvarchar(50)
AS
BEGIN
SELECT 'Name is: '[email protected];
END
Вызовите хранимую процедуру, которая возвращает список.
CREATE PROCEDURE [dbo].[TestList]
AS
BEGIN
SELECT [UserName], [Id] FROM [dbo].[AspNetUsers]
END
Чтобы вызвать эту хранимую процедуру, лучше создать статический класс, который содержит все эти функции, например, я назвал его классом DataAccess следующим образом:
public static class DataAccess
{
private static string connectionString = ""; //Your connection string
public static string Test(String userName)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("dbo.Test", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// 3. add parameter to command, which will be passed to the stored procedure
cmd.Parameters.Add(new SqlParameter("@UserName", userName));
// execute the command
using (var rdr = cmd.ExecuteReader())
{
if (rdr.Read())
{
return rdr[0].ToString();
}
else
{
return null;
}
}
}
}
public static IList<Users> TestList()
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
// 1. create a command object identifying the stored procedure
SqlCommand cmd = new SqlCommand("dbo.TestList", conn);
// 2. set the command object so it knows to execute a stored procedure
cmd.CommandType = CommandType.StoredProcedure;
// execute the command
using (var rdr = cmd.ExecuteReader())
{
IList<Users> result = new List<Users>();
//3. Loop through rows
while (rdr.Read())
{
//Get each column
result.Add(new Users() { UserName = (string)rdr.GetString(0), Id = rdr.GetString(1) });
}
return result;
}
}
}
}
И класс Пользователи выглядит следующим образом:
public class Users
{
public string UserName { set; get; }
public string Id { set; get; }
}
Кстати, вам не нужно беспокоиться о производительности открытия и закрытия соединения для каждого запроса на sql, поскольку asp.net берет на себя управление ими для вас. И я надеюсь, что это было полезно.
Ответ 7
У меня были большие проблемы с параметрами ExecuteSqlCommand и ExecuteSqlCommandAsync, IN были простыми, но параметры OUT были очень сложными.
Мне пришлось вернуться к использованию DbCommand, например:
DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();
cmd.CommandText = "dbo.sp_DoSomething";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar) { Value = "Steve" });
cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar) { Value = "Smith" });
cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });
Я написал больше об этом в этот пост.
Ответ 8
Использование коннектора MySQL и Entity Framework Core 2.0
Моя проблема заключалась в том, что я получал исключение, как FX. Ex.Message = "Обязательный столбец 'body' не присутствовал в результатах операции 'FromSql'." Таким образом, чтобы извлечь строки с помощью хранимой процедуры таким образом, вы должны вернуть все столбцы для того типа сущности, с которым связан DBSet, даже если вам не нужен доступ ко всем этим для текущего запроса.
var result = _context.DBSetName.FromSql($"call storedProcedureName()").ToList();
ИЛИ с параметрами
var result = _context.DBSetName.FromSql($"call storedProcedureName({optionalParam1})").ToList();
Ответ 9
Я нашел это расширение очень полезным: StoredProcedureEFCore
Тогда использование так
List<Model> rows = null;
ctx.LoadStoredProc("dbo.ListAll")
.AddParam("limit", 300L)
.AddParam("limitOut", out IOutParam<long> limitOut)
.Exec(r => rows = r.ToList<Model>());
long limitOutValue = limitOut.Value;
ctx.LoadStoredProc("dbo.ReturnBoolean")
.AddParam("boolean_to_return", true)
.ReturnValue(out IOutParam<bool> retParam)
.ExecNonQuery();
bool b = retParam.Value;
ctx.LoadStoredProc("dbo.ListAll")
.AddParam("limit", 1L)
.ExecScalar(out long l);
Ответ 10
Я попробовал все другие решения, но у меня не получилось. Но я пришел к правильному решению, и это может быть полезно для кого-то здесь.
Чтобы вызвать хранимую процедуру и получить результат в списке моделей в EF Core, мы должны выполнить 3 шага.
Шаг 1.
Вам нужно добавить новый класс так же, как ваш класс сущности. Который должен иметь свойства со всеми столбцами в вашем SP. Например, если ваш SP возвращает два столбца с именами Id и Name, тогда ваш новый класс должен быть чем-то вроде
public class MySPModel
{
public int Id {get; set;}
public string Name {get; set;}
}
Шаг 2.
Затем вам нужно добавить одно свойство DbQuery в ваш класс DBContext для вашего SP.
public partial class Sonar_Health_AppointmentsContext : DbContext
{
public virtual DbSet<Booking> Booking { get; set; } // your existing DbSets
...
public virtual DbQuery<MySPModel> MySP { get; set; } // your new DbQuery
...
}
Шаг 3.
Теперь вы сможете позвонить и получить результат от вашего SP из вашего DBContext.
var result = await _context.Query<MySPModel>().AsNoTracking().FromSql(string.Format("EXEC {0} {1}", functionName, parameter)).ToListAsync();
Я использую универсальный UnitOfWork & Repository. Поэтому моя функция для выполнения SP -
/// <summary>
/// Execute function. Be extra care when using this function as there is a risk for SQL injection
/// </summary>
public async Task<IEnumerable<T>> ExecuteFuntion<T>(string functionName, string parameter) where T : class
{
return await _context.Query<T>().AsNoTracking().FromSql(string.Format("EXEC {0} {1}", functionName, parameter)).ToListAsync();
}
Надеюсь, это будет полезно для кого-то !!!
Ответ 11
Ничего не нужно делать... когда вы создаете dbcontext для кода, сначала подходите, инициализируйте пространство имен под свободной областью API, создайте список sp и используйте его в другом месте, где вам нужно.
public partial class JobScheduleSmsEntities : DbContext
{
public JobScheduleSmsEntities()
: base("name=JobScheduleSmsEntities")
{
Database.SetInitializer<JobScheduleSmsEntities>(new CreateDatabaseIfNotExists<JobScheduleSmsEntities>());
}
public virtual DbSet<Customer> Customers { get; set; }
public virtual DbSet<ReachargeDetail> ReachargeDetails { get; set; }
public virtual DbSet<RoleMaster> RoleMasters { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//modelBuilder.Types().Configure(t => t.MapToStoredProcedures());
//modelBuilder.Entity<RoleMaster>()
// .HasMany(e => e.Customers)
// .WithRequired(e => e.RoleMaster)
// .HasForeignKey(e => e.RoleID)
// .WillCascadeOnDelete(false);
}
public virtual List<Sp_CustomerDetails02> Sp_CustomerDetails()
{
//return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Sp_CustomerDetails02>("Sp_CustomerDetails");
// this.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails");
using (JobScheduleSmsEntities db = new JobScheduleSmsEntities())
{
return db.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails").ToList();
}
}
}
}
public partial class Sp_CustomerDetails02
{
public long? ID { get; set; }
public string Name { get; set; }
public string CustomerID { get; set; }
public long? CustID { get; set; }
public long? Customer_ID { get; set; }
public decimal? Amount { get; set; }
public DateTime? StartDate { get; set; }
public DateTime? EndDate { get; set; }
public int? CountDay { get; set; }
public int? EndDateCountDay { get; set; }
public DateTime? RenewDate { get; set; }
public bool? IsSMS { get; set; }
public bool? IsActive { get; set; }
public string Contact { get; set; }
}
Ответ 12
Если вы выполняете хранимую процедуру из Informix с помощью EntityFrameworkCore, вам необходимо включить команду EXECUTE PROCEDURE
var spresult = _informixContext.procdata.FromSql("EXECUTE PROCEDURE dummyproc ({0},{1},{2})", parameters: new[] { p0, p1,p2 }).ToList();