2

I have table Requests, that has Approvals :

public class Request {
    public virtual List<Approval> Approvals { get; set; } 
}

Requests come from DB

public DbSet<Request> Request { get; set; }

I have extension method:

public static IQueryable<Request> HaveApprovals(this IQueryable<Request> requests) {
    return requests.Where(r => r.ActiveApprovals().Count() > 0).ToList();
}

And another extension method:

public static IEnumerable<Approval> ActiveApprovals(this Request request) {
     return request.Approvals.Where(a => !a.Deleted);
}

But getting error:

LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable`1[Domain.Approval] ActiveApprovals(Domain.Request)' method, and this method cannot be translated into a store expression.

For some reason one extension method is able to be translated to LINQ, but when using extension method inside another, then it fails to translate. Any suggestions?

Jaanus
  • 16,161
  • 49
  • 147
  • 202

2 Answers2

0

Try to rewrite your HaveApprovals extension in this way:

public static IQueryable<Request> HaveApprovals(this IQueryable<Request> requests) {
    return requests.Where(r => r.Approvals.Any(a => !a.Deleted)).ToList();
}

Error occurs because EF tries to execute any operation in query on the server side ( SQL server). So SQL know nothing about your extension method and how to execute it.

UPDATE#1:

query below

var query = this.DataContext.Products.Where(x => x.Sales.Any(s => s.SectorId == 1));

will generate following SQL query

SELECT [t0].[Id], [t0].[Name], [t0].[Description]
FROM [dbo].[Products] AS [t0]
WHERE EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [dbo].[Sales] AS [t1]
    WHERE ([t1].[SectorId] = @p0) AND ([t1].[ProductId] = [t0].[Id])
    )
}

It will not extract all records so it will not decrease performance.

akorenchikov
  • 67
  • 6
  • I want it to execute in query, otherwise it will load my 100k items from database and its very slow. It should be able to translate extension method content to LINQ, just as it works when using only single extension method, not nested. – Jaanus Jan 14 '15 at 07:54
  • I have updated my answer with some proofs. Query will not extract yours 100k items ( see the similar query and generated SQL query). Also there is no way to translate custom extension methods into SQL. – akorenchikov Jan 14 '15 at 08:08
  • Check this topic to see that if using IEnumerable, it will execute the whole querya and filtering will be done in memory. http://stackoverflow.com/questions/2876616/returning-ienumerablet-vs-iqueryablet – Jaanus May 19 '15 at 07:47
  • I suggest removing `ToList` here so that you don't execute the query from inside of a scope. Leave it up to the containing context to decide when to execute, that way you can combine several of these extensions. – Alexander Trauzzi Sep 21 '16 at 01:45
0

Return a Iqueryable or Ienumerable and not a list. The query will be handled by sql so it cannot understand the returned list

Amex
  • 1