使用实体框架和 OData 查询的导航 DTO 属性

Question

开发环境

• ASP.NET 核心3.1
• Microsoft.实体框架核心 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 核心控制器

[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

如果我将自动映射器添加到组合中并尝试使用以下代码使用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

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

这是可能的，但您需要解决一些建模和实现特定的问题。

首先，建模。OData 仅支持实体类型的集合导航属性。因此，为了将ComputerDto.Disks属性映射为导航属性，您需要创建DiskDto实体类型。这反过来又要求它有一个密钥。因此，要么向其添加Id属性，要么将其他属性(例如，Letter)关联到它：

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

现在，Disks属性将不包括$expand选项，并且还将消除原始 OData 异常。

这都是关于OData Edm模型并为Disks启用$expand选项。

下一个要解决的问题与 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 查询转换管道，由Disks成员投影中的ToList()调用引起，这反过来又是必需的，因为目标属性类型为ICollection<DiskDto>，如果没有它，则会出现编译时错误。可以通过使属性类型IEnumerable<DiskDto>并从投影中删除ToList()来解决，这将消除异常，但再次会产生效率更低的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扩展，它：

从ODataQueryOptions创建 LINQ 表达式并执行查询。

你需要的是安装软件包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>)

并更改类似这样的控制器方法(本质上是不要使用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]