How to prevent cyclic loading of related objects in Entity Framework Code First

Question

I am new to Entity Framework and trying to learn how to use Code First to load objects from a database.

My model contains a user:

public class User { public int UserID { get; set; } [Required] public string Name { get; set; } // Navigation Properties public virtual ICollection<AuditEntry> AuditEntries { get; set; } } 

Each user can have a set of audit records, each of which contains a simple message:

 public class AuditEntry { public int AuditEntryID { get; set; } [Required] public string Message { get; set; } // Navigation Properties public int UserID { get; set; } public virtual User User { get; set; } } 

I have a DBC context that just expands two tables:

 public DbSet<User> Users { get; set; } public DbSet<AuditEntry> AuditEntries { get; set; } 

What I want to do is load the list of AuditEntry objects containing this message and the associated User object containing the UserID and Name properties.

 List<AuditEntry> auditEntries = db.AuditEntries.ToList(); 

Since I have navigation properties marked as virtual and I don’t have lazy loading disabled, I get an infinitely deep graphic object (each AuditEntry object has a User object that contains a list of AuditEntries, each of which contains a user object that contains a list of AuditEntries and etc.)

This is not good if I want to serialize an object (for example, send as a result in the web API).

I tried disabling lazy loading (either by removing virtual keywords from my navigation properties in the model, or by adding this.Configuration.LazyLoadingEnabled = false to my DBContext). As expected, this leads to a flat list of AuditEntry objects with the user set to null.

With lazy loading, I tried to load the user like this:

 var auditentries = db.AuditEntries.Include(a => a.User); 

but this leads to the same deep / cyclical result as before.

How to load one depth level (for example, include user ID and name) without also loading backlinks / following navigation properties back to the original object and creating a loop?

Answer 1

After a big hack, I got the following potential solution using the dynamic return and forecast type in my Linq query:

 public dynamic GetAuditEntries() { var result = from a in db.AuditEntries select new { a.AuditEntryID, a.Message, User = new { a.User.UserID, a.User.Username } }; return result; } 

This creates (internally) the following SQL, which seems reasonable:

 SELECT [Extent1].[AuditEntryID] AS [AuditEntryID], [Extent1].[Message] AS [Message], [Extent1].[UserID] AS [UserID], [Extent2].[Username] AS [Username] FROM [dbo].[AuditEntries] AS [Extent1] INNER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[UserID] = [Extent2].[UserID] 

This leads to the results that I need, but it seems a bit long (especially for real-life models, which would be significantly more complex than my example), and I doubt what impact this will have on performance.

<strong> Benefits

• This gives me great flexibility regarding the exact contents of my returned object. Since I usually do most of my client-side interaction / user interface templates, I often have to create multiple versions of model objects. Usually I need a certain level of granularity by which users can see what properties (for example, I don’t want to send every user email address to a low-privilege user’s browser in an AJAX request).

• It allows the entity structure to intelligently build a query and select only those fields that I have selected for the project. For example, inside each top-level AuditEntry object, I want to see User.UserID and User.Username, but not User.AuditEntries.

disadvantages

• The return type from my web API is no longer strongly typed, so I could not create a strongly typed MVC view based on this API. As it happens, this is not a problem for my particular case.

• Projecting manually in this way from a large / complex model can lead to a lot of code, it seems like a lot of work and could potentially lead to API errors. This should be carefully checked.

• The API method is closely related to the structure of the model, and since it is no longer fully automated based on my POCO classes, any changes made to the model should be reflected in the code that loads them.

Enable method?

I'm still a little confused about using the .Include () method. I understand that this method will indicate that related objects should be “eagerly loaded” along with the specified object. However, since the manual seems to be that the navigation properties should be placed on both sides of the relationship and marked as virtual, the Include method apparently leads to the creation of a loop that has a significant negative impact on its usefulness ( especially with serialization).

In my case, the “tree” would look a bit:

 AuditEntry User AuditEntries * n User * n etc 

I would be very interested to hear any comments about this approach, the impact of dynamic usage in this way, or any other ideas.