关联导航

场景

我们来看一个常见的电商系统业务场景

要显示如下所示的产品订购记录，应该如何编写查询表达式？

产品名称	下单时间	订购数量	订购价	标价	库存量

解决方案

实体模型

/// <summary>
///订单
/// </summary>
public class Order
{
    /// <summary>
    ///买家名称
    /// </summary>
    private string _buyerName;

    /// <summary>
    ///订单编号
    /// </summary>
    private string _orderCode;

    /// <summary>
    ///下单时间
    /// </summary>
    private DateTime _paymentTime;

    /// <summary>
    ///订单产品
    /// </summary>
    private List<OrderProduct> _products;

    /// <summary>
    ///订单状态
    /// </summary>
    private string _status;

    /// <summary>
    ///总价
    /// </summary>
    private int _totalMoney;

    /// <summary>
    ///订单编号
    /// </summary>
    public string OrderCode
    {
        get => _orderCode;
        set => _orderCode = value;
    }

    /// <summary>
    ///下单时间
    /// </summary>
    public DateTime PaymentTime
    {
        get => _paymentTime;
        set => _paymentTime = value;
    }

    /// <summary>
    ///订单状态
    /// </summary>
    public string Status
    {
        get => _status;
        set => _status = value;
    }

    /// <summary>
    ///总价
    /// </summary>
    public int TotalMoney
    {
        get => _totalMoney;
        set => _totalMoney = value;
    }

    /// <summary>
    ///买家名称
    /// </summary>
    public string BuyerName
    {
        get => _buyerName;
        set => _buyerName = value;
    }

    /// <summary>
    ///订单产品
    /// </summary>
    public List<OrderProduct> Products
    {
        get => _products;
        set => _products = value;
    }
}

/// <summary>
///订单产品
/// </summary>
public class OrderProduct
{
    /// <summary>
    ///订单编号
    /// </summary>
    private readonly string _orderCode;

    /// <summary>
    ///产品编号
    /// </summary>
    private readonly string _productCode;

    /// <summary>
    ///购买量
    /// </summary>
    private int _amount;

    /// <summary>
    ///订单
    /// </summary>
    private Order _order;

    /// <summary>
    ///折扣价格
    /// </summary>
    private int _price;

    /// <summary>
    ///产品
    /// </summary>
    private Product _product;

    /// <summary>
    ///初始化订单产品
    /// </summary>
    /// <param name="order">订单</param>
    /// <param name="product">产品</param>
    public OrderProduct(Order order, Product product)
    {
        _order = order;
        _orderCode = order.OrderCode;
        _product = product;
        _productCode = product.Code;
    }

    /// <summary>
    ///反持久化构造函数
    /// </summary>
    /// <param name="orderCode">订单编号</param>
    /// <param name="productCode">产品编号</param>
    protected OrderProduct(string orderCode, string productCode)
    {
        _orderCode = orderCode;
        _productCode = productCode;
    }

    /// <summary>
    ///订单编号
    /// </summary>
    public string OrderCode => _orderCode;

    /// <summary>
    ///订单
    /// </summary>
    public Order Order
    {
        get => _order;
        set => _order = value;
    }

    /// <summary>
    ///产品编号
    /// </summary>
    public string ProductCode => _productCode;

    /// <summary>
    ///产品
    /// </summary>
    public Product Product
    {
        get => _product;
        set => _product = value;
    }

    /// <summary>
    ///折扣价格
    /// </summary>
    public int Price
    {
        get => _price;
        set => _price = value;
    }

    /// <summary>
    ///购买量
    /// </summary>
    public int Amount
    {
        get => _amount;
        set => _amount = value;
    }
}

/// <summary>
///产品
/// </summary>
public class Product
{
    /// <summary>
    ///库存量
    /// </summary>
    private int _amount;

    /// <summary>
    ///产品编号
    /// </summary>
    private string _code;

    /// <summary>
    ///描述
    /// </summary>
    private string _description;

    /// <summary>
    ///产品名称
    /// </summary>
    private string _name;

    /// <summary>
    ///订单产品
    /// </summary>
    private OrderProduct _orderProduct;

    /// <summary>
    ///价格
    /// </summary>
    private int _price;

    /// <summary>
    ///产品编号
    /// </summary>
    public string Code
    {
        get => _code;
        set => _code = value;
    }

    /// <summary>
    ///产品名称
    /// </summary>
    public string Name
    {
        get => _name;
        set => _name = value;
    }

    /// <summary>
    ///价格
    /// </summary>
    public int Price
    {
        get => _price;
        set => _price = value;
    }

    /// <summary>
    ///描述
    /// </summary>
    public string Description
    {
        get => _description;
        set => _description = value;
    }

    /// <summary>
    ///库存量
    /// </summary>
    public int Amount
    {
        get => _amount;
        set => _amount = value;
    }

    /// <summary>
    ///订单产品
    /// </summary>
    public OrderProduct OrderProduct
    {
        get => _orderProduct;
        set => _orderProduct = value;
    }
}

模型配置

 public class AssociationNavigationConfiguration : MySqlContextConfigProvider
 {
     /// <summary>由派生类实现，获取数据库连接字符串。</summary>
     protected override string ConnectionString => Configuration.MySqlConnectionString;

     /// <summary>获取一个值，该值指示是否启用存储结构映射</summary>
     protected override bool EnableStructMapping => true;

     /// <summary>使用指定的建模器创建对象数据模型。</summary>
     /// <param name="modelBuilder"></param>
     protected override void CreateModel(ModelBuilder modelBuilder)
     {
         //将Order配置为实体型
         var orderEntity = modelBuilder.Entity<Order>();
         //配置主键
         orderEntity.HasKeyAttribute(p => p.OrderCode).HasKeyIsSelfIncreased(false);

         //将Product配置为实体型
         var productEntity = modelBuilder.Entity<Product>();
         //配置主键
         productEntity.HasKeyAttribute(p => p.Code).HasKeyIsSelfIncreased(false);

         //配置OrderProduct显式关联型
         var orderProductAssociation = modelBuilder.Association<OrderProduct>();
         //配置一个反持久化构造函数
         orderProductAssociation.HasConstructor(typeof(OrderProduct).GetConstructor(
                 BindingFlags.Instance | BindingFlags.NonPublic,
                 null, new[] { typeof(string), typeof(string) }, null))
             //指示OrderCode ProductCode已经在此构造函数内赋值 此处需要与反持久化构造函数参数顺序一致
             .Map(p => p.OrderCode).Map(p => p.ProductCode).End();
         //配置关联端 Order关联端为Order这个属性 在关联表中Order的主键OrderCode映射为OrderCode
         orderProductAssociation.AssociationEnd(p => p.Order).HasMapping("OrderCode", "OrderCode");
         //配置关联端 Product关联端为Product这个属性 在关联表中Product的主键Code映射为ProductCode
         orderProductAssociation.AssociationEnd(p => p.Product).HasMapping("Code", "ProductCode");

         //配置Order实体型中的关联引用 左端就是关联型上自己类型的这一端 即Order 右端即为另外一端Product
         orderEntity.AssociationReference(p => p.Products).HasLeftEnd("Order").HasRightEnd("Product");
         //配置Product实体型中的关联引用 左端就是关联型上自己类型的这一端 即Product 右端即为另外一端Order
         productEntity.AssociationReference(p => p.OrderProduct).HasLeftEnd("Product").HasRightEnd("Order");
     }
 }