Linq - Dynamic query that contain DateTime filed

Question

I have search like as below picture:

I create query by below code:

StringBuilder query = new StringBuilder();

string OrderBy = null;
switch (sortOrder)
{
    case "orderCode_desc":
        OrderBy = " OrderCode desc";
        break;
    case "mobile":
        OrderBy = "Mobile";
        break;
    case "mobile_desc":
        OrderBy = "Mobile desc";
        break;
    case "date":
        OrderBy = "OrderDate";
        break;
    case "date_desc":
        OrderBy = "OrderDate desc";
        break;
    case "orderCode":
    default:
        OrderBy = "OrderCode";
        break;
}

if (filters.SelectedProducts.NotNullString())
{
    var SelectedProducts = filters.SelectedProducts.Split(',');
    query.Append("(");
    for (int i = 0; i < SelectedProducts.Length; i++)
    {
        query.AppendFormat("uag.oag.ordDet.ProductID == {0}" , SelectedProducts[i]);
        if (i != SelectedProducts.Length - 1)
        {
            query.Append(" || ");
        }
    }
    query.Append(") && ");
}


if (filters.DateFrom != null && filters.DateTo != null)
{
    query.AppendFormat("(uag.oag._order.order.OrderDate >= {0} && uag.oag._order.order.OrderDate <= {1} )", filters.DateFrom.Date, filters.DateTo.Date);
}
if (filters.DateFrom != null && filters.DateTo == null)
{
    query.AppendFormat("( uag.oag._order.order.OrderDate >= {0} )", filters.DateFrom.Date);
}
if (filters.DateFrom == null && filters.DateTo != null)
{
    query.AppendFormat("( uag.oag._order.order.OrderDate <= {0} )", filters.DateTo.Date);
}

string _query = query.ToString();
_query = _query.EndsWith("&& ") ? _query.Remove(_query.Length - 3) : _query;

orders = db.orders
        .Join(db.order_statuses, order => order.OrderStatusID, OrderStatus => OrderStatus.OrderStatusID, (order, OrderStatus) => new { order, OrderStatus })
        .Join(db.order_details, _order => _order.order.OrderID, ordDet => ordDet.OrderID, (_order, ordDet) => new { _order, ordDet })
        .Join(db.agents_info, oag => oag._order.order.AgentID, ag => ag.AgentInfoID, (oag, ag) => new { oag, ag })
        .Join(db.users, uag => uag.ag.UserID, u => u.UserID, (uag, u) => new { uag, u })
        .AsQueryable()
        .Where(_query)
        .Select(m => new OrderList
        {
            OrderID = m.uag.oag._order.order.OrderID,
            OrderCode = m.uag.oag._order.order.OrderCode,
            OrderDate = m.uag.oag._order.order.OrderDate,
            OrderStatus = m.uag.oag._order.OrderStatus.OrderStatusTitle,
            TotalPrice = m.uag.oag._order.order.TotalPrice,
            Mobile = m.u.Mobile
        }).OrderBy(OrderBy).Skip(PageSize * (page - 1)).Take(PageSize).ToList();

I get an error:

Operator '>=' incompatible with operand types 'DateTime' and 'Int32'

here is the same problem, and his problem was solved by use a parameterized query. I used parameterized query but problem does not solved. How can i solved this problem?

Answer 1

Per the documentation you need to use a public constructor to create a DateTime object, so something like

query.AppendFormat("(uag.oag._order.order.OrderDate >= {0} && uag.oag._order.order.OrderDate <= {1})",
    filters.DateFrom.Date.ToString(@"Da\teTi\me(yyyy,M,d)"),
    filters.DateTo.Date.ToString(@"Da\teTi\me(yyyy,M,d)"));