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

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();
    }
}

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

using (var dataContext = new SampleDbContext()) {
    var categoryID = 1;
    var query = dataContext.Categories.FromSql($"select * from Category where CategoryID=    {categoryID}");
    var result = query.ToList();
}

exec sp_executesql N''select * from Category where CategoryID=@p0
'',N''@p0 int'',@p0=1

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);
}

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();
}

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

using (var dataContext = new SampleDbContext()) {
    var categoryID = 1;
    var query = dataContext.Categories.FromSql($"GetCategoryById {categoryID}");
    var result = query.ToList();
    Assert.NotNull(result);
}

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}");
}

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

    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();
        }
    }

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

        using (var dataContext = new SampleDbContext()) {        
            var categoryID = 1;            
            var query = dataContext.Categories.FromSql($"select * from Category where CategoryID={categoryID}");      
              var result = query.ToList();
        }

exec sp_executesql N''select * from Category where CategoryID=@p0'',N''@p0 int'',@p0=1

        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);
        }

       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();
       }

exec sp_executesql N''SELECT [item].[CategoryID], [item].[CategoryName]FROM (    select * from Category) AS [item]WHERE [item].[CategoryID] = @__categoryID_1ORDER BY [item].[CategoryName]'',N''@__categoryID_1 int'',@__categoryID_1=1

       using (var dataContext = new SampleDbContext()) {           var categoryID = 1;       
           var query = dataContext.Categories.FromSql($"GetCategoryById {categoryID}");           var result = query.ToList();

           Assert.NotNull(result);
       }

       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}");
       }

本文分享自微信公众号 - dotNET跨平台（opendotnet）。
如有侵权，请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”，欢迎正在阅读的你也加入，一起分享。

Entity Framework Core中执行SQL语句和存储过程的方法介绍

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

表结构

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

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

EntityFramework Core 使用原生 SQL 语句，执行存储过程和视图

参考资料：
微软 MVP 杨旭教程：https://www.bilibili.com/video/BV1xa4y1v7rR?p=10

• 创建视图和存储过程
• 无主键的 Entity
• 原生 SQL 查询
• 原生 SQL 查询的要求
• 字符串插值
• 执行非查询类 SQL

在 EF Core 项目中，如果想在数据库中添加视图或者存储过程或者类似的东西，不可以直接操作数据库，而应该把生成视图或生成存储过程的脚本放在一个 Migration 里面，让它来执行生成视图或者创建存储过程。

创建视图和存储过程

直接 Add 一个空的 Migration，然后再修改 Migration 的代码。空的 Migration：

public partial class AddView : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {

    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {

    }
}

然后使用 migrationBuilder.Sql()，把 SQL 脚本用 @"xxxx" 包裹起来当作参数放进去。创建视图和存储过程的脚本都应该放在 Up 方法中，先创建视图再创建存储过程：

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(
        @"CREATE VIEW [dbo].[PlayerClubView]
            AS SELECT p.Id as PlayerId, p.Name as PlayerName, c.Name as ClubName
            FROM [dbo].[Players] as p
            INNER JOIN [dbo].[Clubs] as c
            ON p.ClubId = c.Id");
    migrationBuilder.Sql(
        @"CREATE PROCEDURE [dbo].[RemoveGamePlayersProcedure] @playerId int = 0
            AS 
                DELETE FROM [dbo].[GamePlayers] WHERE [PlayerId] = @playerId
            RETURN 0");
}

同时 Down 方法中应该包含回滚的操作，如果更新失败就回滚，回滚时应该先删除存储过程再删除视图：

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(
        @"DROP PROCEDURE [dbo].[RemoveGamePlayersProcedure]");
    migrationBuilder.Sql(
        @"DROP VIEW [dbo].[PlayerClubView]");
}

Update-Database 之后，看一下数据库：

可以看到视图和存储过程全都有了。

无主键的 Entity

我们建立的实体 Model 基本都有主键，而且基本都是 Id，但：

• .NET Core 3.1 允许无主键的 Entity
• 它们不会被追踪
• 映射到没有主键的 Table 或者 View

我们根据刚才建立的视图建立一个没有主键的模型：

public class PlayerClub
{
    public int PlayerId { get; set; }
    public string PlayerName { get; set; }
    public string ClubName { get; set; }
}

然后把这个类添加到 Context 里的 DbSet 属性。但 DbSet 不识别没有主键的类，我们需要再 OnModelCreating 中设置一下，用 HasNoKey() 方法设置 PlayerClub 这个 Entity。但只这样设置还不足够，如果后面再添加迁移，它会认为我们想要创建 PlayerClubs 这样一个 Table，所以要用 ToView() 方法把它映射到我们前面创建的视图 [dbo].[PlayerClubView] 上。需要稍微修改一下视图名，去掉中括号和 dbo：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    ...
    modelBuilder.Entity<PlayerClub>()
        .HasNoKey()
        .ToView("PlayerClubView");
}

针对这种没有主键的 Entity，查出来的结果都是无法追踪的。

然后我们像查表一样查一下视图：

var playerClubs = context.PlayerClubs.ToList();

这种查询也可以加 Where 查询条件：

var playerClubs = context.PlayerClubs
    .Where(x => x.PlayerId > 1)
    .ToList();

但如果使用 DbSet 的 Find() 方法，在编译的时候不会报错，在运行的时候会报错。因为 Find 后面跟的是主键，而这个视图没有主键。

原生 SQL 查询

一共有两种方法，还有它们各自的异步版本：

• FromSQLRaw("SELECT" * ...")
• FromSQLRawAsync("SELECT" * ...")
• FromSQLInterpolated($"SELECT * WHERE x={var}")
• FromSQLInterpolatedAsync($"SELECT * WHERE x={var}")

第二种方法支持 C#6 出现的字符串插值。这几种方法都是 DbSet 的方法，所以只能针对 DbSet 来执行这个方法。最后还要调用 ToList() 等方法，否则查询不会执行。

var leagues = context.Leagues
    .FromSqlRaw("SELECT * FROM dbo.Leagues")
    .ToList();

也可以加查询条件和 Include() 等。过滤条件可以在 SQL 语句里面写，在外面写没有太大意义：

var clubs = context.Clubs
    .FromSqlRaw("SELECT * FROM dbo.Clubs")
    .Include(x => x.League)
    .Include(x => x.Players)
        .ThenInclude(x => x.GamePlayers)
    .ToList();

原生 SQL 查询的要求

• 必须返回 Entity 类型的所有（标量）属性

  也就是 SELECT *，如果写列名必须一个不多一个不少，而且不包含导航属性

• 字段名和 Entity 的属性名匹配
• 无法包含关联的数据

  指在 SQL 语句中无法包含关联的数据

• 只能查询已知的 Entity

字符串插值

插值的部分在生成的 SQL 语句中也是 SQL 参数。

var id = 0;

var clubs = context.Clubs
    .FromSqlInterpolated($"SELECT * FROM dbo.Clubs WHERE Id > {id}")
    .ToList();

如果数据库中有对应的 Club 类的存储过程的话，Clubs.FromSqlInterpolated() 和 Clubs.FromSqlRaw() 也可以执行存储过程。前提是要求存储过程返回的字段必须与 Club 类匹配。

执行非查询类 SQL

执行非查询类 SQL，包括执行非查询类的存储过程，不能使用 DbSet 的方法，应当使用 Context 的 Database 属性，它有下面两种方法，各自还有一个异步方法。。

• Context.Database.ExecuteSQLRaw()
• Context.Database.ExecuteSQLRawAsync()
• Context.Database.ExecuteSQLInterpolated()
• Context.Database.ExecuteSQLInterpolatedAsync()
• 无法用于查询
• 只能返回影响的行数

var count = context.Database
    .ExecuteSqlRaw("EXEC dbo.RemoveGamePlayersProcedure {0}", 2);

count = context.Database
    .ExecuteSqlInterpolated($"EXEC dbo.RemoveGamePlayersProcedure {2}");

可以看到无论那种方法，都使用了参数形式，因为这是非查询类 SQL 语句，不使用参数的话很容易被 SQL 注入。

我们今天的关于通过Zend Framework执行MSSQL存储过程的分享已经告一段落，感谢您的关注，如果您想了解更多关于Entity Framework Core 执行 SQL 语句和存储过程、Entity Framework Core 执行SQL语句和存储过程、Entity Framework Core中执行SQL语句和存储过程的方法介绍、EntityFramework Core 使用原生 SQL 语句，执行存储过程和视图的相关信息，请在本站查询。