过滤大于或等于LINQ表达式错误

Question

我正在我的。netcore项目中实现OData，我正在尝试应用一些过滤器。当我使用odata/productshops?$filter=SellingPrice eq 20时，我得到的产品售价为20。

但是当我试图过滤所有销售价格大于等于20的产品时:odata/productshops?$filter=SellingPrice ge 20

我得到一个内部服务器错误说:

The LINQ expression 'DbSet<ProductShop>
.Where(p => p.SellingPrice >= __TypedProperty_0)' could not be translated. 
Either rewrite the query in a form that can be translated, or switch 
to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync().

我虽然它必须做一些字段的类型，这是十进制，所以我试着写20.0,20M和20.0M，但仍然是相同的结果，我不能告诉为什么它给我这个错误。

如何正确地过滤大于或等于

?My Startup config:

var builder = new ODataConventionModelBuilder(app.ApplicationServices);
builder.EntitySet<ProductShopOData>("ProductShops");
app.UseMvc(routerBuilder =>
{
routerBuilder.Select().Expand().Filter().OrderBy().MaxTop(100).Count();
routerBuilder.MapODataServiceRoute("ODataRoute", "odata", builder.GetEdmModel());
});

控制器:

[ODataRoutePrefix("Productshops")]
[Authorize]
public class ProductShopsController : ODataController
{
private readonly MapperConfiguration _config = new MapperConfiguration(cfg =>
{
cfg.CreateMap<ProductShop, ProductShopOData>();
});
private readonly Context _context;
public ProductShopsController(Context context)
{
_context = context;
}
[EnableQuery]
[ODataRoute]
[HttpGet]
public IQueryable<ProductShopOData> GetProductShop()
{
return _context.ProductShops
.ProjectTo<ProductShopOData>(_config);
}
}

使用实体:

[Table("pro_product_shop_bind")]
public class ProductShop
{
[Column("id")]
[Key]
public int Id { get; set; }
[Column("status")]
[Required]
public RecordStatus Status { get; set; } = RecordStatus.Valid;

[Column("shop_id")]
[ForeignKey("Shop")]
[Required]
public int ShopId { get; set; }

[Column("product_id")]
[ForeignKey("Product")]
[Required]
public int ProductId { get; set; }

[Column("type_code")]
[Required]
public ProductShopBindType TypeCode { get; set; }

[Column("request_date")]
public DateTime? RequestDate { get; set; }

[Column("approval_date")]
public DateTime? ApprovalDate { get; set; }

[Column("request_status_code")]
[Required]
public RequestStatus RequestStatusCode { get; set; }
[Column("is_hidden")]
[Required]
public bool IsHidden { get; set; } = true;
[Column("is_automatic")]
[Required]
public bool IsAutomatic { get; set; } = true;

[Column("delivery_status_id")]
[ForeignKey("DeliveryStatus")]
public int? DeliveryStatusId { get; set; }

[Column("status_limit")]
public short? StatusLimit { get; set; }

[Column("below_limit_status_id")]
[ForeignKey("BelowLimitStatus")]
public int? BelowLimitStatusId { get; set; }

[Column("above_limit_status_id")]
[ForeignKey("AboveLimitStatus")]
public int? AboveLimitStatusId { get; set; }

[Column("zero_limit_status_id")]
[ForeignKey("ZeroLimitStatus")]
public int? ZeroLimitStatusId { get; set; }

[Column("label_id")]
[ForeignKey("Label")]
public int? LabelId { get; set; }

[Column("category_id")]
[ForeignKey("Category")]
public int? CategoryId { get; set; }

[Column("warranty_id")]
[ForeignKey("Warranty")]
public int? WarrantyId { get; set; }

[Column("selling_price", TypeName = "decimal(10,2)")]
[Required]
public decimal SellingPrice { get; set; }

[Column("other_price", TypeName = "decimal(10,2)")]
[Required]
public decimal OtherPrice { get; set; }
[Column("use_default_price")]
[Required]
public bool UseDefaultPrice { get; set; } = true;
[Column("price_changed")]
[Required]
public bool PriceChanged { get; set; } = false;

[Column("shipper_id")]
public int? ShipperId { get; set; }

[Column("vat_type")]
[Required]
public VatType VatType { get; set; }
[Column("must_be_stored")] 
[Required] 
public bool MustBeStored { get; set; } = true;
public virtual Shop Shop { get; set; }
public virtual Product Product { get; set; }
public virtual DeliveryTime DeliveryStatus { get; set; }
public virtual DeliveryTime BelowLimitStatus { get; set; }
public virtual DeliveryTime AboveLimitStatus { get; set; }
public virtual DeliveryTime ZeroLimitStatus { get; set; }
public virtual Label Label { get; set; }
public virtual Category Category { get; set; }
public virtual Warranty Warranty { get; set; }
}

EF Core版本为3.1.9.

更新:

在这里找到了某种"解决方案">，但是有一个解决方法是使用。tolist()，在那里获得了内存中的所有内容并将其转换为AsQueryable()。但这不是最优的，如何使它正确?

Answer 1

这可能是。net core版本的错误，在新的。net 5中一切正常。

Answer 2

这很可能是由于向数据库发送了错误的查询。我猜你是在计算SellingPrice

(我想把这篇文章作为评论，但我仍然没有足够的声誉-对不起!)