数据库设计 外键设计 关联表查询 linq efcore

数据库的设计

实体之间的关系图

实体

EleOrderRowItem.cs

public class EleOrderRowItem : EleEntity
{

    public string? Xxx { get; set; }

    // 外键：一个rowItem属于一个row 
    public long RowId { get; set; }
    public required EleOrderRow Row { get; set; }
}

EleOrderRow.cs

public class EleOrderRow : EleEntity
{

    public string? Xxx { get; set; }

    // 外键：一个Row属于一个request
    public long RequestId { get; set; }
    public required EleOrderRequest Request { get; set; }

    // 导航属性：一个row可以有多个rowItem
    public required ICollection<EleOrderRowItem> RowItems { get; set; }

}

EleOrderRequest.cs

public class EleOrderRequest : EleEntity
{

    public string? Xxx { get; set; }

    // 外键：一个request属于一个order
    public long OrderId { get; set; }
    public required EleOrder Order { get; set; }

    // 导航属性：一个request可以有多个row
    public required  ICollection<EleOrderRow> Rows { get; set; }

}

EleOrderHeader.cs

public class EleOrderHeader : EleEntity
{

    public string? Xxx { get; set; }

    // 外键：一个messageHeader属于一个order
    public long OrderId { get; set; }
    public required EleOrder Order { get; set; }

}

EleOrder.cs

public class EleOrder : EleEntity
{
    /// <summary>
    /// 导航属性：一个Order有一个MessageHeader
    /// </summary>
    [Required]
    public  EleOrderHeader MessageHeader { get; set; }

    /// <summary>
    /// 导航属性：一个Order可以有多个request
    /// </summary>
    [Required]
    public  ICollection<EleOrderRequest> Requests { get; set; }

}

外键配置

modelBuilder.Entity<EleOrder>(entity =>
{
    // order与header的关系，一对一
    entity.HasOne(d => d.MessageHeader).WithOne(u => u.Order).HasForeignKey<EleOrderHeader>(u => u.OrderId);

    // order与request的关系，一对多
    entity.HasMany(d => d.Requests).WithOne(u => u.Order).HasForeignKey(r => r.OrderId).OnDelete(DeleteBehavior.Cascade);

});

modelBuilder.Entity<EleOrderRequest>(entity =>
{
    // 一个eleorderrequest可以有多个elorderrow
    entity.HasMany(e => e.Rows).WithOne(r => r.Request).HasForeignKey(x => x.RequestId).OnDelete(DeleteBehavior.Cascade);
});

modelBuilder.Entity<EleOrderRow>(entity =>
{
    // 一个row可以有多个rowitem
    entity.HasMany(d => d.RowItems).WithOne(u => u.Row).HasForeignKey(x => x.RowId).OnDelete(DeleteBehavior.Cascade);
});

LINQ查询关联表

var orders = context.EleOrder
			.Include(x => x.MessageHeader)
			.Include(x => x.Requests)
				.ThenInclude(x => x.Rows)
					.ThenInclude(x => x.RowItems)
			.ToList();

手动查询

var orders = context.EleOrder.ToList();
foreach (var order in orders)
{
    // System.Console.WriteLine("--------------------");
    // Console.WriteLine(order.Id);
    var header = context.EleOrderHeader.Where(x => x.OrderId == order.Id).First();
    // Console.WriteLine(header.UUID);
    order.MessageHeader = header;
    // System.Console.WriteLine(order.MessageHeader.UUID);
    var requests = context.EleOrderRequest.Where(x => x.OrderId == order.Id).ToList();
    // System.Console.WriteLine(requests.Count);
    foreach (var request in requests)
    {
        var rows = context.EleOrderRow.Where(x => x.RequestId == request.Id).ToList();
        foreach (var row in rows)
        {
            var rowItems = context.EleOrderRowItem.Where(x => x.RowId == row.Id).ToList();
            row.RowItems = rowItems;
        }
        request.Rows = rows;
    }
    order.Requests = requests;
    TestDbMapper(order);
    break;
}