0

I have a little odd question working by lifting an very old system and replacing 10year old nHibernate with EF. I have trouble formulating some linq extensions for a search page. The thing I want to refactor is this(this code rows is used many times or with a variant for other types) :

            if (searchCriteria.PlannedEndDateFrom.HasValue && searchCriteria.PlannedEndDateTo.HasValue)
            {
                efquerable= efquerable.Where(d => searchCriteria.PlannedEndDateFrom < d.PlannedEndDate && d.PlannedEndDate > searchCriteria.PlannedEndDateTo);

            }
            else if (searchCriteria.PlannedEndDateFrom.HasValue)
            {
                efquerable= efquerable.Where(d => d.PlannedEndDate > searchCriteria.PlannedEndDateFrom);
            }
            else if (searchCriteria.PlannedEndDateTo.HasValue)
            {
                efquerable= efquerable.Where(d => d.PlannedEndDate < searchCriteria.PlannedEndDateTo);

            }

This should be able to move to an extensions where I do this kind of checks instead...

       private static IQueryable<T> FilterOnCorrectSearchDateTime<T>(this IQueryable<T> efquerable, Expression<Func<T, DateTime>> datePropertyName, DateTime? dateFrom, DateTime? dateTo)
       {
//Missing code
}

And then be called like

EFquerable = EFquerable.FilterOnCorrectSearchDateTime<EntityObjectWithDatetimeproperty>(s=>s.PlannedEndDate ,searchCriteria.PlannedEndDateFrom,searchCriteria.PlannedEndDateTo);

Any suggestions for how to solve this? What I get stuck on is to handle the checks on the properties that is sent into the function. How do I formulate the Expression<Func<T, DateTime>> datePropertyName to a usable statement for the Where function ? Is there something else I have missed in thinking in this refactoring? I have tought on using System.Linq.Dynamics but that seems less good...

Jerker Pihl
  • 125
  • 1
  • 13

1 Answers1

1

You could implement IQueryable extension method like this:

    public static IQueryable<T> FilterOnCorrectSearchDateTime<T>(this IQueryable<T> efquerable, Func<T,DateTime> datePropertyValue, DateTime? dateFrom, DateTime? dateTo)
    {
        if (dateFrom.HasValue && dateTo.HasValue)
        {
            return efquerable.Where(d => dateFrom.Value < datePropertyValue(d)
                                    && datePropertyValue(d) < dateTo.Value);
        }
        else if (dateFrom.HasValue)
        {
            return efquerable.Where(d => datePropertyValue(d) > dateFrom);
        }
        else if (dateTo.HasValue)
        {
            return efquerable.Where(d => datePropertyValue(d) < dateTo);
        }

        return efquerable;
    }

And then use it:

var results =  
iQueryable.FilterOnCorrectSearchDateTime( 
      (entityObject)=> entityObject.YourDateTimeField,
      dateFrom,
      dateTo);

Update:

There r LINQKIT

package which might help you. But implementing whole thing as dynamic SQL statement, which is what you actually have on your hands, would be, IMHO, a better solution overall.

fatherOfWine
  • 1,191
  • 16
  • 39
  • Sadly this solution didnt work. EF complains that return efquerable.Where(d => dateFrom.Value < datePropertyValue(d) && datePropertyValue(d) < dateTo.Value); gives System.NotSupportedException: 'The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.' – Jerker Pihl Oct 27 '21 at 15:37
  • Well, it would have worked if it has been applied to the Iquerable result set AFTER EF would executed "main" query, so to speak. After all EF should generate SQL in the end of the tunnel and having task to translate all that logic into SQL EF really has no interest apparently. Bu no fear, please, take a look on my update it might help. – fatherOfWine Nov 02 '21 at 15:33