c# - 使用 "Linq Methods"处理 DateTime

Question

[ASP.NET 4.0/EF 4.1]

你好，

我正在尝试使用“Linq 方法”根据日期时间字段过滤数据源，但出现错误:“此方法仅支持基本类型(‘例如 Int32、String 和 Guid’)上下文”。

我知道 Entity Framework 在处理日期时有一些限制，但我需要的是一些优雅的解决方案来处理这个问题。

我的代码是:

    public IList<Order> GetOrders(int? orderId = null, string customerId = null, int? employeeId = null, DateTime? orderDateFrom = null, DateTime? orderDateUntil=null, DateTime? requiredDate = null, DateTime? shippedDate = null)
    {
        IQueryable<Order> result;

        result = from order in ctx.Orders.Include("Order_Details")
                 select order;

        // Apply filters to the base query
        if (orderId != null)
            result = result.Where(o => o.OrderID.Equals(orderId));

        if (!String.IsNullOrEmpty(customerId))
            result = result.Where(o => o.CustomerID.ToUpper().Equals(customerId.ToUpper()));

        if (employeeId != null)
            result = result.Where(o => o.EmployeeID.Equals(employeeId));

        if (orderDateFrom != null)
            result = result.Where(o => o.OrderDate >= orderDateFrom);

        if (orderDateUntil != null)
            result = result.Where(o => o.OrderDate <= orderDateUntil);

        if (requiredDate != null)
            result = result.Where(o => o.RequiredDate == requiredDate);

        if (shippedDate != null)
            result = result.Where(o => o.ShippedDate == shippedDate);

        return result.ToList();
    }

当代码执行查询 (result.ToList()) 时，它会抛出异常。如果我删除 datetime .Where 子句，它工作正常。

谢谢!

解决方案

我已将我的代码更改为:

    public IList<Order> GetOrders(int? orderId = null, string customerId = null, int? employeeId = null, DateTime? orderDateFrom = null, DateTime? orderDateUntil=null, DateTime? requiredDate = null, DateTime? shippedDate = null)
    {
        IQueryable<Order> result;

        result = from order in ctx.Orders.Include("Order_Details")
                 select order;

        // Apply filters to the base query
        if (orderId != null)
            result = result.Where(o => o.OrderID.Equals(orderId.Value));

        if (!String.IsNullOrEmpty(customerId))
            result = result.Where(o => o.CustomerID.ToUpper() == customerId.ToUpper());

        if (employeeId != null)
            result = result.Where(o => o.EmployeeID == employeeId.Value);

        if (orderDateFrom != null)
            result = result.Where(o => o.OrderDate >= orderDateFrom.Value);

        if (orderDateUntil != null)
            result = result.Where(o => o.OrderDate <= orderDateUntil.Value);

        if (requiredDate != null)
            result = result.Where(o => o.RequiredDate == requiredDate.Value);

        if (shippedDate != null)
            result = result.Where(o => o.ShippedDate == shippedDate.Value);

        return result.ToList();
    }

如果有人有更好的解决方案，请告诉我。

Answer 1

您使用的是可空类型 DateTime？在你的例子中。我不确定您的数据库列是否允许 NULL，但我会使用 date.Value 传递参数值以确保 EF 不会超过它。请注意，对于可空类型，您还可以使用 HasValue 属性来检查您的参数是否包含正确的值。这导致:

if (orderDateFrom.HasValue)
   result = result.Where(o => o.OrderDate >= orderDateFrom.Value);