日期之间的Odata C#动态查询 [英] Odata c# dynamic query between dates

问题描述

我需要为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

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

查询应在当前月份过滤带有createDate的所有产品. 像这样:

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

$ filter = createDate + gt + MonthFirstDay(今天)+和+ createDate + lt + MonthLastDay(今天).

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

非常感谢您

推荐答案

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;
}

现在，来自odata的每个请求都将通过转换器，并寻找我们在转换器字典中设置的关键字. 如果转换器发现任何问题，它将用相应的值替换它.

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.

现在我可以使用: http://api.dev.com/odata/entity ?$ filter = dateTime + gt + StartOfTheMonth

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

它会自动翻译为: http://api.dev.com/odata/entity ?$ filter = dateTime + gt + 2018-03-001T00:00:00.000Z

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.

如果有人知道使用当前Odata版本执行此操作的方法，请在此处发布.

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

这篇关于日期之间的Odata C#动态查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！