基于Dapper Extension实现双表通用类映射及仓储/控制器构建

1. 实体类调整 #

由于MessageModel合并了两张表的字段，首先需要为每张表创建独立实体类，再通过联合查询组装成目标模型：

表对应实体类 #

// GuestMessageDetails表实体
public class GuestMessageDetail
{
    public long Id { get; set; }
    public string? CompanyName { get; set; }
    public string? FirstName { get; set; }
    public string? FamilyName { get; set; }
    public string? EmailAddress { get; set; } // 修正原拼写错误EmialAdress
    public long Telephone { get; set; }
    public string? Country { get; set; }
    public string? ArticleNumber { get; set; }
    public string? SoftwareVersion { get; set; }
    public string? SerialNumber { get; set; }
    public string? PurchaseDate { get; set; }
    public string? OrderNumber { get; set; }
}

// ProductTable表实体
public class Product
{
    public long Id { get; set; }
    public string? ArticleImage { get; set; }
    public string? ArticleNumber { get; set; }
}

2. Dapper Extensions映射配置 #

Dapper Extensions需要为每个实体配置表名、主键及字段映射（处理属性与列名不一致的情况）：

映射类实现 #

// GuestMessageDetail映射
public class GuestMessageDetailMapper : ClassMapper<GuestMessageDetail>
{
    public GuestMessageDetailMapper()
    {
        Table("GuestMessageDetails");
        Map(x => x.Id).Key(KeyType.Identity); // 主键自增配置
        Map(x => x.EmailAddress).Column("EmialAdress"); // 适配数据库列名拼写
        AutoMap(); // 自动映射其余匹配属性
    }
}

// Product映射
public class ProductMapper : ClassMapper<Product>
{
    public ProductMapper()
    {
        Table("ProductTable");
        Map(x => x.Id).Key(KeyType.Identity);
        AutoMap();
    }
}

注册映射 #

在应用启动时（如Program.cs）注册映射程序集：

DapperExtensions.DapperExtensions.SetMappingAssemblies(new[] { typeof(GuestMessageDetailMapper).Assembly });

3. 通用仓储扩展 #

基于已有通用仓储基类，扩展实现双表查询及数据操作：

仓储接口定义 #

public interface IGuestMessageRepository : IGenericRepository<GuestMessageDetail>
{
    Task<IEnumerable<MessageModel>> GetAllWithProductInfoAsync();
    Task<MessageModel?> GetByIdWithProductInfoAsync(long id);
    Task<int> InsertMessageWithProductAsync(MessageModel model);
}

仓储实现 #

public class GuestMessageRepository : GenericRepository<GuestMessageDetail>, IGuestMessageRepository
{
    public GuestMessageRepository(IDbConnection connection) : base(connection)
    {
    }

    // 查询所有消息及关联产品信息
    public async Task<IEnumerable<MessageModel>> GetAllWithProductInfoAsync()
    {
        var sql = @"
            SELECT 
                gmd.*, pt.ArticleImage
            FROM GuestMessageDetails gmd
            LEFT JOIN ProductTable pt ON gmd.ArticleNumber = pt.ArticleNumber";
        
        return await Connection.QueryAsync<MessageModel>(sql);
    }

    // 根据ID查询单条消息及关联产品信息
    public async Task<MessageModel?> GetByIdWithProductInfoAsync(long id)
    {
        var sql = @"
            SELECT 
                gmd.*, pt.ArticleImage
            FROM GuestMessageDetails gmd
            LEFT JOIN ProductTable pt ON gmd.ArticleNumber = pt.ArticleNumber
            WHERE gmd.Id = @Id";
        
        return await Connection.QueryFirstOrDefaultAsync<MessageModel>(sql, new { Id = id });
    }

    // 新增消息及关联产品（事务处理）
    public async Task<int> InsertMessageWithProductAsync(MessageModel model)
    {
        using var transaction = Connection.BeginTransaction();
        try
        {
            // 插入消息详情
            var messageId = await InsertAsync(new GuestMessageDetail
            {
                CompanyName = model.companyname,
                FirstName = model.FirstName,
                FamilyName = model.FamilyName,
                EmailAddress = model.EmialAdress,
                Telephone = model.Telephone,
                Country = model.Country,
                ArticleNumber = model.ArticleNumber,
                SoftwareVersion = model.SoftwareVersion,
                SerialNumber = model.SerialNumber,
                PurchaseDate = model.PurchaseDate,
                OrderNumber = model.OrderNumber
            }, transaction);

            // 插入产品（仅当产品不存在时）
            var existingProduct = await Connection.QueryFirstOrDefaultAsync<Product>(
                "SELECT * FROM ProductTable WHERE ArticleNumber = @ArticleNumber", 
                new { model.ArticleNumber }, transaction);
            
            if (existingProduct == null && !string.IsNullOrEmpty(model.ArticleImage))
            {
                await Connection.ExecuteAsync(
                    "INSERT INTO ProductTable (ArticleImage, ArticleNumber) VALUES (@ArticleImage, @ArticleNumber)",
                    new { model.ArticleImage, model.ArticleNumber }, transaction);
            }

            transaction.Commit();
            return messageId;
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }
}

4. 控制器实现 #

创建API控制器暴露业务接口：

[ApiController]
[Route("api/guest-messages")]
public class GuestMessageController : ControllerBase
{
    private readonly IGuestMessageRepository _repository;

    public GuestMessageController(IGuestMessageRepository repository)
    {
        _repository = repository;
    }

    [HttpGet]
    public async Task<IActionResult> GetAll()
    {
        var messages = await _repository.GetAllWithProductInfoAsync();
        return Ok(messages);
    }

    [HttpGet("{id}")]
    public async Task<IActionResult> GetById(long id)
    {
        var message = await _repository.GetByIdWithProductInfoAsync(id);
        if (message == null)
        {
            return NotFound();
        }
        return Ok(message);
    }

    [HttpPost]
    public async Task<IActionResult> Create([FromBody] MessageModel model)
    {
        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        var messageId = await _repository.InsertMessageWithProductAsync(model);
        return CreatedAtAction(nameof(GetById), new { id = messageId }, model);
    }
}

5. 依赖注入注册 #

在Program.cs中注册仓储及数据库连接：

builder.Services.AddScoped<IDbConnection>(sp => 
    new SqlConnection(builder.Configuration.GetConnectionString("YourConnectionString")));
builder.Services.AddScoped<IGuestMessageRepository, GuestMessageRepository>();

内容的提问来源于stack exchange，提问作者Kudiyarasu M