Entity Framework Core 执行SQL语句和存储过程

houbin

　　无论ORM有多么强大，总会出现一些特殊的情况，它无法满足我们的要求。在这篇文章中，我们介绍几种执行SQL的方法。

表结构
　　在具体内容开始之前，我们先简单说明一下要使用的表结构。
    public>{　　

　　public int CategoryID { get; set; }
　　public string CategoryName { get; set; }
　　}
　　在Category定义了两个字段：CategoryID、CategoryName。
    public>{　　public virtual DbSet<Category> Categories { get; set; }
　　

　　protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
　　{
　　var sqlConnectionStringBuilder = new SqlConnectionStringBuilder {
　　DataSource = &quot;10.0.1.5&quot;,
　　InitialCatalog = &quot;TestDataBase&quot;,
　　UserID = &quot;sa&quot;,
　　Password = &quot;******&quot;
　　};
　　optionsBuilder.UseSqlServer(sqlConnectionStringBuilder.ConnectionString);
　　

　　base.OnConfiguring(optionsBuilder);
　　}
　　

　　protected override void OnModelCreating(ModelBuilder modelBuilder)
　　{
　　base.OnModelCreating(modelBuilder);
　　EntityTypeBuilder<Category> entityTypeBuilder = modelBuilder.Entity<Category>();
　　entityTypeBuilder.ToTable(&quot;Category&quot;);
　　entityTypeBuilder.HasKey(e => e.CategoryID);
　　entityTypeBuilder.Property(e => e.CategoryID).UseSqlServerIdentityColumn();
　　}
　　}
　　我们使用SampleDbContext来访问数据库。

FromSql执行SQL语句
　　Entity Framework Core为DbSet<TEntity>提供了一个扩展方法FromSql，用于执行SQL语句或存储过程，以下示例使用FromSql加载所有的数据。
        using (var dataContext = new SampleDbContext()) {　　var query = dataContext.Categories.FromSql(&quot;select * from Category&quot;);
　　

　　var result = query.ToList();
　　}
　　对于带有参数的SQL语句，我们使用C# 6 语法将SQL写成如下：
        using (var dataContext = new SampleDbContext()) {　　var categoryID = 1;
　　var query = dataContext.Categories.FromSql($&quot;select * from Category where CategoryID={categoryID}&quot;);
　　

　　var result = query.ToList();
　　}
　　注意：这里不是直接使用拼接的方式处理SQL，而是转化为参数化的SQL语句，这有助于防止SQL注入攻击。我们可以使用SQL Server Profiler帮我们验证：
exec sp_executesql N'select * from Category where CategoryID=@p0　　
',N'@p0 int',@p0=1

　　如果您不使用C# 6的语法特征，我们必须使用 @p0、@p1 ... @pn 做为SQL语句的参数：
        using (var dataContext = new SampleDbContext()) {　　

　　var categoryID = 1;
　　var categoryName = &quot;Product&quot;;
　　var query = dataContext.Categories.FromSql(&quot;select * from Category where CategoryID=@p0 and CategoryName=@p1&quot;
　　categoryID, categoryName);
　　

　　var result = query.ToList();
　　

　　Assert.NotNull(result);
　　}
　　在上述SQL语句中中，将@p0映射到categoryID、@ p1映射到categoryName。
　　FromSql扩展方法返回的是IQueryable<TEntity>对象，要们还可以接着使用一些Linq的方法，示例如下：
       using (var dataContext = new SampleDbContext()) {　　

　　var categoryID = 1;
　　

　　var query = dataContext.Categories.FromSql(&quot;select * from Category&quot;)
　　.Where(item => item.CategoryID == categoryID)
　　.OrderBy(item => item.CategoryName);
　　

　　var result = query.ToList();
　　}
　　不过在这里，使用的是子查询，使用SQL Server Profiler捕获到的SQL语句如下：
exec sp_executesql N'SELECT [item].[CategoryID], [item].[CategoryName]　　
FROM (
　　
    select * from Category
　　
) AS [item]
　　
WHERE [item].[CategoryID] = @__categoryID_1
　　
ORDER BY [item].[CategoryName]',N'@__categoryID_1 int',@__categoryID_1=1

　　提示：使用FromSql时，需要在执行的SQL语句中返回所有列，并且列名必须与实体属性名相匹配，否则执行会出错。

FromSql执行存储过程
　　存储过程与SQL语句写法基本一致，使用存储过程的示例如下：
       using (var dataContext = new SampleDbContext()) {　　

　　var categoryID = 1;
　　var query = dataContext.Categories.FromSql($&quot;GetCategoryById {categoryID}&quot;);
　　

　　

　　var result = query.ToList();
　　

　　Assert.NotNull(result);
　　}
　　这些参数的顺序必须与存储过程参数的顺序一致。

　　提示：使用FromSql执行存储过程时，如果使用'Where'、'OrderBy'等Linq语法，这些操作不会生成SQL语句，而是在.Net中对存储过程返回的集合进行过滤与排序。

ExecuteSqlCommand
　　在DbContext暴露了一个Database属性，它包括一个ExecuteSqlCommand方法。此方法返回一个整数，表示执行的SQL语句影响的行数。有效的操作是INSERT、UPDATE和DELETE，不能用于返回实体。
       using (var dataContext = new SampleDbContext())　　{
　　var categoryID = 1;
　　var categoryName = &quot;Product&quot;;
　　var result = dataContext.Database.ExecuteSqlCommand($&quot;UPDATE dbo.Category SET CategoryName={categoryName} WHERE CategoryID={categoryID}&quot;);
　　}

总结
　　本节介绍了Entity Framework Core中执行SQL语句和存储过程的几种方法， 希望对您有帮助，谢谢！

　　作者：Sweet Tang
　　
本文地址：http://www.cnblogs.com/tdfblog/p/execute-sql-stored-procedure-in-entity-framework-core.html
　　
欢迎转载，请在明显位置给出出处及链接。