Using DateTime for comparisons in dynamic LINQ with other type parameters in predicate

Question

My requirement is simple and very common. I did find many struggling to find answers to it but did not get any acceptable solution.

I am using EF 6.1 and want to query a table with a predicate which is being created dynamically something like "ABC > '1' && DEF = '2' && GHI = 3.." and so on. The issue is, I am also having DateTime as a data type. I tried searching for appropriate solutions, but provided solutions (mentioned below) doesn't work (ABC, DEF are name of the columns which is of DateTime type in DB).

repository.Where("ABC > DateTime.Parse(...
repository.Where("ABC > @1 && DEF < @2....", listDateTime.ToArray())

Thoughts?

Thanks.

UPDATE 1: Just to be clear, I do not know the count of DateTime parameters at compile time, I am using List (listDateTime) as shown in example #2 above.

UPDATE 2: listDateTime is an object of List<DateTime> which stores values of datetime parameters at run-time.

UPDATE 3: Question asked here inlines to what has been asked here. The only change is, I do not know the count of an array of DateTime parameters (@0, @1...) at compile time.

I'm not entirely clear on what specifically you're trying to achieve here. Can you provide a more complete example? To include sample values in your list and a sample `.Where()` predicate you'd like to achieve? — David, Dec 12 '17 at 13:48
you'll have to provide together with your datetimelist also the operator used and the column. That also means you can just as well pass a List of Expression>. Using this in Linq's .Where() is very easy. — DevilSuichiro, Dec 12 '17 at 13:52
Dumisani, David, DevilSuichiro: Please see updates to my question. I hope my requirement should be more clear to you now. — Deepak Agarwal, Dec 12 '17 at 14:01
How do you know wich Column of the Entity must be compared with a DateTime value of your List? Your column names are abc, def, ghi, etc? — aperezfals, Dec 12 '17 at 14:07
@AlejandroPérezFals Correct. I am constructing the predicate at run-time something like string.Format("{0} {1}", columnName, operator). — Deepak Agarwal, Dec 12 '17 at 14:44

score 0 · Answer 1 · answered Dec 12 '17 at 14:18

I think, it is not proper way to perform it. It could be better to use PredicateBuilder instead of this. But for this case maybe you can try something like that;

        //Returns "ABC > DateTime(2016,1,1,0,0,0) && ABC < DateTime(2018,1,1,0,0,0)"
        public static string BuildDateTimeListFilter(string filter,List<DateTime> dateTimeFilterList)
        {
            var filterDateTimeByString = dateTimeFilterList.Select(x => string.Format("DateTime({0},{1},{2},{3},{4},{5})", x.Year, x.Month, x.Day, x.Hour, x.Minute, x.Second)).ToArray();
            for (int i = 0; i < filterDateTimeByString.Length; i++)
            {
                filter = filter.Replace("@" + i, filterDateTimeByString[i]);
            }
            return filter;
        }

Usage;

        var filterDateTimeByString = new List<DateTime>
        {
           new DateTime(2016,01,01),
           new DateTime(2018,01,01)
        };
        var records = repository.Where(BuildDateTimeListFilter("ABC > @0 && ABC < @1",filterDateTimeByString)).ToList();

Returned filter string equilavent to ;

repository.Where("ABC > DateTime(2016,1,1,0,0,0) && ABC < DateTime(2018,1,1,0,0,0)").ToList();

If you are replacing the parameters manually, why do you need it in the first place? I can achieve the same output using string.Format() which is the source of my question; it doesn't work. See example #1. Did you try running your code? Maybe I am missing something. — Deepak Agarwal, Dec 12 '17 at 14:54

Using DateTime for comparisons in dynamic LINQ with other type parameters in predicate

1 Answers1