0

I'm trying to figure out a way to use methods in a SELECT query in EF Core to reduce the amount of code that is rewritten over and over again. Instead a simple method could be used to query that piece of data. The query looks like so...

 var users = await DbContext.Users.Where(user => user.Id == 1)
                                   .Select(user => new UserDTO
                                   {
                                       Id = user.Id,
                                       Name = user.Name,
                                       Unavailable = user.Tasks
                                                       .Any(task => task.HasTasksToday()),
                                   }).ToListAsync();

The HasTasksToday() code looks like so...

    public bool HasTasksToday()
    {
        return Tasks.Any(task => task.StartedAt.Date == DateTime.Now.Date);
    }

The problem is I get a...

...could not be translated. Either rewrite the query in a form that can be translated...

...error.

I know that using IQueryable may work for this but I am unsure how that would even happen in the select statement as I know only how to use IQueryable on the Users entity.

Lewis
  • 566
  • 4
  • 21
  • what is the rdbms used? MySQL, Mssql? It means EF is not capable of translating the query in question into sql query statement. – Soner from The Ottoman Empire Jun 29 '21 at 11:54
  • *I know that using IQueryable may work for this* Not sure what you mean. It clearly doesn't, because the method call can't be translated into SQL. Use the predicate `task.StartedAt.Date = DateTime.Now.Date` directly in the query. – Gert Arnold Jun 29 '21 at 11:54
  • 1
    @GertArnold `task.StartedAt.Date = DateTime.Now.Date` or `task.StartedAt.Date == DateTime.Now.Date` ? – Soner from The Ottoman Empire Jun 29 '21 at 11:57
  • @snr I'm using MSSQL. – Lewis Jun 29 '21 at 11:59
  • @GertArnold Im trying to not use it directly in the query because its going to be a reusable method across multiple queries and I don't want to rewrite it many times if I can help it. – Lewis Jun 29 '21 at 12:00
  • 1
    @snr Yeah, just copied OP's code. I type too much SQL to notice such errors. – Gert Arnold Jun 29 '21 at 12:00
  • @GertArnold respect you , sir (; – Soner from The Ottoman Empire Jun 29 '21 at 12:01
  • Have you looked into repository + specification pattern? Sounds like that might be a bit what you're looking for. – Pavlos Jun 29 '21 at 12:02
  • Not necessarily repository pattern, but yes, why should this code be repeated so often? Anyway, you can always make the predicate itself reusable as an expression stored somewhere as a `const` in a utility class. – Gert Arnold Jun 29 '21 at 12:05
  • 2
    It sounds like you want an extension method on the `Tasks` property so the method would be defined something like `public static bool HasTasksToday(this IEnumerable)` and the select becomes `Unavailable = user.Tasks.HasTasksToday())`. Is this what you were trying to do? – Daniel Dearlove Jun 29 '21 at 12:17
  • see https://stackoverflow.com/questions/62115690/ef-core-queries-all-columns-in-sql-when-mapping-to-object-in-select/62138200#62138200 for using 3rd party package and plugging it into EFC (3.1, for 5.x it would need some adjustments). – Ivan Stoev Jun 29 '21 at 12:19
  • @DanielDearlove that worked for me. I think EF Core wasn't liking the method on the entity itself. It looks like it needs the IEnumerable to work. Without that I get the error. – Lewis Jun 29 '21 at 12:23
  • @Pavlos thanks for suggesting the specification pattern I think I will be refactoring and using that as a way to tidy up code. – Lewis Jun 29 '21 at 12:24
  • Could you please provide code on e.g. github to repro this? ;) EF cannot translate C# predicates so it will not work. There are many ways to solve your problem. You can e.g. create view in sql that shows users who has tasks at date that is provided in parameter: https://stackoverflow.com/questions/1687279/can-we-pass-parameters-to-a-view-in-sql – zolty13 Jun 29 '21 at 12:25
  • You may want to consider using [LINQKit](https://github.com/scottksmith95/LINQKit) which provides extension to LINQ that enables referencing predicates in other queries. – NetMage Jun 29 '21 at 16:38

0 Answers0