C# Code First：将Order与Tracking的DB一对多转为DTO一对一并实现OData过滤

1. 实体与DTO定义（基础结构） #

先明确核心实体和DTO的结构，确保映射关系清晰：

数据库实体 #

public class Order
{
    public int Id { get; set; }
    public string OrderNumber { get; set; }
    public DateTime CreatedTime { get; set; }
    public string OrderType { get; set; } // 线上/电话
    public ICollection<Tracking> Trackings { get; set; } = new List<Tracking>();
}

public class Tracking
{
    public int Id { get; set; }
    public int? OrderId { get; set; }
    public Order? Order { get; set; }
    public string Status { get; set; } // 准备中/派送中/已签收等
    public DateTime CreatedTime { get; set; } // 跟踪记录生成时间（用于取最新）
    public string NodeInfo { get; set; } // 快递节点详情
}

DTO结构 #

public class OrderDataDto
{
    public int Id { get; set; }
    public string OrderNumber { get; set; }
    public DateTime CreatedTime { get; set; }
    public string OrderType { get; set; }
    public RecentTrackingDto? RecentTracking { get; set; }
}

public class RecentTrackingDto
{
    public string Status { get; set; }
    public DateTime CreatedTime { get; set; }
    public string NodeInfo { get; set; }
}

2. 数据库层面关联最新跟踪记录 #

避免内存中筛选跟踪记录（性能差且OData无法识别），直接通过EF Core生成SQL在数据库层面获取每个订单的最新跟踪：

// 先分组获取每个OrderId对应的最新跟踪记录
var latestTrackings = _dbContext.Trackings
    .GroupBy(t => t.OrderId)
    .Select(g => new
    {
        OrderId = g.Key,
        LatestTracking = g.OrderByDescending(t => t.CreatedTime).FirstOrDefault()
    });

// 左关联订单与最新跟踪记录（支持无跟踪记录的订单返回null）
var ordersQuery = _dbContext.Orders
    .GroupJoin(latestTrackings,
        o => o.Id,
        lt => lt.OrderId,
        (o, ltGroup) => new { Order = o, LatestTrackings = ltGroup })
    .SelectMany(x => x.LatestTrackings.DefaultIfEmpty(),
        (x, lt) => new OrderDataDto
        {
            Id = x.Order.Id,
            OrderNumber = x.Order.OrderNumber,
            CreatedTime = x.Order.CreatedTime,
            OrderType = x.Order.OrderType,
            RecentTracking = lt?.LatestTracking != null ? new RecentTrackingDto
            {
                Status = lt.LatestTracking.Status,
                CreatedTime = lt.LatestTracking.CreatedTime,
                NodeInfo = lt.LatestTracking.NodeInfo
            } : null
        });

3. 配置OData支持嵌套DTO过滤 #

OData需要明确识别DTO的嵌套属性，才能支持RecentTracking/Status这类过滤条件，在Program.cs中配置Edm模型：

builder.Services.AddControllers().AddOData(options =>
{
    // 启用OData核心功能：筛选、排序、分页等
    options.Select().Filter().OrderBy().Expand().Count().SetMaxTop(100);
    options.AddRouteComponents("odata", GetODataEdmModel());
});

// 构建OData实体模型
private static IEdmModel GetODataEdmModel()
{
    var modelBuilder = new ODataConventionModelBuilder();
    
    // 注册主DTO集合
    var orderEntitySet = modelBuilder.EntitySet<OrderDataDto>("Orders");
    
    // 显式注册嵌套DTO的属性，让OData识别可过滤字段
    var trackingDtoType = modelBuilder.EntityType<RecentTrackingDto>();
    trackingDtoType.Property(t => t.Status);
    trackingDtoType.Property(t => t.CreatedTime);
    
    return modelBuilder.GetEdmModel();
}

4. API控制器实现 #

返回IQueryable<OrderDataDto>并启用OData特性，让过滤条件直接下推到数据库：

[ApiController]
[Route("odata/[controller]")]
public class OrdersController : ControllerBase
{
    private readonly AppDbContext _dbContext;

    public OrdersController(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }

    [HttpGet]
    [EnableQuery] // 启用OData查询支持
    public IQueryable<OrderDataDto> GetOrders()
    {
        var latestTrackings = _dbContext.Trackings
            .GroupBy(t => t.OrderId)
            .Select(g => new
            {
                OrderId = g.Key,
                LatestTracking = g.OrderByDescending(t => t.CreatedTime).FirstOrDefault()
            });

        return _dbContext.Orders
            .GroupJoin(latestTrackings,
                o => o.Id,
                lt => lt.OrderId,
                (o, ltGroup) => new { Order = o, LatestTrackings = ltGroup })
            .SelectMany(x => x.LatestTrackings.DefaultIfEmpty(),
                (x, lt) => new OrderDataDto
                {
                    Id = x.Order.Id,
                    OrderNumber = x.Order.OrderNumber,
                    CreatedTime = x.Order.CreatedTime,
                    OrderType = x.Order.OrderType,
                    RecentTracking = lt?.LatestTracking != null ? new RecentTrackingDto
                    {
                        Status = lt.LatestTracking.Status,
                        CreatedTime = lt.LatestTracking.CreatedTime,
                        NodeInfo = lt.LatestTracking.NodeInfo
                    } : null
                });
    }
}

5. 测试OData过滤示例 #

现在可以直接使用OData语法进行筛选：

• 筛选最新跟踪状态为「派送中」的订单：

  GET /odata/Orders?$filter=RecentTracking/Status eq '派送中'
  

• 筛选最新跟踪记录在指定时间之后的订单：

  GET /odata/Orders?$filter=RecentTracking/CreatedTime ge 2024-05-01T00:00:00Z
  

• 筛选无跟踪记录的订单：

  GET /odata/Orders?$filter=RecentTracking eq null
  

6. 关键注意事项 #

• 必须返回IQueryable类型，OData才能将过滤条件转换为SQL执行，避免内存中全表扫描。
• 若遇到OData无法识别嵌套属性的问题，检查Edm模型是否显式注册了RecentTrackingDto的所有需要过滤的属性。
• 处理RecentTracking为null的场景时，过滤条件需要先判断非空，例如RecentTracking ne null and RecentTracking/Status eq '已签收'。

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