Serialize Iqueryable To Json String

Handling Entity Relations Microsoft Docsby Mike Wasson. Download Completed Project. This section describes some details of how EF loads related entities, and how to handle circular navigation properties in your model classes. This section provides background knowledge, and is not required to complete the tutorial. If you prefer, skip to Part 5. Eager Loading versus Lazy Loading. When using EF with a relational database, its important to understand how EF loads related data. Its also useful to see the SQL queries that EF generates. To trace the SQL, add the following line of code to the Book. Service. Context constructor public Book. Service. Context basenameBook. Service. Context. New code. this. Database. Log s System. Diagnostics. Debug. Write. Lines. If you send a GET request to apibooks, it returns JSON like the following. Book. Id 1. Title Pride and Prejudice. Year 1. 81. 3. Price 9. Genre Comedy of manners. Author. Id 1. Author null. You can see that the Author property is null, even though the book contains a valid Author. Id. Thats because EF is not loading the related Author entities. The trace log of the SQL query confirms this SELECT. Extent. 1. Book. Id AS Book. Id. Extent. 1. Title AS Title. Extent. 1. Year AS Year. Extent. 1. Price AS Price. Extent. 1. Genre AS Genre. Extent. 1. Author. Id AS Author. Id. FROM dbo. Books AS Extent. The SELECT statement takes from the Books table, and does not reference the Author table. For reference, here is the method in the Books. Introduction Here we will learn how to pass values from one page to another in asp. C, VB. NET with example or send values with query. Controller class that returns the list of books. IQueryablelt Book Get. Books. return db. Books. Lets see how we can return the Author as part of the JSON data. There are three ways to load related data in Entity Framework eager loading, lazy loading, and explicit loading. There are trade offs with each technique, so its important to understand how they work. Eager Loading. With eager loading, EF loads related entities as part of the initial database query. To perform eager loading, use the System. Data. Entity. Include extension method. IQueryablelt Book Get. Books. return db. In previous articles I explained create installer file for windows application in visual studio, split string including delimiter at the end in c, vb. This section describes some details of how EF loads related entities, and how to handle circular navigation properties in your model classes. This section provides. Serialize Iqueryable To Json String' title='Serialize Iqueryable To Json String' />. Web APIWeb APIASP. NET Web API Web API. Books. new code. Includeb b. Author. This tells EF to include the Author data in the query. If you make this change and run the app, now the JSON data looks like this. Book. Id 1. Title Pride and Prejudice. Year 1. 81. 3. Price 9. Genre Comedy of manners. Author. Id 1. Author. Author. Id 1. Name Jane Austen. The trace log shows that EF performed a join on the Book and Author tables. SELECT. Extent. 1. Book. Id AS Book. Id. Extent. 1. Title AS Title. Extent. 1. Year AS Year. Extent. 1. Price AS Price. Extent. 1. Genre AS Genre. Extent. 1. Author. Id AS Author. Id. Extent. 2. Author. Id AS Author. Id. Extent. 2. Name AS Name. FROM dbo. Books AS Extent. INNER JOIN dbo. Authors AS Extent. ON Extent. 1. Author. Id Extent. 2. Author. Id. Lazy Loading. With lazy loading, EF automatically loads a related entity when the navigation property for that entity is dereferenced. To enable lazy loading, make the navigation property virtual. For example, in the Book class public class Book. Other properties. Virtual navigation property. Author Author get set. Now consider the following code var books db. Books. To. List Does not load authors. Author Loads the author for books0. No Bird Sing Theft Of The Commons Rar on this page. When lazy loading is enabled, accessing the Author property on books0 causes EF to query the database for the author. Lazy loading requires multiple database trips, because EF sends a query each time it retrieves a related entity. Generally, you want lazy loading disabled for objects that you serialize. The serializer has to read all of the properties on the model, which triggers loading the related entities. For example, here are the SQL queries when EF serializes the list of books with lazy loading enabled. You can see that EF makes three separate queries for the three authors. SELECT. Extent. 1. Book. Id AS Book. Id. Extent. 1. Title AS Title. Extent. 1. Year AS Year. Extent. 1. Price AS Price. Extent. 1. Genre AS Genre. Extent. 1. Author. Id AS Author. Id. FROM dbo. Books AS Extent. Extent. 1. Author. Id AS Author. Id. Extent. 1. Name AS Name. FROM dbo. Authors AS Extent. WHERE Extent. 1. Author. Id Entity. Key. Value. Extent. Author. Id AS Author. Id. Extent. 1. Name AS Name. FROM dbo. Authors AS Extent. WHERE Extent. 1. Author. Id Entity. Key. Value. Extent. Author. Id AS Author. Id. Extent. 1. Name AS Name. FROM dbo. Authors AS Extent. WHERE Extent. 1. Author. Id Entity. Key. Value. There are still times when you might want to use lazy loading. Eager loading can cause EF to generate a very complex join. Or you might need related entities for a small subset of the data, and lazy loading would be more efficient. One way to avoid serialization problems is to serialize data transfer objects DTOs instead of entity objects. Ill show this approach later in the article. Explicit Loading. Explicit loading is similar to lazy loading, except that you explicitly get the related data in code it doesnt happen automatically when you access a navigation property. Explicit loading gives you more control over when to load related data, but requires extra code. For more information about explicit loading, see Loading Related Entities. Navigation Properties and Circular References. When I defined the Book and Author models, I defined a navigation property on the Book class for the Book Author relationship, but I did not define a navigation property in the other direction. What happens if you add the corresponding navigation property to the Author classAuthor. Author. Id get set. Name get set. ICollectionlt Book Books get set. Unfortunately, this creates a problem when you serialize the models. If you load the related data, it creates a circular object graph. When the JSON or XML formatter tries to serialize the graph, it will throw an exception. The two formatters throw different exception messages. Here is an example for the JSON formatter. Message An error has occurred. Exception. Message The Object. Content1 type failed to serialize the response body for content type. Exception. Type System. Invalid. Operation. Exception. Stack. Trace null. Inner. Exception. Message An error has occurred. Exception. Message Self referencing loop detected with type Book. Service. Models. Book. Path 0. Author. Books. Exception. Type Newtonsoft. Json. Json. Serialization. Exception. Stack. Trace. Here is the XML formatter lt Error. Message An error has occurred. Message. Exception. Message The Object. Content1 type failed to serialize the response body for content type. Exception. Message. Exception. Type System. Invalid. Operation. Exceptionlt Exception. Type. lt Stack. Trace. Inner. Exception. Message An error has occurred. Message. Exception. Message Object graph for type Book. Service. Models. Author contains cycles and cannot be. Exception. Message. Exception. Type System. Runtime. Serialization. Serialization. Exceptionlt Exception. Type. lt Stack. Trace. Stack. Trace. lt Inner. Exception. One solution is to use DTOs, which I describe in the next section. A circular reference was detected while serializing an object of type Sub. Sonic. Schema. Database. Column. Provided answers are good, but I think they can be improved by adding an architectural perspective. Investigation. MVCs Controller. Json function is doing the job, but it is very poor at providing a relevant error in this case. By using Newtonsoft. Json. Json. Convert. Serialize. Object, the error specifies exactly what is the property that is triggering the circular reference. This is particularly useful when serializing more complex object hierarchies. Proper architecture. One should never try to serialize data models e. EF models, as ORMs navigation properties is the road to perdition when it comes to serialization. Data flow should be the following Database data models service models JSON string. Service models can be obtained from data models using auto mappers e. Automapper. While this does not guarantee lack of circular references, proper design should do it service models should contain exactly what the service consumer requires i. In those rare cases, when the client requests a hierarchy involving the same object type on different levels, the service can create a linear structure with parent child relationship using just identifiers, not references. Modern applications tend to avoid loading complex data structures at once and service models should be slim. E. g. access an event only header data identifier, name, date etc. JSON containing only header datamanaged attendees list access a popup and lazy load the list service model JSON containing only the list of attendees.