标签 标签 标签

• 一句话的事儿：

01.创建一个空的Migration文件

步骤1：Add-migration AddView

public partial class AddView : Migration
{
    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].[GamePlayer] WHERE [PlayerId]=@playerId 
            Return 0 ");
    }
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            @"DROP PROCEDURE [dbo].[RemoveGamePlayersProcedure] ");
        migrationBuilder.Sql(
            @"DROP VIEW [dbo].[PlayerClubView] ");
    }
}

步骤2：Update-DataBase

步骤3：查询PlayerClubView

• 在DataContex中注入 ```csharp modelBuilder.Entity() .HasNoKey() .ToView(“PlayerClubView”);

//无Primary Key public DbSet PlayerClubs { get; set; }

- 查询
```csharp
var playerClubs = context.PlayerClubs.ToList();

• 查询结果

• 增加筛选条件

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

  

  02.原生SQL 语句

  

• 查询01：

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

  

• 查询02：

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

  

  03.原生SQL查询的要求

  

• 查询03

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

  

• 执行SP，前提是返回的字段必须与Entity一致匹配

  var data = context.Clubs
    .FromSqlInterpolated($"EXEC RemoveGamePlayersProcedure WHERE Id>{0}")
    .ToList();

  04.执行非查询类的SQL语句

  

• 执行存储过程

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

  

  05.注册EF Core服务

  public DataContext(DbContextOptions<DataContext> options) : base(options)
  {
  }

• 本文作者：GeekPower - Felix Sun
• 版权声明： 本博客所有文章除特别声明外，均采用 MIT 许可协议。转载请注明出处！