Select entities with multiple and nested levels without using Include

Question

I have the following entity:

public class Item 
{
    public int Id { get; set; }

    public int? ParentId { get; set; }
    public Item Parent { get; set; }
    public List<Item> Children { get; set; }

    public double PropertyA { get; set; }
    public double PropertyB { get; set; }
    ...
}

Now I want to query the database and retrieve data of all the nested children. I could achieve this by using Eager Loading with Include():

var allItems = dbContext.Items
                    .Include(x => Children)
                    .ToList();

But instead of Eager Loading, I want to do the following projection:

public class Projection 
{
    public int Id { get; set; }
    public List<Projection> Children { get; set; }
    public double PropertyA { get; set; }
}

Is it possible to retrieve only the desired data with a single select? We are using Entity Framework 6.1.3.

Edit: This is what I have tried so far. I really don't know how to tell EF to map all child Projection the same way than their parents.

An unhandled exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll

Additional information: The type 'Projection' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order.

var allItems = dbContext.Items
    .Select(x => new Projection
    {
        Id = x.Id,
        PropertyA = x.PropertyA,
        Children = x.Children.Select(c => new Projection()
        {
            Id = c.Id,
            PropertyA = c.PropertyA,
            Children = ???
        })
    })
    .ToList();

You can use Lazy Loading instead of Eager Loading MSDN Link — Baximilian
– Baximilian, Commented Nov 17, 2016 at 16:32
but did you test your last query, Doing that projection EF should know it needs to fetch some columns from Children without the necessity of call Include extension method. — ocuenca
– ocuenca, Commented Nov 17, 2016 at 16:44
@Baximilian, I know I can achieve this with Eager Loading or recursive db calls, but I'm curious if it is possible with a single query. — Marc
– Marc, Commented Nov 17, 2016 at 20:40
@octavioccl, I updated my question with the exception message I got when executing the query. — Marc
– Marc, Commented Nov 17, 2016 at 20:42
What are you doing in your inner projection (in Children), you are omitting that. I think the exception tells well what is going on — ocuenca
– ocuenca, Commented Nov 17, 2016 at 20:49

grek40 · Accepted Answer · 2016-11-29 19:55:26Z

Generally speaking, you can't load a recursive structure of unknown unlimited depth in a single SQL query, unless you bulk-load all potentially relevant data irregardless whether they belong to the requested structure.

So if you just want to limit the loaded columns (exclude PropertyB) but its ok to load all rows, the result could look something like the following:

var parentGroups = dbContext.Items.ToLookup(x => x.ParentId, x => new Projection
{
    Id = x.Id,
    PropertyA = x.PropertyA
});

// fix up children
foreach (var item in parentGroups.SelectMany(x => x))
{
    item.Children = parentGroups[item.Id].ToList();
}

If you want to limit the number of loaded rows, you have to accept multiple db queries in order to load child entries. Loading a single child collection could look like this for example

entry.Children = dbContext.Items
    .Where(x => x.ParentId == entry.Id)
    .Select(... /* projection*/)
    .ToList()

Evk · Accepted Answer · 2016-11-29 10:16:44Z

1

I see only a way with first mapping to anonymous type, like this:

var allItems = dbContext.Items
            .Select(x => new {
                Id = x.Id,
                PropertyA = x.PropertyA,
                Children = x.Children.Select(c => new {
                    Id = c.Id,
                    PropertyA = c.PropertyA,
                })
            })
            .AsEnumerable()
            .Select(x => new Projection() {
                Id = x.Id,
                PropertyA = x.PropertyA,
                Children = x.Children.Select(c => new Projection {
                    Id = c.Id,
                    PropertyA = c.PropertyA
                }).ToList()
            }).ToList();

A bit more code but will get the desired result (in one database query).

answered Nov 29, 2016 at 10:16

Evk

102k8 gold badges156 silver badges204 bronze badges

5 Comments

Ivan Stoev Over a year ago

While this eliminates the exception, I don't see how it solves populating the second, third etc. level Children collections (which is not possible anyway).

Evk Over a year ago

@IvanStoev I don't think OP needs to populate children collections recursively, just first level. For example he says " I could achieve this ... with Include()".

grek40 Over a year ago

@Evk read comments "But how should I tell EF to create a projection for all the Children -> Children -> Children... and so on..." (Marc, 17. Nov.). If all Students (no filter!) are loaded as tracked entities with Include(x => x.Children), then all levels will be loaded because the child of one entity is itself a tracked entity who's children where loaded. This falls apart as soon as a filtered subset of entities is loaded.

Marc Over a year ago

Yes, this is the point. I would like to receive an object graph with the children mapped recursively. At least with this sample I could retrieve the desired data with a single db query. Then I would have to map recursively in memory afterwards.

Evk Over a year ago

Ok I see I did not carefully read comments. I'll leave answer still since it was a little bit useful for the OP. I don't think you can recursively get all children with Entity Framework in one query.

Simple Code · Accepted Answer · 2018-03-11 09:29:54Z

Let's say we have the following self-referencing table:

public class Person
{
    public Person()
    {
        Childern= new HashSet<Person>();
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public int? ParentId { get; set; }


    [StringLength(50)]
    public string Name{ get; set; }

    public virtual Person Parent { get; set; }

    public virtual ICollection<Person> Children { get; set; }

}

And for some point of time you need to get all grandsons for specific persons.

So, first of all I will create stored procedure(using code-first migration) to get all persons in the hierarchy for those specific persons:

public override void Up()
{
    Sql(@"CREATE TYPE IdsList AS TABLE   
                ( 
                Id Int
                )
                GO

                Create Procedure getChildIds(
                @IdsList dbo.IdsList ReadOnly
                )
                As
                Begin
                WITH RecursiveCTE AS
                (
                    SELECT Id
                    FROM dbo.Persons
                    WHERE ParentId in (Select * from @IdsList)
                    UNION ALL

                    SELECT t.Id
                    FROM dbo.Persons t
                    INNER JOIN RecursiveCTE cte ON t.ParentId = cte.Id
                )
                SELECT Id From RecursiveCTE
                End");
}

public override void Down()
{
    Sql(@" Drop Procedure getChildIds
           Go
           Drop Type IdsList
           ");
}

After that you can use Entity Framework to load the ids(you could modify stored procedure to return persons instead of only returning ids) of persons under the passed persons(ex grandfather) :

 var dataTable = new DataTable();
 dataTable.TableName = "idsList";
 dataTable.Columns.Add("Id", typeof(int));
 //here you add the ids of root persons you would like to get all persons under them
 dataTable.Rows.Add(1);
 dataTable.Rows.Add(2);
//here we are creating the input parameter(which is array of ids)
 SqlParameter idsList = new SqlParameter("idsList", SqlDbType.Structured);
 idsList.TypeName = dataTable.TableName;
 idsList.Value = dataTable;
 //executing stored procedure
 var ids= dbContext.Database.SqlQuery<int>("exec getChildIds @idsList", idsList).ToList();

I hope my answer will help others to load hierarchical data for specific entities using entity framework.

Collectives™ on Stack Overflow

Select entities with multiple and nested levels without using Include

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related