如何在Web-Api控制器中过滤odata $ expand查询

I have got two models. A Product model:

我有两个型号。产品型号:

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }

    public virtual ICollection<Categories> Categories { get; set; }
}

And a Categories model:

和类别模型:

public class Categories
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsPublic { get; set; }

    public virtual Product Products { get; set; }
}

As you can see there is a relation between the models. One Product can have multiple categories. I'm able to query the model with http://localhost/Products?$expand=Categories.

如您所见,模型之间存在关联。一个产品可以有多个类别。我可以使用http:// localhost / Products?$ expand = Categories查询模型。

However I want to filter the categories before they are returned. Main goal is to avoid that the api consumer can query categories where theIsPublic is set to false. How can I archive this inside the Web API controller?

但是我想在返回之前过滤类别。主要目标是避免api使用者可以查询IsPublic被设置为false的类别。如何在Web API控制器中存档?

My first attemp is not working because I need a IQueryable<Product> and it returns a IQueryable<Categories>:

我的第一次尝试不起作用,因为我需要一个IQueryable ,它返回一个IQueryable :

        [HttpGet]
        [EnableQuery]
        public IQueryable<Product> Get(ODataQueryOptions<Product> odataQueryOptions)
        {
            if (odataQueryOptions.SelectExpand.RawExpand == "Categories")
            {
                var p = db.Products.First();
                var result = db.Entry(p).Collection(x => x.Categories).Query().Where(x => x.IsPublic == true).AsQueryable();
                return result;
            }
            else
            {
                return db.Products;
            }
        }

Basically my question is: What is the correct way to write a OData-like $expand query in LINQ? If this is not possible, how else can I filter on en expanded navigation property?

基本上我的问题是:在LINQ中编写类似OData的$ expand查询的正确方法是什么?如果这不可能,我还可以如何过滤扩展的导航属性?

1 个解决方案

#1

EF doesn't allow to filter included properties. (This was a highly voted feature request for EF but never implemented: Allow filtering for Include extension method)

EF不允许过滤包含的属性。 (这是一个高度投票的功能请求,但从未实现:允许过滤包含扩展方法)

So, EF doesn't support it. So I can only offer you these two hacks:

所以,EF不支持它。所以我只能为你提供这两个黑客:

create a filtered view in the database, and map it to a different entity in EF. This is efficient, because the filtering will happen in the server.

在数据库中创建筛选视图,并将其映射到EF中的其他实体。这很有效,因为过滤将在服务器中进行。

in the controller's code, project the query to a new class, with the related collection filtered (by using Select(x=> new ...)), and make this projected result IQueryable with .AsQueryable extension method. In this way you'll returned a new queryable with related entities filtered as you wanted. This is more inefficient: it requires to recover the whole related collection from the DB server, filter it in the controller's method, and convert it to queryable

在控制器的代码中,将查询投影到一个新类,过滤相关集合(通过使用Select(x => new ...)),并使用.AsQueryable扩展方法使该投影结果IQueryable。通过这种方式,您将返回一个新的可查询对象,并根据需要过滤相关实体。这样效率更低:它需要从数据库服务器恢复整个相关集合,在控制器的方法中对其进行过滤,并将其转换为可查询的

Obviously the first option is the "best hack". I think that unfortunately there is not a better solution. Perhaps some other hacks, with TVFs on the server or something like that.

显然,第一个选择是“最好的黑客”。我认为不幸的是没有更好的解决方案。也许其他一些黑客,服务器上的TVF或类似的东西。

#1