(In theory) We have the following query:

var variableDate = DateTime.Parse("Nov 2, 2021")

var results = (from x in db.FooBar
               where x.Date == variableDate
               select x).ToList();

We can simply modify the variableDate to 11/3/21, 11/4/21, etc. to return results for different dates.

The question is can we use this same query to return all results after variableDate with some modification to the operator(s)/variable(s)? For example including the operator (> or ==) within variableDate (let's call it variableDateFormula):

> 11/2/2021

or

== 11/2/2021

This way we can simply call the same query with the same variable to return results for different operators:

var results = (from x in db.FooBar
               where x.Date variableDateFormula 
               select x).ToList();

I know the suggestion above will not work, it is just for visualization. I have a query that takes up 9 lines and has 8 different iterations depending on 3 values passed into the function. I would love to avoid writing basically the same query 8 times and taking up ~70 lines, and instead dynamically change the operators/criteria to have only 1 query.

edit:

public List<Job> GetActiveDocJobs(bool LimitQuantity, int JobQuantityLimit)
        {            
            PremEntities db = new PremEntities();

            var lessThan = (Func<int, int, bool>)((d1, d2) => d1 < d2);
            var greaterThan = (Func<int, int, bool>)((d1, d2) => d1 > d2);

            var quantityFunction = lessThan;
            if (!LimitQuantity)
            {
                JobQuantityLimit = 0;
                quantityFunction = greaterThan;
            }      


            List<Job> test = (from job in db.Jobs
                               where quantityFunction(job.Quantity, JobQuantityLimit)
                               orderby job.DueDate, job.Quantity
                               select job).ToList();
            return test;
        }

CodePudding user response：

Yes, the operator is just syntactic sugar for a function call and you can change that function, for example with a lambda:

var equalFunc = ((d1,d2) => d1 == d2);
var greaterFunc = ((d1,d2) => DateTime.Compare(d1, d2) > 0);

var actualFunc = greaterFunc; // Insert logic here to choose the appropriate function

Then your select becomes

from x in db.FooBar
where actualFunc(x.Date, variableDate)
select x

CodePudding user response：

Yes. The LINQ is just building an ExpressionTree that Entity Framework translates in to SQL, so you can use other expressions or lambdas as parameters that get inserted into the tree, like in this LINQpad example:

void Main()
{
    var aDate = new DateTime(2002, 1, 1);

    GetResults(d => d.TheDate == aDate).Dump();
    GetResults(d => d.TheDate <= aDate).Dump();
    GetResults(d => d.TheDate > aDate).Dump();
}
   
IEnumerable<Data> GetResults(Func<Data, bool> op)
{
    var data = new List<Data>
    {
        new Data{TheDate = new DateTime(2000,1,1)},
        new Data{TheDate = new DateTime(2001,1,1)},
        new Data{TheDate = new DateTime(2002,1,1)},
        new Data{TheDate = new DateTime(2003,1,1)},
    };
    
    return data.Where(d => op(d));
}

public class Data
{
    public DateTime TheDate { get; set; }
}

Which produces the following results:

If you are doing more complex logic in your expressions, the EF Database Provider may not be able to translate the expression to SQL though. There will be limitations. You couldn't for example, do this:

GetResults(d => d.TheDate.ToString().Reverse() == "1234");

because it wouldn't understand the custom Reverse extension method.

CodePudding user response：

I would suggest to use LINQKit for such task. EF Core cannot translate local variable as expression function.

var variableDate = DateTime.Parse("Nov 2, 2021");

Expression<Func<DateTime, DateTime, bool>> compareFunc = (d1, d2) => d1 > d2;

var results = (from x in db.FooBar
               where compareFunc.Invoke(x.Date, variableDate)
               select x).ToList();

For enabling LINQKit for EF Core add the following to the options:

builder
    .UseSqlServer(connectionString)
    .WithExpressionExpanding(); // enabling LINQKit extension

Or if you use other LINQ provider, add AsExpandable() at the top of the query:

var results = (from x in db.FooBar.AsExpandable()
               where compareFunc.Invoke(x.Date, variableDate)
               select x).ToList();