无 ORM 的 CRUD 操作，附带审计、身份验证和授权

• 下载源代码 - 425 KB

引言

很多时候，我不需要对象关系映射 (ORM) 的复杂性，因为我只是在处理没有业务规则的表，如果它们有关系，我期望 API 调用者提供正确的外部键 ID。我也通常不提供操作复杂父子模型的 API。

不幸的是，在后端编程中我经常看到这样做：

1. 创建一个 C# 模型来映射数据库表。
2. 为每个表创建 CRUD 端点。
3. 将 JSON 反序列化为 C# 模型。
4. 使用 Entity Framework 将记录插入数据库。
5. 甚至更复杂
   1. 对于被软删除的记录，使用 EF 读取记录，更新 Deleted 标志，然后使用 EF 更新记录。
   2. 对于正在更新的记录，使用 EF 读取记录，更新所有属性（通常使用映射工具），然后使用 EF 更新记录。
6. 最后，如果我们要创建创建/更新/删除操作的审计记录，程序员必须记住调用审计服务并提供要记录的创建/更新/删除操作的信息。

哎呀。大量的代码基本上就是一遍又一遍地做同样的事情。像 Swagger Codegen 这样的工具通过生成 CRUD（无意冒犯）后端 API 服务来传播这种混乱，为每个表产生独特但非常相似的实现。

我的解决方案

使用 Dapper 和非常有选择性地使用 Entity Framework，对任何表的任何 CRUD 操作都可以泛化为单个实体控制器中的一组简单 API 端点。此外，后端表服务可以轻松实现创建/更新/删除的审计。这是一套“一次编写，永不操心”的控制器和服务。

我将讨论实现过程

• 控制器和服务作为插件，如我在文章中所述： ASP.NET Core Web API：插件控制器和服务。
• Dapper 作为微型 ORM，但实际上并不用作 ORM。
• FluentMigrator 用于设置测试数据库。
• FluentAssertions 用于集成测试。
• SqlKata 用于涉及表连接以进行授权的一次性需求。
• 集成测试，如我在文章中所述： Fluent Web API 集成测试
• 账户管理，如我在文章中所述： .NET 6 中的简单 Web API 账户管理器
• 迁移，如我在文章中所述： .NET Core 的 FluentMigrator 控制器和服务

运行应用程序之前

编辑 appsettings.json 以获取正确的数据库连接字符串和插件路径。下载中的配置如下：

"UseDatabase": "DefaultConnection",

"ConnectionStrings": {
"DefaultConnection": "Server=localhost;Database=DMS;Integrated Security=True;",
"MasterConnection": "Server=localhost;Database=master;Integrated Security=True;"
},

"Plugins": [
  { "Path": "C:\\projects\\Personal\\PluginNetCoreDemo\\TableService
               \\bin\\Debug\\netcoreapp3.1\\TableService.dll" },
  { "Path": "C:\\projects\\Personal\\PluginNetCoreDemo\\MigratorService
               \\bin\\Debug\\netcoreapp3.1\\MigratorService.dll" },
  { "Path": "C:\\projects\\Personal\\PluginNetCoreDemo\\AuditService
               \\bin\\Debug\\netcoreapp3.1\\AuditService.dll" },
  { "Path": "C:\\projects\\Personal\\PluginNetCoreDemo\\DatabaseService
               \\bin\\Debug\\netcoreapp3.1\\DatabaseService.dll" }
]

这很可能不适用于您的本地计算机。数据库 "DMS"（这与我尚未发布的另一篇文章有关）将为您创建。

架构

上面的图表说明了

1. 我们有四个插件
   1. 审计服务
   2. 表服务和控制器
   3. 数据库服务
   4. 迁移服务和控制器
2. Migrations.dll 中的迁移
3. 各种程序包引用
4. 一个独立的集成测试项目

项目必须组织在 Web API 应用程序的同级目录下，否则 Visual Studio 会在尝试弄清楚项目引用和依赖关系时卡壳。

请注意，服务配置完成了服务的基本设置，*即使您没有明确看到任何服务被添加*，因为它们是插件。

public void ConfigureServices(IServiceCollection services)
{
  services.AddControllers()
    .AddNewtonsoftJson(options => options.SerializerSettings.Formatting = Formatting.Indented);

  services.AddSwaggerGen(c =>
  {
    var xmlFile = $"{Assembly.GetExecutingAssembly().GetName().Name}.xml";
    var xmlPath = Path.Combine(AppContext.BaseDirectory, xmlFile);
    c.IncludeXmlComments(xmlPath);
  });

  var connection = Configuration.GetConnectionString(AppSettings.UseDatabase);
  services.AddDbContext<IAppDbContext, AppDbContext>
                       (options => options.UseSqlServer(connection));

  services
    .AddAuthentication("tokenAuth")
    .AddScheme<TokenAuthenticationSchemeOptions, 
    AuthenticationService>("tokenAuth", ops => { });

  services.AddSingleton<IApplicationService, ApplicationService>();

  services.LoadPlugins(Configuration);
}

Migrations

迁移控制器和服务在我之前的文章中有所介绍， .NET Core 的 FluentMigrator 控制器和服务 - CodeProject，但是这里有一些需要涵盖的点，因为此项目配置为使用 IIS。

运行迁移之前

当 Visual Studio 构建项目时，它将配置 IIS。应用程序池，在此为 Demo AppPool，必须在 SQL Server 的 Security => Logins 下获得权限。

请注意，上面的权限可能过于宽泛。

运行迁移

您可以通过浏览器运行迁移：https:///Demo/migrator/migrateup，您应该会看到：

使用以下命令检查 FluentMigrator 的 VersionInfo 表：https:///Demo/migrator/versionInfo。

您应该会看到：

迁移库

我有一个使用 FluentMigrator 语法的迁移，如下所示：

using FluentMigrator;

namespace DMS.Migrations
{
  [Migration(202201011201)]
  public class _202201011201_CreateTables : Migration
  {
    public override void Up()
    {
      Create.Table("Test")
        .WithColumn("ID").AsInt32().PrimaryKey().Identity().NotNullable()
        .WithColumn("IntField").AsInt32().Nullable()
        .WithColumn("StringField").AsString().Nullable()
        .WithColumn("DateField").AsDate().Nullable()
        .WithColumn("DateTimeField").AsDateTime().Nullable()
        .WithColumn("TimeField").AsTime().Nullable()
        .WithColumn("BitField").AsBoolean().Nullable()
        .WithColumn("Deleted").AsBoolean().NotNullable();

      Create.Table("Audit")
        .WithColumn("ID").AsInt32().PrimaryKey().Identity().NotNullable()
        .WithColumn("Entity").AsString().NotNullable()
        .WithColumn("EntityId").AsInt32().NotNullable()
        .WithColumn("RecordBefore").AsString(int.MaxValue).Nullable()
        .WithColumn("RecordAfter").AsString(int.MaxValue).Nullable()
        .WithColumn("Action").AsString().NotNullable()
        .WithColumn("ActionBy").AsString().NotNullable()
        .WithColumn("ActionDate").AsDateTime().NotNullable().WithDefault
                   (SystemMethods.CurrentDateTime)
        .WithColumn("Deleted").AsBoolean().NotNullable();
    }

    public override void Down()
    {
    }
  }
}

为了帮助我按时间顺序组织迁移，最佳实践是在迁移文件前面加上日期/时间戳，格式为 yyyymmDDhhmm，后跟迁移的描述性名称。

上面的迁移创建了两个表：

1. 一个 Test 表
2. 一个 Audit 表

Test 表用于测试表服务，而 Audit 表用于跟踪对表（实体）的创建/更新/删除更改。我们将在查看其他服务时看到这一切是如何工作的。

数据库服务

此服务没有控制器。我决定创建此服务以便将来可以扩展以支持事务操作。目前该服务相当简单，因为文章中的任何操作都不需要事务。此服务的目的是返回一个 SqlConnection 以供 Dapper 使用。请注意，混合使用 Entity Framework 和 Dapper 调用事务是不可能的，因为它们将是两个独立的连接实例。

using System.Data.SqlClient;

using Microsoft.Extensions.Configuration;

using Interfaces;
using Lib;

namespace Clifton.Services
{
  public class DatabaseService : IDatabaseService
  {
    private readonly IConfiguration cfg;

    public DatabaseService(IConfiguration cfg)
    {
      this.cfg = cfg;
    }

    public SqlConnection GetSqlConnection()
    {
      var cs = cfg.GetConnectionString(AppSettings.Settings.UseDatabase);
      var conn = new SqlConnection(cs);

      return conn;
    }

    // TODO:
    // https://docs.microsoft.com/en-us/ef/core/saving/transactions
    // conn.BeginTransaction();
  }
}

审计服务

Audit Service 目前只实现了一个服务。将来，我们可能会有一个控制器来操作实体、对该实体的更改等。目前，我只想实现记录带有“之前”和“之后”记录的操作。我甚至没有记录是谁做的更改！之前，我提到混合使用 Dapper 和 Entity Framework 事务在一个事务性连接中是不可能的，所以这里我基本上是在违反自己的规则。但老实说，我并不太在意，因为如果 Dapper 事务失败，审计事务将永远不会被创建，正如我们稍后将看到的。事实上，审计事务可以异步执行，以避免延迟响应客户端。

此服务使用 Entity Framework，因为它比使用 Dapper 插入记录要容易得多，Dapper 需要插入 SQL。因此，我们需要一个模型：

using System;
using System.ComponentModel.DataAnnotations;

namespace Models
{
  public class Audit
  {
    [Key]
    public int ID { get; set; }
    public string Entity { get; set; }
    public int EntityId { get; set; }
    public string RecordBefore { get; set; }
    public string RecordAfter { get; set; }
    public string Action { get; set; }
    public string ActionBy { get; set; }
    public DateTime ActionDate { get; set; }
    public bool Deleted { get; set; }
  }
}

目前，“之前”和“之后”的记录被序列化为 JSON。可以有不同的方法来实现审计——通常只审计更改，或者像我在这里所做的那样，审计整个之前/之后记录状态。

using System;

using Interfaces;
using Models;

using Record = System.Collections.Generic.IDictionary<string, object>;

namespace Clifton.Services
{
  public class AuditService : IAuditService
  {
    private readonly IAppDbContext context;

    public AuditService(IAppDbContext context)
    {
      this.context = context;
    }

    public void Insert(string entityName, int entityId, Record before, 
                       Record after, string action)
    {
      var audit = new Audit()
      {
        Entity = entityName,
        EntityId = entityId,
        RecordBefore = before.Serialize(),
        RecordAfter = after.Serialize(),
        Action = action,
        ActionBy = "",
        ActionDate = DateTime.Now,
      };

      // Use EF for this.
      context.Audit.Add(audit);
      context.SaveChanges();
    }
  }
}

表（实体）服务

现在我们来谈谈重点——使用 Dapper 操作表而不使用 Entity Framework ORM。首先，我们有一个用于任何表上 CRUD 操作的控制器。我应该立即指出两点：

1. 您可能不想为数据库中的每个表都支持 CRUD 操作！需要考虑安全问题！
2. 正如您将看到的，表名被硬编码到 SQL 中，因此我们存在 SQL 注入漏洞！有一个解决方法，它很复杂，但在解决第一个问题时有更简单的解决方案。

实体控制器

控制器实现了 CRUD 端点。

using Microsoft.AspNetCore.Mvc;

using Interfaces;

using Parameters = System.Collections.Generic.Dictionary<string, object>;

namespace Clifton.Controllers
{
  [ApiController]
  [Route("[controller]")]
  public class EntityController : ControllerBase
  {
    private ITableService ts;

    public EntityController(ITableService ts)
    {
      this.ts = ts;
    }

    // TODO: Pagination?
    [HttpGet("{entityName}")]
    public ActionResult GetAll(string entityName)
    {
      var result = ts.GetAll(entityName);

      return Ok(result);
    }

    [HttpGet("{entityName}/{entityId}")]
    public ActionResult GetById(string entityName, int entityId)
    {
      var result = ts.GetById(entityName, entityId);
      var ret = result == null ? (ActionResult)NotFound() : Ok(result);

      return ret;
    }

    [HttpPost("{entityName}")]
    public ActionResult Insert(string entityName, Parameters data)
    {
      var result = ts.Insert(entityName, data);

      return Ok(result);
    }

    [HttpPatch("{entityName}/{entityId}")]
    public ActionResult Update(string entityName, int entityId, Parameters data)
    {
      var result = ts.Update(entityName, entityId, data);

      return Ok(result);
    }

    // REMOVE webDAV for this to work! 
    // https://www.c-sharpcorner.com/forums/webapi-delete-405-method-not-allowed
    // Or, if webDAV isn't configured in IIS, this will cause a failure in the web.config file:
    // <modules>
    // <remove name = "WebDAVModule" />
    // </ modules >
    [HttpDelete("{entityName}/{entityId}")]
    public ActionResult SoftDelete(string entityName, int entityId)
    {
      ts.SoftDelete(entityName, entityId);

      return NoContent();
    }

    // REMOVE webDAV for this to work! 
    // https://www.c-sharpcorner.com/forums/webapi-delete-405-method-not-allowed
    // Or, if webDAV isn't configured in IIS, this will cause a failure in the web.config file:
    // <modules>
    // <remove name = "WebDAVModule" />
    // </ modules >
    [HttpDelete("{entityName}/{entityId}/Hard")]
    public ActionResult HardDelete(string entityName, int entityId)
    {
      ts.HardDelete(entityName, entityId);

      return NoContent();
    }
  }
}

表服务

Dapper 的工作方式是让程序员编写 CRUD 操作的 SQL。因此，在大多数使用 Dapper 的应用程序中，您也会在应用程序中看到硬编码的 SQL。真糟糕。我们肯定不想要这个，而且我们也无法这样做，因为 Table Service 是通用的——不是 C# 泛型，而是“通用”一词的定义：“以与一类或一组相似的事物相关的方式；不具体。” 此外，对于查询，Dapper 返回 DapperRow 或 DapperRow 实例的集合，这没关系，因为事实证明 DapperRow 实际上是一个 Dictionary<string, object> 实例，所以它非常适合将响应序列化到客户端。

所以我们先来“using”语句，把它们都放在一边。当人们发布代码示例而不说明“using”对于这些魔法是必需的时，我感到非常恼火。

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

using Dapper;

using Interfaces;
using Lib;

using Record = System.Collections.Generic.IDictionary<string, object>;
using Records = 
  System.Collections.Generic.List<System.Collections.Generic.IDictionary<string, object>>;
using Parameters = System.Collections.Generic.Dictionary<string, object>;

我给 Dictionary 类起了别名，因为它输入 List<Dictionary<string, object>> 或类似内容很烦人，而且它使代码更具可读性。

public 方法镜像了控制器的 CRUD 端点。

namespace Clifton.Services
{
  public class TableService : ITableService
  {
    private readonly IDatabaseService dbSvc;
    private readonly IAuditService auditSvc;

    public TableService(IDatabaseService dbSvc, IAuditService auditSvc)
    {
      this.dbSvc = dbSvc;
      this.auditSvc = auditSvc;
    }

    /// <summary>
    /// Returns the DapperRow collection as a collection of IDictionary string-object pairs.
    /// </summary>
    public Records GetAll(string tableName, Conditions where = null, 
                          Joins joins = null, bool hasDeleted = true)
    {
      var ret = Query<Record>(tableName, null, QueryFnc, where, joins, hasDeleted).ToList();

      return ret;
    }

    public List<T> GetAll<T>(string tableName, 
    Conditions where = null, Joins joins = null, bool hasDeleted = true) where T : new()
    {
      var ret = Query(tableName, null, QueryFnc<T>, where, joins, hasDeleted).ToList();

      return ret;
    }

    /// <summary>
    /// Returns the DapperRow as IDictionary string-object pairs.
    /// </summary>
    public Record GetSingle(string tableName, int recordId, Joins joins = null)
    {
      var ret = Query<Record>(tableName, recordId, QueryFnc, null, joins).SingleOrDefault();

      return ret;
    }

    /// <summary>
    /// Returns the DapperRow as IDictionary string-object pairs.
    /// </summary>
    public Record GetSingle(string tableName, Conditions where)
    {
      var ret = Query<Record>(tableName, null, QueryFnc, where).SingleOrDefault();

      return ret;
    }

    /// <summary>
    /// Returns the DapperRow as IDictionary string-object pairs.
    /// </summary>
    public Record GetSingle(string tableName, Conditions where, Joins joins = null)
    {
      var ret = Query<Record>(tableName, null, QueryFnc, where, joins).SingleOrDefault();

      return ret;
    }

    public Record GetById(string tableName, int entityId)
    {
      var where = Conditions.Where().Field(Constants.ID).Is(entityId);
      var ret = Query<Record>(tableName, null, QueryFnc, where).SingleOrDefault();

      return ret;
    }

    public Record Insert(string tableName, Parameters parms)
    {
      // Returns the full record.
      var ret = Insert(tableName, parms, QueryFnc).SingleOrDefault();
      auditSvc.Insert(tableName, ret[Constants.ID].ToInt(), 
                      null, ret, Constants.AUDIT_INSERT);

      return ret;
    }

    public Record Update(string tableName, int entityId, Parameters parms)
    {
      var before = GetById(tableName, entityId);
      var ret = Update(tableName, entityId, parms, QueryFnc).SingleOrDefault();
      auditSvc.Insert(tableName, entityId, before, ret, Constants.AUDIT_UPDATE);

      return ret;
    }

    public void SoftDelete(string tableName, int entityId)
    {
      var before = GetById(tableName, entityId);
      var parms = new Parameters() { { "ID", entityId }, { Constants.DELETED, true } };
      Update(tableName, entityId, parms, QueryFnc, asDelete: true).SingleOrDefault();
      auditSvc.Insert(tableName, entityId, before, null, Constants.AUDIT_DELETE);
    }

    public void HardDelete(string tableName, int entityId)
    {
      var before = GetById(tableName, entityId);
      using var conn = dbSvc.GetSqlConnection();
      conn.Execute($"delete from {tableName} 
                   where {Constants.ID} = @id", new { id = entityId });
      auditSvc.Insert(tableName, entityId, before, null, Constants.AUDIT_DELETE);
    }
...

忽略关于 Joins 和 Conditions 的内容——这超出了本文的范围，但 var where = Conditions.Where().Field(Constants.ID).Is(entityId); 的作用应该很明显。总之，Conditions 允许客户端指定实体上的复杂搜索条件，而 Joins 允许客户端指定跨表的复杂连接，其中还可以包含 Conditions。如果您想了解更多信息，可以阅读我的文章 自适应分层知识管理 - 第二部分。

总之，重点在于 private 函数。

查询 SQL

首先，我们有两个 Query 函数，一个是通用目的的，另一个是我们有后端模型时的通用函数（但我们没有，但我正在使用 AHKM（见上面链接）的一个通用代码库，我不想仅仅为了这篇文章而专门化它）。

private Records Query(string tableName, int? id, Func<SqlConnection, 
        (string sql, Parameters parms), Records> query, Conditions where = null, 
         Joins joins = null, bool hasDeleted = true)
{
  using var conn = dbSvc.GetSqlConnection();
  var qinfo = SqlSelectBuilder(tableName, id, where, joins, hasDeleted);
  var ret = query(conn, qinfo).ToList();

  return ret;
}

private List<T> Query<T>(string tableName, int? id, Func<SqlConnection, 
        (string sql, Parameters parms), IEnumerable<T>> query, 
         Conditions where = null, Joins joins = null, bool hasDeleted = true)
{
  using var conn = dbSvc.GetSqlConnection();
  var qinfo = SqlSelectBuilder(tableName, id, where, joins, hasDeleted);
  var ret = query(conn, qinfo).ToList();

  return ret;
}

注意两者都调用 SqlSelectBuilder。

private (string sql, Parameters parms) SqlSelectBuilder(string table, int? id, 
         Conditions where = null, Joins joins = null, bool hasDeleted = true)
{
  var sb = GetCoreSelect(table, joins, hasDeleted);

  var parms = new Parameters();

  if (id != null)
  {
    sb.Append($" and {table}.{Constants.ID} = @{Constants.ID}");
    parms.Add(Constants.ID, id.Value);
  }

  where?.AddConditions(sb, parms);

  return (sb.ToString(), parms);
}

它又调用 GetCoreSelect，我们稍后也会看到它被使用。

private StringBuilder GetCoreSelect(string table, Joins joins = null, bool hasDeleted = true)
{
  var joinFields = joins?.GetJoinFields(",") ?? "";
  var joinTables = joins?.GetJoins() ?? "";

  var withDeleteCheck = hasDeleted ? $"where {table}.{Constants.DELETED} = 0" : "";

  StringBuilder sb = new StringBuilder();
  sb.Append($"select {table}.* {joinFields} from {table} {joinTables} {withDeleteCheck}");

  return sb;
}

插入 SQL

使用 Dapper 进行插入是这样的：

private (string sql, Parameters parms) SqlInsertBuilder
        (string table, Parameters parms, Joins joins = null)
{
  if (parms.ContainsKey(Constants.ID))
  {
    parms.Remove(Constants.ID);
  }

  parms[Constants.DELETED] = false;
  var cols = String.Join(", ", parms.Keys.Select(k => k));
  var vals = String.Join(", ", parms.Keys.Select(k => $"@{k}"));
  StringBuilder sb = new StringBuilder();
  sb.Append($"insert into {table} ({cols}) values ({vals});");
  var query = SqlInsertSelectBuilder(table, joins: joins).sql;
  sb.Append(query);

  return (sb.ToString(), parms);
}

这里有几点需要注意。首先，有一个对 SqlInsertSelectBuilder 的调用，因为我们不仅想插入记录，还想将插入的记录取回。这将为我们提供 ID 以及在数据库中作为约束编写的任何默认值或计算值。

private (string sql, Parameters parms) SqlInsertSelectBuilder
        (string table, Conditions where = null, Joins joins = null)
{
  var sb = GetCoreSelect(table, joins);
  sb.Append($" and {table}.{Constants.ID} in (SELECT CAST(SCOPE_IDENTITY() AS INT))");

  var parms = new Parameters();

   return (sb.ToString(), parms);
}

另外请注意，任何“ID”参数都被删除了——ID 是主键（根据我们的规则），我们不希望 Dapper 插入它，因为它是一个自动递增字段。

另请注意，Deleted 参数被设置为 false，并由我们添加。我非常不喜欢可空字段，除非该字段确实是可选的，而且 Deleted 在我看来不是可选的。

最后，由于参数是以字典形式传递的（客户端的 JSON 对象很容易反序列化为字典——请参阅控制器），我们所需要做的就是将名称-值对映射为参数，并将 SQL 语句创建为这些参数的连接。

更新 SQL

更新过程与插入过程类似：

private (string sql, Parameters parms) 
         SqlUpdateBuilder(string table, int id, Parameters parms, bool asDelete = false)
{
  // Remove any ID, Deleted field -- we don't update the deleted flag here.
  parms.Remove(Constants.ID);

  if (!asDelete)
  {
   parms.Remove(Constants.DELETED);
  }

  var setters = String.Join(", ", parms.Keys.Select(k => $"{k}=@{k}"));
  StringBuilder sb = new StringBuilder();
  sb.Append($"update {table} set {setters} where {Constants.ID} = @{Constants.ID};");
  var query = SqlSelectBuilder(table, id).sql;
  sb.Append(query);

  // Add at end, as we're not setting the ID. Here we are setting the parameter, so "id" is OK.
  parms[Constants.ID] = id;

  return (sb.ToString(), parms);
}

这里有一个奇特的可选参数，它说“是的，我确实想更新 Deleted 字段”，如果您注意并仔细阅读了所有代码，您就会在这里看到：

public void SoftDelete(string tableName, int entityId)
{
  var before = GetById(tableName, entityId);
  var parms = new Parameters() { { "ID", entityId }, { Constants.DELETED, true } };
  Update(tableName, entityId, parms, QueryFnc, asDelete: true).SingleOrDefault();
  auditSvc.Insert(tableName, entityId, before, null, Constants.AUDIT_DELETE);
}

哦，看看那个 asDelete: true 参数！

QueryFnc 是什么？

在所有 public CRUD 方法中，您会看到类似这样的内容：

var ret = Query(tableName, null, QueryFnc, where).SingleOrDefault();

这里的神奇的 QueryFnc 是传递给 private CRUD 操作的参数的一部分。

您会看到它被这样调用：

var ret = query(conn, qinfo).ToList();

这都是关于什么的？嗯，我非常讨厌一遍又一遍地编写相同的代码，所以我有两个“查询”函数来完成工作：

private Records QueryFnc(SqlConnection conn, (string sql, Parameters parms) qinfo)
{
  try
  {
    var records = conn.Query(qinfo.sql, qinfo.parms).Cast<Record>().ToList();

    return records;
  }
  catch (Exception ex)
  {
    throw new Exception($"SQL Exception:{ex.Message}\r\n{qinfo.sql}");
  }
}

private List<T> QueryFnc<T>(SqlConnection conn, (string sql, Parameters parms) qinfo)
{
  try
  {
    var records = conn.Query<T>(qinfo.sql, qinfo.parms).ToList();

    return records;
  }
  catch (Exception ex)
  {
    throw new Exception($"SQL Exception:{ex.Message}\r\n{qinfo.sql}");
  }
}

QueryFunc 的目的是将实际的 SQL 调用包装在 try-catch 中，这样我就可以返回一个更智能的错误，包括 SQL，而且我真的不想在所有方法中“污染”各种方法块 try-catch！

请注意，这些方法始终返回一个集合，在 GetAll 之外的所有情况下，结果都只有一个记录。一个函数用于返回通用的 Record 对象，另一个函数用于返回我们有后端模型的情况。

集成测试

那么这一切都奏效吗？

是的，奏效！

让我们看一个基本的集成测试。这是“using”语句，把它们都放在一边：

using System;
using System.Collections.Generic;

using Microsoft.VisualStudio.TestTools.UnitTesting;

using FluentAssertions;

using Clifton.IntegrationTestWorkflowEngine;

using IntegrationTests.Models;
using WorkflowTestMethods;

这是“创建实体”集成测试，使用 FluentAssertions，这很棒，因为如果出现问题，它会告诉你值是什么以及值“应该是什么”：

namespace IntegrationTests
{
  [TestClass]
  public class EntityCrudTests : Setup
  {
    public static Test testData = new Test()
    {
      IntField = 1,
      StringField = "test",
      DateField = DateTime.Parse("8/19/1962"),
      DateTimeField = DateTime.Parse("3/21/1991 7:47 pm"),
      TimeField = DateTime.Parse("12:05 am"),
      BitField = true
    };

    [TestMethod]
    public void CreateEntityTest()
    {
      ClearAllTables();

      var wp = new WorkflowPacket(URL)
        .Post<Test>("entity/test", testData)
        .AndOk()
        .IShouldSee<Test>(t => t.ID.Should().NotBe(0));
    }
...

我在文章中描述了此过程的工作原理： Fluent Web API 集成测试。有趣的部分是基类 Setup：

using System.Data.SqlClient;

using Dapper;

namespace IntegrationTests
{
  public class Setup
  {
    protected string URL = "https:///demo";
    private string connectionString = "Server=localhost;Database=DMS;Integrated Security=True;";

    public void ClearAllTables()
    {
      using (var conn = new SqlConnection(connectionString))
      {
        conn.Execute("delete from Test");
        conn.Execute("delete from Audit");
      }
    }
  }
}

您可能需要为您的本地系统更改 URL 和连接字符串。

我不会在这里放置所有 11 个集成测试的代码，我们只看一个比较有趣的测试，它测试硬删除是否记录在审计表中：

[TestMethod]
public void HardDeleteEntityTest()
{
  int id = -1;

  ClearAllTables();

  var wp = new WorkflowPacket(URL)
    .Post<Test>("entity/test", testData)
    .AndOk()
    .Then(wp => wp.IGet<Test>(t => id = t.ID))
    .Delete($"entity/test/{id}/Hard")
    .AndNoContent()
    .Get<List<Audit>>("entity/audit")
    .IShouldSee<List<Audit>>(r => r.Count.Should().Be(2))
    .IShouldSee<List<Audit>>(r => r.OrderBy
     (q => q.ID).First().Action.Should().Be(Constants.AUDIT_INSERT))
    .IShouldSee<List<Audit>>(r => r.OrderBy
     (q => q.ID).Skip(1).First().Action.Should().Be(Constants.AUDIT_DELETE));
}

我们在这里看到的是：

1. 创建实体
2. 删除实体
3. 获取审计表（其中已清空）
4. 审计表中应该有“INSERT”和“DELETE”这两个操作。

事实上也确实如此，测试通过了，我们可以在 SSMS 的审计表中看到：

select Action, Entity, EntityId from Audit order by ID desc

显示：

房间里的大象

有两个主要问题：

1. SQL 注入攻击的可能性，因为实体名称嵌入在生成的 SQL 中。
2. 任何人都可以访问任何实体并拥有完整的 CRUD 功能。

这两个问题通过添加用户角色权限“系统”来解决。

身份验证：账户服务

首先，我们需要一个账户服务，如我在文章 .NET 6 中的简单 Web API 账户管理器 中所述。只需阅读那篇文章，因为它描述了如何添加用户身份验证。

授权：权限

添加权限涉及添加一些表来管理用户角色权限，这属于授权类别。这里有点疯狂。由于这种实现的通用性，用户的权限必须与他们被允许访问的一个或多个实体相关联。授权不是在特定服务或端点上创建的，而是直接与实体相关联。这使得授权完全脱离了控制器或端点上的属性，而是进入了配置表。请注意，这里的实现并没有深入探讨所有权问题——这是一个单独的问题，我将在关于实现文档管理系统的后续文章中讨论。

新的迁移

授权需要这些表：

• 角色
• UserRole
• 实体
• EntityRole

其思想是，用户的角色的 CRUD“授权”必须与该角色在一个或多个实体上的“授权”相匹配，否则用户没有该 CRUD 操作的权限。

这是迁移：

[Migration(202201011202)]
public class _202201011202_PermissionsAndRoles : Migration
{
  public override void Up()
  {
  Create.Table("Role")
    .WithColumn("Id").AsInt32().PrimaryKey().Identity().NotNullable()
    .WithColumn("Name").AsString().NotNullable()
    .WithColumn("CanCreate").AsBoolean().NotNullable()
    .WithColumn("CanRead").AsBoolean().NotNullable()
    .WithColumn("CanUpdate").AsBoolean().NotNullable()
    .WithColumn("CanDelete").AsBoolean().NotNullable()
    .WithColumn("Deleted").AsBoolean().NotNullable();

  Create.Table("UserRole")
    .WithColumn("Id").AsInt32().PrimaryKey().Identity().NotNullable()
    .WithColumn("RoleId").AsInt32().NotNullable().ForeignKey("Role", "ID")
    .WithColumn("UserId").AsInt32().NotNullable().ForeignKey("User", "ID")
    .WithColumn("Deleted").AsBoolean().NotNullable();

  Create.Table("Entity")
    .WithColumn("Id").AsInt32().PrimaryKey().Identity().NotNullable()
    .WithColumn("TableName").AsString().NotNullable()
    .WithColumn("Deleted").AsBoolean().NotNullable();

  Create.Table("EntityRole")
    .WithColumn("Id").AsInt32().PrimaryKey().Identity().NotNullable()
    .WithColumn("RoleId").AsInt32().NotNullable().ForeignKey("Role", "ID")
    .WithColumn("EntityId").AsInt32().NotNullable().ForeignKey("Entity", "ID")
    .WithColumn("Deleted").AsBoolean().NotNullable();
}

授权服务

在 startup.cs 中添加了一个策略。

services
  .AddAuthorization(options => options.AddPolicy
                   (Constants.ENTITY_AUTHORIZATION_SCHEME, policy =>
  {
    policy.RequireAuthenticatedUser();
    policy.Requirements.Add(new UserHasEntityPermission());
  }));

  services.AddScoped<IAuthorizationHandler, EntityAuthenticationService>();

服务实现调用实体服务来验证实体以及用户对实体的操作。

protected override Task HandleRequirementAsync
   (AuthorizationHandlerContext context, UserHasEntityPermission requirement)
{
  var claims = context.User.Identity as ClaimsIdentity;
  var token = claims.FindFirst("token").Value;
  var method = claims.FindFirst("method").Value;
  var path = claims.FindFirst("path").Value;
  var authorized = false;

  if (path.StartsWith("/entity/"))
  {
    var entityName = path.RightOf("/entity/").LeftOf("/");
    var user = acctSvc.GetUser(token);
    authorized = user.IsSysAdmin ? entityService.IsEntityValid(entityName) : 
                 entityService.IsUserActionAuthorized(entityName, user.Id, method);
  }

  if (authorized)
  { 
    context.Succeed(requirement);
  }

  return Task.CompletedTask;
}

实体验证和授权

考虑到 SysAdmin 拥有所有权限，我们只想验证实体是否确实是数据库中的表。

public bool IsEntityValid(string entityName)
{
  var recs = GetAll("TABLES", Conditions.Where().Field("TABLE_NAME").Is(entityName), 
                     hasDeleted: false, schema: "INFORMATION_SCHEMA");

  return recs.Any();
}

反之，用户应该拥有他们想要对表执行的任何 CRUD 操作的权限。

使用 SqlKata，我们可以使用流畅的语法编写查询，而不是硬编码 SQL 语句供 Dapper 使用。顺便说一句，SqlKata 在后台也使用 Dapper。现在，一个典型的 SqlKata 查询如下所示：

 var query = db.Query("Role")
  .Join("UserRole", "Role.Id", "UserRole.RoleId")
  .Join("EntityRole", "Role.Id", "EntityRole.RoleId")
  .Join("Entity", "Entity.Id", "EntityRole.EntityId")
  .Where("Entity.TableName", entityName)
  .Where("UserRole.UserId", userId);

我真的很讨厌硬编码字符串，也不想使用常量，所以我实现了一些扩展方法，这样我就可以这样写：

var query = db.Query<Role>()
  .Join<Role, UserRole>()
  .Join<Role, EntityRole>()
  .JoinChild<EntityRole, Entity>()
  .Where<Entity>(nameof(Entity.TableName), entityName)
  .Where<UserRole>(nameof(UserRole.UserId), userId);

这反过来又需要一些最小的模型实现：

public class Role { }
public class UserRole 
{
  public int UserId { get; set; }
}
public class EntityRole { }
public class Entity 
{
  public string TableName { get; set; }
}

真有趣，不是吗？

扩展方法在我之前的文章 SqlKata 扩展方法 中进行了描述。

回到主题：确定用户是否被授权对实体（表）执行特定 CRUD 操作的完整实现如下所示：

public bool IsUserActionAuthorized(string entityName, int userId, string method)
{
  var connection = dbSvc.GetSqlConnection();
  var compiler = new SqlServerCompiler();

  var db = new QueryFactory(connection, compiler);

  var query = db.Query<Role>()
    .Join<Role, UserRole>()
    .Join<Role, EntityRole>()
    .JoinChild<EntityRole, Entity>()
    .Where<Entity>(nameof(Entity.TableName), entityName)
    .Where<UserRole>(nameof(UserRole.UserId), userId);

  var data = query.Get<Permissions>();

  bool ok = method.MatchReturn(
    (m => m == "GET", _ => data.Any(d => d.CanRead)),
    (m => m == "POST", _ => data.Any(d => d.CanCreate)),
    (m => m == "PATCH", _ => data.Any(d => d.CanUpdate)),
    (m => m == "DELETE", _ => data.Any(d => d.CanDelete)),
    (_ => true, _ => false)); // anything else

  return ok;
}

是的，它使用了模型 Permissions。

public class Permissions
{
  public bool CanCreate { get; set; }
  public bool CanRead { get; set; }
  public bool CanUpdate { get; set; }
  public bool CanDelete { get; set; }
}

Match 语法在我之前的文章 停止编写 Switch 和 If-Else 语句！ 中进行了描述。

实体控制器属性

EntityController 中的每个端点现在都带有装饰：

[Authorize(Policy = Constants.ENTITY_AUTHORIZATION_SCHEME)]

然后 .NET Core 完成了剩下的工作。

集成测试

现在我们可以编写一些集成测试来验证此代码是否按预期工作。首先，我编写了一个通用的扩展方法来创建一个具有我想要测试的权限的测试用户账户：

public static WorkflowPacket CreateUserAndEntityRole(this WorkflowPacket wp, string entity, 
       string username, string password, string roleName, Permissions permissions)
{
  int roleId = -1;
  int entityId = -1;
  int userId = -1;

  wp
    .Login()
    .Post<User>("account", new { username, password })
    .AndOk()
    .IShouldSee<User>(u => u.Id.Should().NotBe(0))
    .IGet<User>(u => userId = u.Id)
    .Log($"User ID = {userId}")

    .Post<Role>("entity/role", new
    {
        Name = roleName,
        CanCreate = permissions.CanCreate,
        CanRead = permissions.CanRead,
        CanUpdate = permissions.CanUpdate,
        CanDelete = permissions.CanDelete,
    })
    .AndOk()
    .IShouldSee<Role>(r => r.Id.Should().NotBe(0))
    .IGet<Role>(r => roleId = r.Id)

    .Post<Entity>("entity/entity", new { TableName = entity })
    .AndOk()
    .IShouldSee<Entity>(e => e.Id.Should().NotBe(0))
    .IGet<Entity>(e => entityId = e.Id)

    // Map EntityRole and UserRole.
    .Post<UserRole>("entity/userrole", new { RoleId = roleId, UserId = userId })
    .AndOk()
    .IShouldSee<UserRole>(ur => ur.Id.Should().NotBe(0))
    .Post<EntityRole>("entity/entityrole", new { RoleId = roleId, EntityId = entityId })
    .AndOk()
    .IShouldSee<EntityRole>(er => er.Id.Should().NotBe(0));

  return wp;
}

现在我可以编写集成测试了。

用户可以创建实体记录测试

[TestMethod]
public void UserCanCreateEntityTest()
{
  ClearAllTables();

  var wp = new WorkflowPacket(URL)
    .CreateUserAndEntityRole("Test", "Marc", "fizbin", "CreateEntityRole", 
                              new Permissions() { CanCreate = true })
    .Login("Marc", "fizbin")
    .Post<Test>("entity/test", testData)
    .AndOk()
    .IShouldSee<Test>(t => t.ID.Should().NotBe(0));
}

此测试验证具有“Test”表创建权限的用户确实可以创建记录。

用户无法创建实体记录测试

[TestMethod]
public void UserCannotCreateEntityTest()
{
  ClearAllTables();

  var wp = new WorkflowPacket(URL)
    .CreateUserAndEntityRole("Test", "Marc", "fizbin", "CreateEntityRole", 
                              new Permissions() { CanRead = true })
    .Login("Marc", "fizbin")
    .Post<Test>("entity/test", testData)
    .AndForbidden();
}

在这里，用户被授予了读取权限，而不是创建权限，集成测试验证了如果调用端点来创建记录，应用程序将返回“Forbidden”。

用户可以读取实体测试

[TestMethod]
public void UserCanReadEntityTest()
{
  ClearAllTables();

  var wp = new WorkflowPacket(URL)
    .CreateUserAndEntityRole("Test", "Marc", "fizbin", "CreateEntityRole", 
                              new Permissions() { CanRead = true })
    // Post something as SysAdmin
    .Post<Test>("entity/test", testData)
    .Login("Marc", "fizbin")
    .Get<List<Test>>("entity/test")
    .AndOk()
    .IShouldSee<List<Test>>(data => data.Count.Should().Be(1));
}

在这里，记录由拥有所有权限的 SysAdmin 创建，并且该记录可以由具有读取权限的用户读取。如前所述，记录所有权的概念超出了本文的范围。

实体错误测试

[TestMethod]
public void BadEntityTest()
{
  ClearAllTables();

  var wp = new WorkflowPacket(URL)
    .CreateUserAndEntityRole("Test", "Marc", "fizbin", "CreateEntityRole", 
                              new Permissions() { CanCreate = true })
    .Login("Marc", "fizbin")
    .Post<Test>("entity/test2", testData)
    .AndForbidden();
}

在这里，用户正试图在不存在的实体“test2”中创建记录。

SysAdmin 实体错误测试

[TestMethod]
public void SysAdminBadEntityTest()
{
  ClearAllTables();
 
  var wp = new WorkflowPacket(URL)
    .CreateUserAndEntityRole("Test", "Marc", "fizbin", "CreateEntityRole", 
                              new Permissions() { CanCreate = true })
    .Post<Test>("entity/test2", testData)
    .AndForbidden();
}

在这里，SysAdmin 正试图在不存在的实体“test2”中创建记录。

应该离开房间的大象们

此时，我已经解决了授权大象问题，实际上也解决了 SQL 注入大象问题。就我个人而言，我并不太喜欢 SQL 注入仍然存在于内部使用实体服务方面的顾虑，我觉得这个问题确实需要更好地解决。我也对需要进行多少系统管理工作才能为用户授予所需实体上的正确权限感到担忧。一个好的前端可以缓解这一点。但我确实喜欢按用户角色控制 CRUD 操作的精细度。所以有几个小问题需要解决，但不是现在。

所有集成测试都通过了

我认为此时我有一套不错的集成测试来演示这项技术按预期工作。

结论

我希望您能从这种无 ORM 的通用方法中受益，用于对表执行 CRUD 操作，我希望这能减少您编写或自动生成的每个表对应的控制器、模型（似乎称为 POCO——纯旧 CLR 对象）、服务以及其他代码的数量！如果有一种方法可以强制 Entity Framework 以无模型的方式工作，我会非常惊喜！

我也希望我已经展示了下面列出的各种架构和技术：

• 插件控制器和服务
• 流畅集成测试
• Dapper
• SqlKata
• FluentMigrator
• FluentAssertions

老实说，这在一定程度上是实验性的，也并非完全是。我打算将此架构用于其他文章，特别是关于我正在撰写的一份文档管理系统的文章。即便如此，我怀疑这是否适合所有人。它太奇怪了，人们和公司都喜欢 ORM。引入本文所述的技术可能会激起许多其他开发人员和架构师的反对。但我就是我行我素！

历史

• 2022 年 2 月 8 日：初始版本