无论ORM有多么强大,总会出现一些特殊的情况,它无法满足我们的要求。在这篇文章中,我们介绍几种执行SQL的方法。
1|0表结构
在具体内容开始之前,我们先简单说明一下要使用的表结构。
public class Category{ public int CategoryID { get; set; } public string CategoryName { get; set; }}
在Category定义了两个字段:CategoryID、CategoryName。
public class SampleDbContext: DbContext{ public virtual DbSet < Category > Categories { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { var sqlConnectionStringBuilder = new SqlConnectionStringBuilder { DataSource = "10.0.1.5", InitialCatalog = "TestDataBase", UserID = "sa", Password = "******" }; optionsBuilder.UseSqlServer(sqlConnectionStringBuilder.ConnectionString); base.OnConfiguring(optionsBuilder); } protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); EntityTypeBuilder < Category > entityTypeBuilder = modelBuilder.Entity < Category > (); entityTypeBuilder.ToTable("Category"); entityTypeBuilder.HasKey(e => e.CategoryID); entityTypeBuilder.Property(e => e.CategoryID).UseSqlServerIdentityColumn(); }}
我们使用SampleDbContext来访问数据库。
2|0FromSql执行SQL语句
Entity Framework Core为DbSet提供了一个扩展方法FromSql,用于执行SQL语句或存储过程,以下示例使用FromSql加载所有的数据。
using(var dataContext = new SampleDbContext()){ var query = dataContext.Categories.FromSql("select * from Category"); var result = query.ToList();}
对于带有参数的SQL语句,我们使用C# 6 语法将SQL写成如下:
using(var dataContext = new SampleDbContext()){ var categoryID = 1; var query = dataContext.Categories.FromSql($ "select * from Category where CategoryID={categoryID}"); 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
![44b325ef2f7681c56150be4f655d1d65.png](https://i-blog.csdnimg.cn/blog_migrate/7466e942cfd78cd9706f6605970e29bf.jpeg)
如果您不使用C# 6的语法特征,我们必须使用 @p0、@p1 ... @pn 做为SQL语句的参数:
using(var dataContext = new SampleDbContext()){ var categoryID = 1; var categoryName = "Product"; var query = dataContext.Categories.FromSql("select * from Category where CategoryID=@p0 and CategoryName=@p1" categoryID, categoryName); var result = query.ToList(); Assert.NotNull(result);}
在上述SQL语句中中,将@p0映射到categoryID、@ p1映射到categoryName。
FromSql扩展方法返回的是IQueryable对象,要们还可以接着使用一些Linq的方法,示例如下:
using(var dataContext = new SampleDbContext()){ var categoryID = 1; var query = dataContext.Categories.FromSql("select * from Category").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语句中返回所有列,并且列名必须与实体属性名相匹配,否则执行会出错。
3|0FromSql执行存储过程
存储过程与SQL语句写法基本一致,使用存储过程的示例如下:
using(var dataContext = new SampleDbContext()){ var categoryID = 1; var query = dataContext.Categories.FromSql($ "GetCategoryById {categoryID}"); var result = query.ToList(); Assert.NotNull(result);}
这些参数的顺序必须与存储过程参数的顺序一致。
提示:使用FromSql执行存储过程时,如果使用'Where'、'OrderBy'等Linq语法,这些操作不会生成SQL语句,而是在.Net中对存储过程返回的集合进行过滤与排序。
4|0ExecuteSqlCommand
在DbContext暴露了一个Database属性,它包括一个ExecuteSqlCommand方法。此方法返回一个整数,表示执行的SQL语句影响的行数。有效的操作是INSERT、UPDATE和DELETE,不能用于返回实体。
using(var dataContext = new SampleDbContext()){ var categoryID = 1; var categoryName = "Product"; var result = dataContext.Database.ExecuteSqlCommand($ "UPDATE dbo.Category SET CategoryName={categoryName} WHERE CategoryID={categoryID}");}
5|0总结
本节介绍了Entity Framework Core中执行SQL语句和存储过程的几种方法