CODEWITHSUNDEEP
c#entity-frameworklinq
Camilo Terevinto
Camilo Terevinto

Reputation: 32072

Query a 4-levels deep list hierarchy

I have a hierarchy like this:

public class BaseClass
{
    public virtual List<FirstChild> Childs { get; set; }
}
public class FirstChild
{
    public virtual List<SecondChild> Childs { get; set; }
}
public class SecondChild
{
    public virtual List<ThirdChild> Childs { get; set; }
}
public class ThirdChild
{
    public List<FourthCild> Childs { get; set; }
}
public class FourthCild
{
    public int SomeProperty { get; set; }
}

To get a specific ThirdChild from an instance of BaseClass, I was trying with something like this:

using (MyDb context = new MyDb())
{
    context.BaseClass.Where(u => u.username == username).FirstOrDefault()
    .FirstChild.Where(f => f.SomeId == idReceived).FirstOrDefault()
    .SecondChild.Where(s => s.MyProperty.Identifier == identifierReceived).FirstOrDefault()
    .ThirdChild.Where(t => t.Identifier == thirdIdentifier).FirstOrDefault();
}

Is this the shortest/cleanest way of doing this? I would think there's an easier way, but I'm new to Entity Framework. I don't have any data to test this for now, since I'm still modelling it. The context class looks like this:

public class MyDb: DbContext
{
    public DbSet<BaseClass> Base { get; set; }
    public DbSet<FirstChild> First { get; set; }
    public DbSet<SecondChild> Second { get; set; }
    public DbSet<ThirdChild> Third { get; set; }
}

Upvotes: 1

Views: 128

Answers (2)

Paddy
Paddy

Reputation: 33867

I think you might be going about this slightly backwards and your multiple 'FirstOrDefault' calls may cause you some performance issues, as each one will hit the DB.

Perhaps you need somthing more like:

var target = (from tc in context.Third
   where tc.Identifier == thirdIdentifier
   && tc.SecondChild.MyProperty.Identifer == identifierReceived
   && tc.SecondChild.FirstChild.someId == idReceived
   && tc.SecondChild.FirstChild.Base.username == username
   select tc).FirstOrDefault();

i.e. thinking similarly to how you would do this if you were writing SQL directly, select from ThirdChild, join up the hierarchy and filter in the where clause.

Note - this assumes a one-many relationship between each parent and its children.

Upvotes: 1

Ivan Stoev
Ivan Stoev

Reputation: 205849

You are defining one-to-many relationships. You already provided so called navigation property at the one side, but you need also to provide the similar at the many side, like this

public class BaseClass
{
    public virtual List<FirstChild> Childs { get; set; }
}
public class FirstChild
{
    public virtual BaseClass Parent { get; set; }
    public virtual List<SecondChild> Childs { get; set; }
}
public class SecondChild
{
    public virtual FirstChild Parent { get; set; }
    public virtual List<ThirdChild> Childs { get; set; }
}
public class ThirdChild
{
    public virtual SecondChild Parent { get; set; }
    public List<FourthCild> Childs { get; set; }
}
public class FourthCild
{
    public virtual ThirdChild Parent { get; set; }
    public int SomeProperty { get; set; }
}

Then you can start the query directly from the level you want

context.Third.Where(t => t.Identifier == thirdIdentifier
    && t.Parent.MyProperty.Identifier == identifierReceived
    && t.Parent.Parent.SomeId == idReceived
    && t.Parent.Parent.Parent.username == username)

Upvotes: 1

Related Questions