2

As part of a small .NET Core 3 project, I'm trying to use the data model based in Entity Framework, but I'm having some troubles related with queries on joined tables.

When looking for data matching a condition in a single table, the model is easy to understand

List<Element> listOfElements = context.Elements.Where(predicate).ToList();

However, when this query requires joined tables, I'm not sure how to do it efficiently. After some investigation, it seems that the include (and theninclude) methods are the way to go, but I have the impression that the Where clause after the include is not executed at DB level but after all the data has been retrieved. This might work with small datasets, but I don't think it's a good idea for a production system with millions of rows.

List<Element> listOfElements = context.Elements.Include(x => x.SubElement).
    Where(predicate).ToList();

I've seen some examples using EF+ library, but I'm looking for a solution using the nominal EF Core. Is there any clean/elegant way to do it?

Thank you.

Rhadamon
  • 31
  • 3
  • How is your `predicate` defined? – haim770 Aug 18 '20 at 14:09
  • Func predicate = f => f.ElementType == elementType where elementType is a string parameter – Rhadamon Aug 18 '20 at 14:30
  • 2
    It should be `Expression> predicate`. Otherwise, it would only match `Enumerable.Where()` (which is Linq-to-Objects - In memory) instead of your desired `Queryable.Where()`. See https://stackoverflow.com/questions/793571/why-would-you-use-expressionfunct-rather-than-funct – haim770 Aug 18 '20 at 14:35
  • @haim770 I believe you are right if you don't use the Queryable.Where() the filter will be executed in memory as opposed to being executed in the database. – CarlosMorgado Aug 19 '20 at 00:04
  • As @haim770 commented, the solution was adding the "Expression" before for accessing the Queryable.Where instead of the Enumerable.Where. Thanks! If you want, add it as an answer as it's the actual solution to my problem. – Rhadamon Aug 19 '20 at 09:46

1 Answers1

0

There are a few scenarios when the data from DB is populating:

  • Deferred query execution: this is when you try to access your query results, for example, in the foreach statement.
  • Immediate Query Execution: this when you call ToList() (or conversions to other collections, like ToArray()).

I think the answer's to your question:

... but I have the impression that the Where clause after the include is not executed at DB level but after all the data has been retrieved.

is that your assumptions are wrong because you are calling ToList() at the end, not before the Where method.

For more information please also check here.

I've another suggestion also: to be sure about what is exactly executing at the DB level run SQL Server Profiler when executing your query.

Hope this will help ))

Arsen Khachaturyan
  • 7,904
  • 4
  • 42
  • 42
  • As mentioned by @haim770 in the comments of the original post the assumptions are not wrong since given the predicate used the Where() method used is not Queryable.Where() method which will make it so that the query is executed locally with everything in memory instead of in the database itself – CarlosMorgado Aug 19 '20 at 00:09
  • As @haim770 mentioned, the problem was on which Where method was being used, Enumerable instead of Queryable. – Rhadamon Aug 19 '20 at 09:50
  • @Rhadamon, sorry for the confusion. Actually that is correct. In the case of Enumerable `Where` will be used, the data manipulation will happen at the memory level. However, you were mentioning such a method as [Include](https://docs.microsoft.com/en-us/dotnet/api/system.data.objects.objectquery-1.include?view=netframework-4.8), which, if applied to DB Context, will work with Database queries (`IQuerable`) level. Please take my answer as connected to that specific case, because, for the situations with ordinary memory collection, you shouldn't worry about `Where` clause execution order. – Arsen Khachaturyan Aug 19 '20 at 10:44