Entity framework рекурсивный запрос
Category и ID , Name , Parent и Children . Parent и Children из Category тоже.
когда я делаю запрос LINQ to Entities для конкретного Item , Он не возвращает связанный Category , Если я использую Include("Category") метод. Но это не приносит полную категорию, с ее родителем и детьми. Я мог бы сделать Include("Category.Parent") , но этот объект что-то вроде дерево, у меня есть рекурсивная иерархия и я не знаю, где она заканчивается.
как я могу сделать EF полностью загрузить Category , с родителем и детьми, и родитель со своим родителем и детьми, и так далее?
это не что-то для всего приложения, для соображений производительности это было бы необходимо только для этого конкретного объекта, категории.
Если вы определенно хотите, чтобы вся иерархия была загружена, то если бы это был я, я бы попытался написать хранимую процедуру, которая должна возвращать все элементы в иерархии, возвращая тот, который вы просите сначала (и его потомки впоследствии).
а затем пусть исправление отношений EF гарантирует, что они все подключены.
т. е. что-то вроде:
Если вы правильно написали хранимую процедуру, материализуя все элементы в иерархии (т. е. ToList() ) должен сделать EF отношения fixup пинает.
и затем нужный пункт (первый()) должны иметь все его дети загружены, и у них должны быть свои дети загружены и т. д. Все они будут заполнены из этого одного вызова хранимой процедуры, поэтому проблем с MARS тоже нет.
надеюсь, что это помогает
Алекс
это может быть опасно, если вы случайно загрузите все рекурсивные сущности, особенно в категории, вы можете получить гораздо больше, чем вы ожидали:
внезапно вы загрузили большую часть своей базы данных, вы могли бы также загрузить строки счетов-фактур, затем клиентов, а затем все их другие счета-фактуры.
что вы должны сделать, это что-то вроде следующего:
лучшее решение, однако, чтобы построить свой запрос, чтобы построить анонимный класс с результатами, поэтому вам нужно только один раз попасть в хранилище данных.
таким образом, вы можете вернуть результат словаря, если это необходимо.
помните,что ваши контексты должны быть как можно короче.
вы должны скорее ввести таблицу сопоставления, которая отображает каждую категорию родителя и ребенка, вместо добавления родительского и дочернего свойства к самому грузу.
в зависимости от того, как часто вам нужна эта информация, он может быть получен по запросу. С помощью уникальных ограничений в БД вы можете избежать бесконечного количества возможных отношений.
вы не хотите делать рекурсивную загрузку иерархии, если вы не позволяете пользователю итеративно детализировать дерево: каждый уровень рекурсии-это еще одна поездка в базу данных. Точно так же вы захотите ленивую загрузку, чтобы предотвратить дальнейшие поездки БД, когда вы проходите иерархию при рендеринге на страницу или отправке через веб-сервис.
вместо этого переверните свой запрос: Get Catalog и Include предметы в нем. Это даст вам все элементы как иерархически (свойства навигации) и сглажены, поэтому теперь вам просто нужно исключить некорневые элементы, присутствующие в корне, что должно быть довольно тривиальным.
у меня была эта проблема и предоставил подробный пример этого решения другому,здесь
At the end of the day I would like to load the tree into a TreeView but I can't figure how to load it all at once. I tried:
This will get me the the first 2 generations but not more. How do I load the entire tree with all generations and the additional data?
If this is still a problem for you, I've provided another answer that's better than chatty DB calls and will be able to populate the entire hierarchy without having to specify infinite Includes.
Conclusion
CTEs are a powerful way to explore recursive data within a SQL-based relational database engine. EF Core supports saving recursive entities, and as we’ve seen in this post, it’s relatively straightforward to model. There are caveats to what FromSqlRaw can accomplish, so if developers need additional LINQ capability, they would be better adding any CTE implementations into a SQL view or stored procedure.
Hopefully, you found this post helpful and fun. As always, thanks for reading, and please share my content if you found it useful.
Common Table Expressions (CTEs)
Common Table Expressions is a mechanism available in most relational database engines, where developers are enabled to define temporarily named result sets and reference them within the execution scope of a Select , Insert , Update , or Delete statement. They are popular amongst database developers as they can allow for complex recursive queries, which we’ll use in this post.
Most modern database engines support CTEs, with variations in SQL syntax: Oracle, SQL Server, MySQL, PostgreSQL, SQLite, and MariaDB.
Developers can find an excellent in-depth article on CTE with many examples at DatabaseStar written by Ben Brumm.
About Khalid Abuhakmeh
About Khalid Abuhakmeh
Khalid is a product designer, traveler, respected community member, and open source contributor.
В конце дня я хотел бы загрузить дерево в TreeView, но я не могу понять, как загрузить его все сразу. Я пытался:
Это даст мне первые 2 поколения, но не более. Как загрузить все дерево со всеми поколениями и дополнительными данными?
Если это все еще проблема для вас, я предоставил другой ответ, который лучше, чем болтливые вызовы БД, и сможет заполнить всю иерархию без необходимости указывать бесконечные включения.
Недавно у меня была эта проблема, и я наткнулся на этот вопрос после того, как нашел простой способ добиться результатов. Я отредактировал ответ Крейга, предоставив 4-й метод, но власть имущие решили, что это должен быть другой ответ. Я не против :)
Это работает до тех пор, пока все ваши элементы в таблице знают, к какому дереву они принадлежат (что в вашем случае выглядит так:) t.ID . Тем не менее, неясно, какие сущности у вас действительно есть в игре, но даже если у вас их больше одной, вы должны иметь FK в сущности Children , если это не TreeSet
В принципе, просто не используйте Include() :
Это вернет ВСЕ элементы дерева и поместит их все в корень коллекции. На этом этапе ваш набор результатов будет выглядеть так:
Поскольку вы, вероятно, хотите, чтобы ваши сущности выходили из EF только иерархически, это не то, что вам нужно, верно?
.. затем исключите потомков, присутствующих на корневом уровне:
К счастью, поскольку в вашей модели есть свойства навигации, коллекции дочерних сущностей по-прежнему будут заполняться, как вы можете видеть на иллюстрации результирующего набора выше. Вручную перебирая результирующий набор с помощью foreach() цикла и добавляя эти корневые элементы в a new List() , теперь у вас будет список с правильно вложенными корневыми элементами и всеми потомками.
Если ваши деревья становятся большими, а производительность вызывает беспокойство, вы можете отсортировать возвращаемый набор по ВОСХОЖДЕНИЮ ParentID (это Nullable , верно?), чтобы все корневые элементы были первыми. Итерируйте и добавляйте, как и раньше, но прервите цикл, как только вы доберетесь до того, что не равно нулю.
И теперь subset будет выглядеть так:
О решениях Крейга:
-
You really don't want to use lazy loading for this!! A design built around the necessity for n+1 querying will be a major performance sucker. ********* (Well, to be fair, if you're going to allow a user to selectively drill down the tree, then it could be appropriate. Just don't use lazy loading for getting them all up-front!!)
Modeling Most SQL Relationships In Entity Framework Core
Read Next
Problems With CTEs and FromSqlRaw
Generally, FromSqlRaw would allow developers to wrap additional criteria around the initial query. That is not the case for CTEs. Attempting to add any LINQ criteria will result in an InvalidOperationException with the following message.
The workaround for this issue is to place our CTE SQL within a stored procedure or SQL View. After defining our stored procedure, we can call the same helper method, which we redefined to support the change.
6 Answers 6
I had this problem recently and stumbled across this question after I figured a simple way to achieve results. I provided an edit to Craig's answer providing a 4th method, but the powers-that-be decided it should be another answer. That's fine with me :)
This works so long as your items in the table all know which tree they belong to (which in your case it looks like they do: t.ID ). That said, it's not clear what entities you really have in play, but even if you've got more than one, you must have a FK in the entity Children if that's not a TreeSet
Basically, just don't use Include() :
This will bring back ALL the items for the tree and put them all in the root of the collection. At this point, your result set will look like this:
Since you probably want your entities coming out of EF only hierarchically, this isn't what you want, right?
.. then, exclude descendants present at the root level:
Fortunately, because you have navigation properties in your model, the child entity collections will still be populated as you can see by the illustration of the result set above. By manually iterating over the result set with a foreach() loop, and adding those root items to a new List() , you will now have a list with root elements and all descendants properly nested.
If your trees get large and performance is a concern, you can sort your return set ASCENDING by ParentID (it's Nullable , right?) so that all the root items are first. Iterate and add as before, but break from the loop once you get to one that is not null.
And now subset will look like this:
About Craig's solutions:
-
You really don't want to use lazy loading for this!! A design built around the necessity for n+1 querying will be a major performance sucker. ********* (Well, to be fair, if you're going to allow a user to selectively drill down the tree, then it could be appropriate. Just don't use lazy loading for getting them all up-front!!)
Imagine we have a table in the database called Category where each row may have a parent Category such that we end up with a hierarchical tree of categories and subcategories to some unknown depth.
Each Category contains an optional ParentCategoryId forming the basis of the table structure.
Using Entity Framework Core this would be configured like this:
So let's say we wish to produce a query to return a list of of the root most categories such that they also contain any child categories and in turn these child categories contain their subcategories and so on. Our first thought might be something like:
Unfortunately this will not work as the first levels subcategories children will not be loaded.
One way to get around this would be to load all categories first by enumerating a general select all query, like the following:
This is ok for a small amount of data, but what if you have thousands or millions of rows and perhaps you are only interested in one root item this is not very efficient.
Furthermore we more often than not don’t want unnecessary data to be queried as typically we might be returning the data via a rest endpoint so would use some kind of DTO representation of our class, for example:
Finally, one way to query the hierarchical would be to write a recursive method which applies a projection of Category to CategoryDTO and queries all subcategories as needed to a maximum specified depth.
Why do we need to specify a maximum depth? Well the recursive function which ultimately generates SQL needs to know when to stop and we want to achieve all this in a single query.
To do this we create a method which contains the required projection, in which makes a recursive call back to itself.
For each layer down we apply the projection recursively to the SubCategories property until we read the maximum depth. Ideally when picking the maximum depth a knowledge of the depth the hierarchy extends to is helpful to ensure nothing is missed. Going a few levels deeper than required is not a big problem as the underlying SQL left joins will just be populated with null data so not too costly.
Finally to use this we simply query the categories in the typical EF way, but noting for the root we only include Categories with a null ParentCategory and in the Select we initiate the recursive call to the projection method passing in a maximum depth and a starting depth of 0.
Photo by Rowan Lamb
One of the most powerful features of relational database engines is the ability for tables to be self-referential. Having rows reference other rows within the same table allows us to create complex hierarchies and tree structures with minimal effort.
In this short post, we’ll explore how to model a self-referencing entity with EF Core and then create a helper method that will allow us to get all downstream data starting at a particular level. To follow along, developers will need a running instance of Microsoft SQL Server.
Using CTEs With EF Core
The most straightforward way to use CTEs with EF Core is to use the FromSqlRaw extension method. The FromSqlRaw extension method is available on all DbSet properties, with the caveat, our query results will satisfy all properties of our EF entity’s shape. In a previous section, we saw the definition for Employee . Let’s write a CTE that when given an employee Id , the query will return that individual’s information and downline reports.
There are a few critical elements in the SQL Query:
- organization is our CTE
- We define the shape of our CTE within the parenthesis: ( id , name , title , managerid , and below )
- the column value below is an accumulator used to denote the level at which we are currently in the recursive call.
- The parameter @id is used to start our recursive call at a particular row, limiting our result set.
Now, let’s move this query into our DbContext using a helper function.
We take the employee’s id we need the downline reports for and then return the results. To use this method now, we can call it like any other method.
Let’s see this code in action in a simple console application.
Running this program with an employee Id of 1 will result in the following output.
We can rerun our application with the Id to 5 results in the following output.
The Recursive Database Model
We’ll start with the most familiar recursive model everyone is aware of, and that’s the organizational chart for a company. With Entity Framework Core, all we need to do is define our navigation properties. If you need a guide on defining relationships, If you read my previous blog post on modeling all relationships with EF Core.
We know that employees have managers and that managers have reporting employees.
Let’s seed some data in our EF Core DbContext using the organizational chart of Springfield Nuclear Power Plant.
Now that we’re ready, let’s talk about the query we’d like to execute against our dataset.
Given an employee’s identifier number (id), we want to see all subsequent reports, regardless of the management chain.
If we were to query using our DbContext , we would only ever get one level of reports. The default approach would be expensive and wasteful.
There must be a better way, right?! Of course, there is! The solution is to utilize Common Table Expressions.
Читайте также: