Entity Framework Core中執(zhí)行SQL語句和存儲過程的方法介紹

更新時間：2022年02月21日 15:10:00 作者：Sweet-Tang

這篇文章介紹了Entity Framework Core中執(zhí)行SQL語句和存儲過程的方法，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下

無論ORM有多么強大，總會出現(xiàn)一些特殊的情況，它無法滿足我們的要求。在這篇文章中，我們介紹幾種執(zhí)行SQL的方法。

表結(jié)構(gòu)

在具體內(nèi)容開始之前，我們先簡單說明一下要使用的表結(jié)構(gòu)。

    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來訪問數(shù)據(jù)庫。

FromSql執(zhí)行SQL語句

Entity Framework Core為DbSet<TEntity>提供了一個擴展方法FromSql，用于執(zhí)行SQL語句或存儲過程，以下示例使用FromSql加載所有的數(shù)據(jù)。

        using (var dataContext = new SampleDbContext()) {
        
            var query = dataContext.Categories.FromSql("select * from Category");

            var result = query.ToList();
        }

對于帶有參數(shù)的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，而是轉(zhuǎn)化為參數(shù)化的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語句的參數(shù)：

        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時，需要在執(zhí)行的SQL語句中返回所有列，并且列名必須與實體屬性名相匹配，否則執(zhí)行會出錯。

FromSql執(zhí)行存儲過程

存儲過程與SQL語句寫法基本一致，使用存儲過程的示例如下：

       using (var dataContext = new SampleDbContext()) {

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


           var result = query.ToList();

           Assert.NotNull(result);
       }

這些參數(shù)的順序必須與存儲過程參數(shù)的順序一致。

提示：使用FromSql執(zhí)行存儲過程時，如果使用'Where'、'OrderBy'等Linq語法，這些操作不會生成SQL語句，而是在.Net中對存儲過程返回的集合進行過濾與排序。

ExecuteSqlCommand

在DbContext暴露了一個Database屬性，它包括一個ExecuteSqlCommand方法。此方法返回一個整數(shù)，表示執(zhí)行的SQL語句影響的行數(shù)。有效的操作是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}");
       }