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.

Answer 2

You have deactivated lazy loading on OffersTBLs making it non-virtual. What if you activate lazy loading? like this:

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

    //put a virtual here
    public virtual ICollection<OffersTBL> OffersTBLs { get; set; }
}

Then, be sure to not call/include OffersTBLs when serializing. If the OffersTBLs are still returning, it is because you are fetching them somewhere in your code. If this is happening, edit your question and paste all the code, including the serializing logic.

Answer 3

Since OffersTBL has an association to BusinessesTBL and BusinessesTBL to OffersTBL you can loop infinitly throw the Entities like OffersTBL.BusinessesTBL.OffersTBL.BusinessesTBL and so on.

To control the nested depth of the Entities i'm usually using helperclasses with the needed properties in them.

For BusinessesTBL

public class BusinessesTBLHelper
{
    private BusinessesTBLHelper(BusinessesTBL o){
        ID = o.ID;
        FirstName = o.FirstName;
        lastName = o.LastName;
        OffersTBLids = new List<int>();

        foreach(OffersTBL offersTbl in o.OffersTBLs){
            OffersTBLids.Add(offersTbl.ID);
        }
    }

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

    public IEnumerable<int> OffersTBLids { get; set; } //no references anymore
}

And same for your OffersTBL Entity.

public class OffersTBLHelper
{
    private OffersTBLHelper(OffersTBL o){
        ID = o.ID;
        Name = o.Name;
        CatId = o.CatId;
        BusinessesTBLID = o.BusinessesTBLID;
        BusinessesTBLs = new BusinessesTBLHelper(o.BusinessesTBLs);
    }

    public string ID { get; set; }
    public string Name{ get; set; }
    public intCatId{ get; set; }

    public string BusinessesTBLID { get; set; }
    public BusinessesTBLHelper BusinessesTBLs { get; set; }
}

On quering database you can directly create the new helperobjects from queryresult:

public IEnumerable<OffersTBLHelper> GetOffersTBLsCat(int id)
{
    return db.OffersTBLs.where(s => s.CatId == id).Select(x=> new OffersTBLHelper(x)).ToList();
}

Now you have all the OffersTBL with BusinessesTBLs under. The loop stops here because the BusinessesTBLs have no OffersTBL under it. However, it only has them Ids in a List for further referencing and identifying.