0

I have a simple LINQ query here but I get an exception

function can only be invoked from LINQ to entities

Not to sure what it could be, I know it has to do with the SqlFunctions I am using but can't make out why. I have seen a prior solution to a similar issue but it does not apply for this scenario I believe (This function can only be invoked by LINQ to Entities after Entity Framework update).

DateTime begin = new DateTime(2021, 1, 1);
DateTime end = new DateTime(2022, 1, 1);

var dataSet = from e in _db.animals
              where e.age == 2 &&
                    SqlFunctions.DateDiff("yy",begin, end) < 2                                                        
              select e.AnimalType
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2785460
  • 11
  • 1
  • How do you use `dataSet`? – gunr2171 Jan 18 '22 at 17:59
  • Are you use Entity Framework? If so, it might be more approprate to use EntityFunctions https://stackoverflow.com/questions/15637317/what-is-the-difference-between-sqlfunctions-and-entityfunctions – gunr2171 Jan 18 '22 at 18:01
  • Yes I am using entity framework. I tried using EntityFunctions as well but didn't have much luck with it as well. I am returning the dataSet as a jsonresult at the end -- return new JsonResult(dataSet.ToDataSourceResult(request)) – user2785460 Jan 18 '22 at 18:10
  • It always important to mention the exact verion of entity framework. Please add the appropriate tag. – Gert Arnold Jan 18 '22 at 19:17
  • That said, the predicate doesn't make sense because it doesn't depend on any queried database value. It's logically equivalent with `where e.age == 2 && true`. – Gert Arnold Jan 18 '22 at 19:21
  • `SqlFunctions.DateDiff("yy",begin, end)` compares the *variables*, not any table fields. That's what the error says. This clause isn't useful because its result is known without even running the query. – Panagiotis Kanavos Jan 18 '22 at 19:28
  • @PanagiotisKanavos If used in the right environment, like EF6, this function works perfectly with local variables. Not that it makes sense, anyway. – Gert Arnold Jan 18 '22 at 19:36
  • @GertArnold that would mean that `_db` isn't a DbContext, or that `animals` isn't a `DbSet`. Perhaps an evil-repository-from-hell that returns an `IEnumerable<>` because "best practices" ? – Panagiotis Kanavos Jan 18 '22 at 19:40
  • @PanagiotisKanavos Likely, yes. And so we're waiting for OP's clarification before any answer addressing the *technical* issue is possible. I assume that the *logical* flaw is only an artifact because of abridging the code. – Gert Arnold Jan 18 '22 at 19:42
  • 1
    @user2785460 what are `_db` and `animals`? What is the *full* exception text, including the call stack? The error you posted will only be thrown if you try to use an EF function in LINQ to objects. Is the exception thrown inside `ToDataSourceResult` perhaps? Where does this method come from? – Panagiotis Kanavos Jan 18 '22 at 19:44
  • Hey @GertArnold you are right that this works well on EF6 and my current version is EF3. Is there a different way I should be doing this for EF3? – user2785460 Jan 18 '22 at 20:02

2 Answers2

1

It’s probably due to the fact that you’re calling the function using two local variables as parameters so it’s evaluating client side before querying the database, resulting in the error.

Moho
  • 15,457
  • 1
  • 30
  • 31
  • If used in the right environment, like EF6, this function works perfectly with local variables. It wouldn't harm to run a little test before posting a tentative answer. – Gert Arnold Jan 18 '22 at 19:37
0

As Moho points out, both start and end are known, and don't need to be part of the query. Entity Framework is probably trying to evaluate the expression while compiling the query, rather than building it into the query. If this is really what you're trying to do, consider optimizing things by taking your expression out of the query.

DateTime begin = new DateTime(2021, 1, 1);
DateTime end = new DateTime(2022, 1, 1);
int yearsDifference = end.Year - begin.Year;

var dataSet = from e in _db.animals
              where e.age == 2 && yearsDifference < 2                                                        
              select e.AnimalType;

Or further:

DateTime begin = new DateTime(2021, 1, 1);
DateTime end = new DateTime(2022, 1, 1);
int yearsDifference = end.Year - begin.Year;

var dataSet = yearsDifference < 2
    ? Array.Empty<AnimalType>().AsQueryable()
    : from e in _db.animals
      where e.age == 2                                                       
      select e.AnimalType;
StriplingWarrior
  • 151,543
  • 27
  • 246
  • 315