在ASP.NET Core+EF中测试OData内置$filter集合函数遇问题求助

问题背景 #

在ASP.NET Core + Entity Framework环境测试OData的hassubset（hassubsequences存在相同问题）集合过滤函数时，执行以下查询：

http://localhost/comments?$filter=Id%20eq%20'1234'%20and%20hassubset(CommentWords%2C%20%5B'hello'%5D)

解码后：

$filter=Id eq '1234' and hassubset(CommentWords, ['hello'])

收到错误提示：

The query specified in the URI is not valid. An unknown function with name 'hassubset' was found. This may also be a function import or a key lookup on a navigation property, which is not allowed.

需求是探究OData集合函数，因此contains(CommentText, 'hello')这类替代方案不适用。

现有配置 #

实体类 #

public class CommentEntity
{
    public string Id { get; set; }
    public string CommentText { get; set; }

    public IQueryable<string> CommentWords => CommentText.Split(" ").AsQueryable();
}

控制器Action #

[EnableQuery]
[HttpGet("comments")]
public IQueryable<CommentEntity> GetComments()
{
    return _dbContext.Comments.AsQueryable();
}

Program.cs OData配置 #

builder.Services
    .AddControllers()
    .AddOData(opt => 
    {
        var builder = new ODataConventionModelBuilder();
        builder.EntitySet<CommentEntity>(nameof(CommentEntity));
        
        opt
            .EnableQueryFeatures()
            .AddRouteComponents("service/odata", builder.GetEdmModel());
    });

OData元数据片段 #

<EntityType Name="Comment">
    <Key>
        <PropertyRef Name="Id" />
    </Key>
    <Property Name="Id" Type="Edm.String" Nullable="false" />
    <Property Name="CommentTest" Type="Edm.String" />
    <Property Name="CommentWords" Type="Collection(Edm.String)" />
</EntityType>

解决步骤 #

1. 显式启用集合函数支持 #

ASP.NET Core OData默认不开启所有集合函数，需要在配置中指定允许使用hassubset/hassubsequences。修改Program.cs的OData配置：

builder.Services
    .AddControllers()
    .AddOData(opt => 
    {
        var builder = new ODataConventionModelBuilder();
        builder.EntitySet<CommentEntity>(nameof(CommentEntity));
        
        opt
            .EnableQueryFeatures()
            // 启用所有集合函数
            .Filter().AllowedFunctions(ODataAllowedFunctions.AllCollectionFunctions)
            // 若仅需特定函数，可替换为：
            // .Filter().AllowedFunctions(ODataAllowedFunctions.HasSubset | ODataAllowedFunctions.HasSubsequence)
            .AddRouteComponents("service/odata", builder.GetEdmModel());
    });

2. 修正实体类的集合属性类型 #

当前CommentWords使用IQueryable<string>，EF Core无法将Split操作转换为SQL，且OData对集合属性的处理更适配IEnumerable<string>类型。同时添加[NotMapped]避免EF尝试映射到数据库字段：

using System.ComponentModel.DataAnnotations.Schema;

public class CommentEntity
{
    public string Id { get; set; }
    public string CommentText { get; set; }

    [NotMapped]
    public IEnumerable<string> CommentWords => CommentText?.Split(" ", StringSplitOptions.RemoveEmptyEntries) ?? Enumerable.Empty<string>();
}

3. 修正查询路由匹配 #

OData配置的路由前缀是service/odata，控制器的comments路由需要拼接前缀，正确的查询URL应为：

http://localhost/service/odata/comments?$filter=Id eq '1234' and hassubset(CommentWords, ['hello'])

若不需要前缀，可移除AddRouteComponents中的"service/odata"参数。

4. 验证元数据与查询 #

重启服务后，访问/service/odata/$metadata确认CommentWords仍为Collection(Edm.String)，再执行上述查询即可生效。

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