Hierarchical Entity Framework Query Exception

Question

I am attempting to build up a hierarchical collection using Entity Framework - see the below query - every member in a given company has a parent member - but when trying to execute this I get the following exception:

System.NotSupportedException: The type 'Member' 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.

If I remove the ParentMember assign it works - any ideas on what is going on?

        return from c in _Entities.Company
               where c.Deleted == false
                select new Member()
                {
                    Name = c.Name,
                    ParentMember = new Member() 
                    {
                        Name = c.ParentMember.Name
                    }
                }; 

Answer 1

Try

return (from c in _Entities.Company
               where c.Deleted == false
                select new
                {
                    c.Name,
                    ParentMember = new
                    {
                        c.ParentMember.Name
                    }
                })
.AsEnumerable()
.Select(c=> new Member
                {
                    Name = c.Name,
                    ParentMember = new Member
                    {
                        Name = c.ParentMember.Name
                    }
                }); 

Answer 2

I haven't tried this, but the error message gives you a clue: you're not setting the same properties in the same order in both places.

What happens if you try setting an ID property on the outer Member()?

Answer 3

You will end up with a recursion of Member records when you try to retrieve the same fields in each one. You can't just make the last parent record equal to null.

I would retrieve what I could and then build up the record with further queries. Note that your Company entity will require a ParentId field or similar.

var members = 
  return from c in _Entities.Company
  select new Member()
  {
    Name = c.Name,
    ParentId = c.ParentId
  }; 

Now iterate and add in the parent records.

foreach (var member in members)
{
  member.ParentMember = new Member 
    {
      Name = _Entities.Company.First(c => c.Id == member.ParentId).Name
    };
}