asp.net mvc - Use Entity framework I want to include only first children objects and not child of child(sub of sub)

Question

Useing Entity framework I want to include an only the first level of children objects and not the children of child

I have these two classes:

public class BusinessesTBL
{
    public string ID { get; set; }
    public string FirstName { get; set; }
    public string lastName { get; set; }

    public ICollection<OffersTBL> OffersTBLs { get; set; }
}

public class OffersTBL 
{
    public int ID { get; set; }
    public string Name { get; set; }

    public int CatId { get; set; }

    public string BusinessesTBLID { get; set; }
    public virtual BusinessesTBL BusinessesTBLs { get; set; }
}

when I try to bring all offers according to CatId field, I need to return the BusinessesTBLs also, but the method also return offers again per each BusinessesTBL obj , My code is :

public IQueryable<OffersTBL> GetOffersTBLsCat(int id)
{
    db.OffersTBLs.Include(s => s.BusinessesTBLs);
}

You can see the wrong result on : http://mycustom.azurewebsites.net/api/OffersApi/GetOffersTBLsCat/4

As you can see it return all offers under each Business object while business object under each offer, And I want only to return offers with its Business object without offer under Business obj.

Could anyone help please?

Answer 1

A downvote brought this answer back into my attention (thank you). I now see that a big part of it is nonsense.

Sure enough, the reason for the endless loop is relationship fixup. But you can't stop EF from doing that. Even when using AsNoTracking, EF performs relationship fixup in the objects that are materialized in one query. Thus, your query with Include will result in fully populated navigation properties OffersTBLs and BusinessesTBLs.

The message is simple: if you don't want these reference loops in your results, you have to project to a view model or DTO class, as in one of the other answers. An alternative, less attractive in my opinion, when serialization is in play, is to configure the serializer to ignore reference loops. Yet another less attractive alternative is to get the objects separately with AsNoTracking and selectively populate navigation properties yourself.

---

Original answer:

This happens because Entity Framework performs relationship fixup, which is the process that auto-populates navigation properties when the objects that belong there are present in the context. So with a circular references you could drill down navigation properties endlessly even when lazy loading is disabled. The Json serializer does exactly that (but apparently it's instructed to deal with circular references, so it isn't trapped in an endless loop).

The trick is to prevent relationship fixup from ever happing. Relationship fixup relies on the context's ChangeTracker, which caches objects to track their changes and associations. But if there's nothing to be tracked, there's nothing to fixup. You can stop tracking by calling AsNoTracking():

db.OffersTBLs.Include(s => s.BusinessesTBLs)
             .AsNoTracking()

If besides that you also disable lazy loading on the context (by setting contextConfiguration.LazyLoadingEnabled = false) you will see that only OffersTBL.BusinessesTBLs are populated in the Json string and that BusinessesTBL.OffersTBLs are empty arrays.

A bonus is that AsNoTracking() increases performance, because the change tracker isn't busy tracking all objects EF materializes. In fact, you should always use it in a disconnected setting.