c# - 使用 Entity Framework 和 OData 查询的导航 DTO 属性

Question

开发环境

ASP.NET 核心 3.1

Microsoft.EntityFrameworkCore 3.1.9

Microsoft.AspNetCore.OData 7.5.1

楷模

public class Computer
{
    public int Id { get; set; }
    public string Name { get; set; }
    
    public ICollection<Disk> Disks { get; set; }
}

public class Disk
{
    public int Id { get; set; }
    public string Letter { get; set; }
    public float Capacity { get; set; }
    
    public int? ComputerId { get; set; }
    public virtual Computer Computer { get; set; }
}

托斯

public class ComputerDto
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<DiskDto> Disks { get; set; }
}

public class DiskDto
{
    public string Letter { get; set; }
    public float Capacity { get; set; }
}

EF 核心上下文

public class ComputerContext : DbContext
{
    public DbSet<Computer> Computers { get; set; }
    public DbSet<Disk> Disks { get; set;}
    
    public ComputerContext(DbContextOptions<ComputerContext> options)
        : base(options)
    {
        
    }   
}

OData EDM 模型

private static IEdmModel GetEdmModel()
{
    var builder = new ODataConventionModelBuilder();
    
    builder.EntitySet<Computer>("Computers");   
    builder.EntitySet<Disk>("Disks");
        
    builder.ComplexType<ComputerDto>();
    builder.ComplexType<DiskDto>();

    return builder.GetEdmModel();
}

ASP.NET 核心 Controller

[Route("api/[controller]")]
[ApiController]
public class ComputersController : ControllerBase
{
    private readonly ComputerContext context;
    
    public ComputersController(ComputerContext context)
    {
        this.context = context;
    }
    
    [HttpGet]
    [EnableQuery]
    public IQueryable<ComputerDto> GetComputers()
    {
        return this.context.Computers.Select(c => new ComputerDto
        {
            Id = c.Id,
            Name = c.Name,
            Disks = c.Disks.Select(d => new DiskDto
            {
                Letter = d.Letter,
                Capacity = d.Capacity
            }).ToList()
        });
    }
}

此查询有效，但磁盘已扩展，因为我正在手动创建列表。

https://localhost:46324/api/computers?$filter=startswith(name,'t')

和输出

{
  "@odata.context": "https://localhost:46324/api/$metadata#Collection(ODataPlayground.Dtos.ComputerDto)",
  "value": [
    {
      "Id": 14,
      "Name": "TestComputer1",
      "Disks": [
        {
          "Letter": "C",
          "Capacity": 234.40
        },
        {
          "Letter": "D",
          "Capacity": 1845.30
        }
      ]
    },
    {
      "Id": 15,
      "Name": "TestComputer2",
      "Disks": [
        {
          "Letter": "C",
          "Capacity": 75.50
        },
        {
          "Letter": "D",
          "Capacity": 499.87
        }
      ]
    }
  ]
}

如果我然后尝试使用以下查询扩展“磁盘”，则会收到错误消息:

https://localhost:46324/api/computers?$filter=startswith(name,'t')&$expand=disks

错误

{
    "error": {
        "code": "",
        "message": "The query specified in the URI is not valid. Property 'disks' on type 'ODataPlayground.Dtos.ComputerDto' is not a navigation property or complex property. Only navigation properties can be expanded.",
        "details": [],
        "innererror": {
            "message": "Property 'disks' on type 'ODataPlayground.Dtos.ComputerDto' is not a navigation property or complex property. Only navigation properties can be expanded.",
            "type": "Microsoft.OData.ODataException",
            "stacktrace": "...really long stack trace removed for compactness..."
        }
    }
}

题

我似乎能够将顶级类作为 dto 返回，只公开客户端可能需要的属性，但是否也可以公开并返回 dto 作为导航属性？

非dto输出

{
  "@odata.context": "https://localhost:46324/api/$metadata#Collection(ODataPlayground.Dtos.ComputerDto)",
  "value": [
    {
      "Id": 14,
      "Name": "TestComputer1",
      "Disks": [
        {
          "Id": 16,
          "ComputerId": 14,
          "Letter": "C",
          "Capacity": 234.40
        },
        {
          "Id": 17,
          "ComputerId": 14,
          "Letter": "D",
          "Capacity": 1845.30
        }
      ]
    }
  ]
}

所需的输出(使用上面的 $filter 和 $expand 查询)

{
  "@odata.context": "https://localhost:46324/api/$metadata#Collection(ODataPlayground.Dtos.ComputerDto)",
  "value": [
    {
      "Id": 14,
      "Name": "TestComputer1",
      "Disks": [
        {
          "Letter": "C",
          "Capacity": 234.40
        },
        {
          "Letter": "D",
          "Capacity": 1845.30
        }
      ]
    }
  ]
}

更新 #1
如果我将 Automapper 添加到组合中并尝试使用 ProjectTo使用以下代码的方法:

    //// Inject context and mapper
    public ComputersController(ComputerContext context, IMapper mapper)
    {
        this.context = context;
        this.mapper = mapper;
    }

    [HttpGet]
    [EnableQuery]
    public IQueryable<ComputerDto> GetComputers()
    {
        return this.context.Computers.ProjectTo<ComputerDto>(mapper.ConfigurationProvider);
    }

我得到一个不同的错误:

    InvalidOperationException: When called from 'VisitLambda', rewriting a node of type
    'System.Linq.Expressions.ParameterExpression' must return a non - null value of the same type.
    Alternatively, override 'VisitLambda' and change it to not visit children of this type.

Answer 1

I seem to be able to return the top level class as a dto, only exposing the properties a client might need but is it also possible to expose and return a dto as a navigation property?

这是可能的，但您需要解决一些特定于建模和实现的问题。
第一，建模。 OData 仅支持实体类型的集合导航属性。所以为了图 ComputerDto.Disks属性作为导航属性，您需要制作 DiskDto实体类型。这反过来又要求它有一把 key 。所以要么添加 Id属性，或者将一些其他属性(例如， Letter )关联到它:

//builder.ComplexType<DiskDto>();
builder.EntityType<DiskDto>().HasKey(e => e.Letter);

现在 Disks属性(property)将不包括在内 $expand选项，也将消除原始 OData 异常。
这完全是关于 OData Edm 模型和启用 $expand Disks 的选项.
下一个要解决的问题与 OData 和 EF Core 查询实现细节有关。运行过滤查询(不带 $expand )会产生所需的 JSON 输出(不包括 Disks)，但生成的 EF Core SQL 查询是

SELECT [c].[Id], [c].[Name], [d].[Letter], [d].[Capacity], [d].[Id]
FROM [Computers] AS [c]
LEFT JOIN [Disks] AS [d] ON [c].[Id] = [d].[ComputerId]
WHERE (@__TypedProperty_0 = N'') OR ([c].[Name] IS NOT NULL AND (LEFT([c].[Name], LEN(@__TypedProperty_0)) = @__TypedProperty_0))
ORDER BY [c].[Id], [d].[Id]

如您所见，它包括不必要的连接和列，这是低效的。
并与 $expand选项，您可以获得 VisitLambda异常，来自 EF Core 3.1 查询转换管道，由 ToList() 引起调用 Disks成员投影，这反过来又是必需的，因为目标属性类型是 ICollection<DiskDto>没有它，你会得到编译时错误。可以通过设置属性类型 IEnumerable<DiskDto>来解决。并删除 ToList() from 投影，这将消除异常，但同样会产生效率更低的 SQL 查询

SELECT [c].[Id], [c].[Name], [d].[Letter], [d].[Capacity], [d].[Id], @__TypedProperty_2, [d0].[Letter], [d0].[Capacity], CAST(1 AS bit), [d0].[Id]
FROM [Computers] AS [c]
LEFT JOIN [Disks] AS [d] ON [c].[Id] = [d].[ComputerId]
LEFT JOIN [Disks] AS [d0] ON [c].[Id] = [d0].[ComputerId]
WHERE (@__TypedProperty_0 = N'') OR ([c].[Name] IS NOT NULL AND (LEFT([c].[Name], LEN(@__TypedProperty_0)) = @__TypedProperty_0))
ORDER BY [c].[Id], [d].[Id], [d0].[Id]

所有这一切都意味着尝试直接通过 EF Core 投影查询使用 OData 查询是有问题的。
因此，作为实现问题的解决方案，我建议 AutoMapper.Extensions.OData扩展名:

Creates LINQ expressions from ODataQueryOptions and executes the query.

你需要的是安装包 AutoMapper.AspNetCore.OData.EFCore ，使用类似这样的AutoMapper配置(关键是开启空集合和显式扩展)

cfg.AllowNullCollections = true;
cfg.CreateMap<Computer, ComputerDto>()
    .ForAllMembers(opt => opt.ExplicitExpansion());
cfg.CreateMap<Disk, DiskDto>()
    .ForAllMembers(opt => opt.ExplicitExpansion());

(注意:使用这种方法，属性类型可以保留 ICollection<DiskDto> )
并更改与此类似的 Controller 方法(关键是不要使用 EnableQuery ，添加选项参数并返回 IEnumerable/ ICollection 而不是 IQueryable )

using AutoMapper.AspNet.OData;

[HttpGet]
public async Task<IEnumerable<ComputerDto>> GetComputers(
    ODataQueryOptions<ComputerDto> options) =>
    await context.Computers.GetAsync(mapper, options, HandleNullPropagationOption.False);

现在两个输出都将如预期的那样，以及生成的 SQL 查询:

(无扩展)

输出:

{
    "@odata.context": "https://localhost:5001/api/$metadata#Collection(ODataTest.Dtos.ComputerDto)",
    "value": [
        {
            "Id": 1,
            "Name": "TestComputer1"
        },
        {
            "Id": 2,
            "Name": "TestComputer2"
        }
    ]
}

SQL查询:

SELECT [c].[Id], [c].[Name]
FROM [Computers] AS [c]
WHERE [c].[Name] IS NOT NULL AND ([c].[Name] LIKE N't%')

与 $expand=disks

输出:

{
    "@odata.context": "https://localhost:5001/api/$metadata#Collection(ODataTest.Dtos.ComputerDto)",
    "value": [
        {
            "Id": 1,
            "Name": "TestComputer1",
            "Disks": [
                {
                    "Letter": "C",
                    "Capacity": 234.4
                },
                {
                    "Letter": "D",
                    "Capacity": 1845.3
                }
            ]
        },
        {
            "Id": 2,
            "Name": "TestComputer2",
            "Disks": [
                {
                    "Letter": "C",
                    "Capacity": 75.5
                },
                {
                    "Letter": "D",
                    "Capacity": 499.87
                }
            ]
        }
    ]
}

SQL查询:

SELECT [c].[Id], [c].[Name], [d].[Id], [d].[Capacity], [d].[ComputerId], [d].[Letter]
FROM [Computers] AS [c]
LEFT JOIN [Disks] AS [d] ON [c].[Id] = [d].[ComputerId]
WHERE [c].[Name] IS NOT NULL AND ([c].[Name] LIKE N't%')
ORDER BY [c].[Id], [d].[Id]