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

表结构

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

    public class Category
    {

        public int CategoryID { get; set; }
        
        public string CategoryName { get; set; }
    }

Category定义了两个字段:CategoryIDCategoryName

    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语句影响的行数。有效的操作是INSERTUPDATEDELETE,不能用于返回实体。

       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语句和存储过程的文章就介绍到这了。希望对大家的学习有所帮助,也希望大家多多支持阿兔在线工具。

点赞(0)

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部