c# - 过滤大于或等于 LINQ 表达式错误的结果

问题描述

我正在我的 .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().

我虽然它必须对字段类型（十进制）做一些事情，所以我尝试了 write 20.0, 20Mand 20.0M，但仍然是相同的结果，我不知道为什么它会给我这个错误。

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

我的启动配置：

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()。但这不是最佳的，如何正确制作呢？

标签： c#asp.net-core.net-coreodata

c# - 过滤大于或等于 LINQ 表达式错误的结果

问题描述

解决方案

推荐阅读