
表结构
在具体内容开始之前,我们先简单说明一下要使用的表结构。
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
来访问数据库。
FromSql 执行 SQL 语句
Entity Framework Core 为DbSet<TEntity>
提供了一个扩展方法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
如果您不使用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<TEntity>
对象,要们还可以接着使用一些 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 语句中返回所有列,并且列名必须与实体属性名相匹配,否则执行会出错。
FromSql 执行存储过程
存储过程与 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 中对存储过程返回的集合进行过滤与排序。
ExecuteSqlCommand
在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}");
}
总结
本节介绍了 Entity Framework Core 中执行 SQL 语句和存储过程的几种方法, 希望对您有帮助,谢谢!
原文地址:https://www.cnblogs.com/tdfblog/p/execute-sql-stored-procedure-in-entity-framework-core.html