Odata c# dynamic query between dates

2019-09-21 10:12发布

问题:

I need to create a dynamic query for odata, http://xxx/odata/products?$filter=createDate+gt+2018-03-01T00:00:00%2B02:00+and+createDate+lt+2018-03-31T23:59:59%2B03:00

The query should filter all the products with the createDate on the current Month. Something like:

$filter=createDate+gt+MonthFirstDay(today)+and+createDate+lt+MonthLastDay(today).

Thank you very much

回答1:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.OData.Query;

namespace Business.BusinessLogic.Translators
{
    /// <summary>
    /// Convert odata query using keywords
    /// </summary>
    public class ODataQueryConverter : IODataQueryConverter
    {
        private readonly DateTime _serverNow = DateTime.UtcNow;
        private Dictionary<string, string> KeywordsDictionary { get; set; }

        /// <summary>
        /// constructor
        /// </summary>
        public ODataQueryConverter()
        {
            KeywordsDictionary = new Dictionary<string, string>
            {
                { "StartOfTheMonth", new DateTime(_serverNow.Year, _serverNow.Month, 1).ToString(Consts.IsoPattern)},
                { "EndOfTheMonth", new DateTime(_serverNow.Year, _serverNow.Month +1,1).AddSeconds(-1).ToString(Consts.IsoPattern)},
            };
        }

        /// <summary>
        /// Convert OData query option to new uri using const keywords
        /// </summary>
        /// <param name="odataQuery"></param>
        /// <returns></returns>
        public ODataQueryOptions Convert(ODataQueryOptions odataQuery)
        {
            if (odataQuery == null)
                return null;

            var odataRequestMessage = odataQuery.Request;
            var odataRequestUri = odataRequestMessage.RequestUri.ToString();
            var newRequestUriString = ConvertKeywords(odataRequestUri);

            odataRequestMessage.RequestUri = new Uri(newRequestUriString);
            var newOdataQueryOptions = new ODataQueryOptions(odataQuery.Context, odataRequestMessage);

            return newOdataQueryOptions;
        }

        public string Convert(string odataQueryString)
        {
            return ConvertKeywords(odataQueryString);
        }

        private string ConvertKeywords(string odataRequestUri)
        {
            return KeywordsDictionary.Aggregate(odataRequestUri, (current, keyword) => current.Replace(keyword.Key, keyword.Value));
        }
    }
}

//method in my BaseODataController used to query data 
//_converter is the instance of ODataQueryConverter that I got from depedency injection
public virtual IQueryable<T> Get(ODataQueryOptions<T> queryOptions)
{
   var convertedQuery = _converter.Convert(queryOptions);
   var queryable = BusinessController.Get();
   var convertedQueryable = convertedQuery.ApplyTo(queryable);
   return convertedQueryable;
}

Now every request from odata would go through the converter and look for the keywords we set in the converter dictionary. If the converter finds any it will replace it with the corresponding values.

Now I can use: http://api.dev.com/odata/entity?$filter=dateTime+gt+StartOfTheMonth

And it would automatically translated to: http://api.dev.com/odata/entity?$filter=dateTime+gt+2018-03-001T00:00:00.000Z

We created one source of truth for the keywords instead of letting each endpoint translate the keywords itself.

If anyone knows a way to do it with the current Odata version, please post it here.



标签: c# linq odata