How to fill an ICollection inside an Entity

Question

This has to have been asked before, but I must not be using the right terminology in my searches because I can't find it. I'm trying to figure out how to assign a collection of objects from a LINQ query.

Consider the following DB Schema:

I want to query for a specific Parent and have it include any and all Child children that relate to it, together with the Type and Status for that Child. So far, that part is easy--I could just do something like this:

var result = context.Parents
                    .Where(p=>p.ParentID == idToSearchFor)
                    .Include(p=>p.Child.Select(c=>c.Type))
                    .Include(p=>p.Child.Select(c=>c.Status))

and (assuming I don't have any typos) that would give me everything right there, clean and simple.

HOWEVER

Assume that GenericLink.EntityID can point to Parent when EntityTypeID is the ID for Parent type. I want to include data from SubItems if there are any GenericLink objects that relate to my parent. Well, since there isn't a relationship defined in the model between Parent and GenericLink, the Include method isn't going to work anymore.

For the actual scenario I'm in, the best thing to do is to make a new DTO object and return that.

public class ParentDTO
{
   public int Data1;
   public int Data2;
   public string Data3;
   public ICollection<Child> Children;
   public ICollection<Tuple<int, string> SubItemHeaders;
}

Now, here's the part I'm having a hard time with. I have the following query:

var result = from qParent in context.Parents.Where(p=>p.ParentID = idToSearchFor)
             join qChild in context.Children on qParent.ParentID equals qChild.ParentID
             join qType in context.Types on qChild.TypeID equals qType.TypeID
             join qStatus in context.Statuses on qChild.StatusID equals qStatus.StatusID
             from qGen in context.GenericLinks.Where(g=>g.EntityTypeID == PARENT_TYPE && g.EntityID == qParent.ParentID).DefaultIfEmpty()
             join qSub in context.SubItems on qGen.SubItemId equals qSub.SubItemID
             select new ParentDTO
             {
                Data1 = qParent.Data1,
                Data2 = qParent.Data2,
                Data3 = qParent.Data3,
                // The following don't work:
                //Children = qChild,
                //SubItemHeaders = new Tuple<int, string>(qSub.Data1, qSub.Data2)
             }

qChild is not an array of Child but here it only represents a single Child object despite the fact that in the DB, there are several Children associated to the parent. Although the SubItemHeaders manipulates the data a bit, it has the same problem.

So my question is How do I set the Children collection?

Note: although I'm using query syntax here, feel free to switch to fluent syntax in your answer if that's easier. I'm also open to an alternate approach as appropriate.

EDIT (Additional detail)

I would like to try and do this in a single round trip to the DB if possible and making changes to the DB itself (such as adding a new view) is highly undesirable.

Answer 1

Firstly, look to keep the concerns separate. A DTO should not hold references to entities, only other DTOs to avoid issues with entities outside of their context's lifetime scope & serializers.

Next, leverage entity relationship mapping. A Parent entity should have a collection of Children, where a Child has a many to one reference to both Type and Status. When these are mapped out you can reference this data and Select from it without explicit joins.

//Entities

public class Parent
{
    public int ParentId { get; set; }
    public string Data1 { get; set; }
    public string Data2 { get; set; }
    public string Data3 { get; set; }
    // ...
    public virtual ICollection<Child> Children { get; set; } = new List<Child>();
}

public class Child
{
    public int ChildId { get; set; }
    // ...
    public virtual Parent Parent { get; set; }
    public virtual ChildType ChildType { get; set; }
    public virtual Status Status { get; set; }
}

public class ChildType
{
    public int ChildTypeId { get; set; }
    // ...
}

public class Status
{
    public int StatusId { get; set; }
    // ...
}

Depending on whether you're using EF6 or EFCore you need to set up the navigation properties between the entities. If you use an entity name like ChildType but the Table is "Types" then you can map the table name and key name this way as well. (Avoid dancing around C# reserved keywords) To understand the relationship mapping have a search for examples of HasOne (EFCore) or HasRequired/HasOptional (EF6), HasMany, etc. EF can map many of these cases automatically by convention, but you may need to get a bit explicit so it doesn't hurt to know how mappings are declared.

Your DTOs will contain just the fields that you need from each entity. They can be 1-to-1 mappings of the entities if you need every field, or just subsets of the fields. They can also "flatten" entity structures, for instance you might want the status and "type" name listed with the Child rather than the normalized structure:

For instance:

[Serializable]
public sealed class ChildDTO
{
   public int ChildId { get; set; }
   public int ChildTypeId { get; set; }
   public string ChildTypeName { get; set; }
   public int StatusId { get; set; }
   public string StatusName { get; set; }
}

Then to select the parent(s) and children:

var parent = context.Parents.Where(x => x.ParentId == idToSearchFor)
    .Select(x => new ParentDTO
    {
        ParentId = x.ParentId,
        Data1 = x.Data1,
        Data2 = x.Data2,
        Data3 = x.Data3,
        Children = x.Children.Select(c => new ChildDTO
        {
            ChildId = c.ChildId,
            ChildTypeId = c.ChildType.ChildTypeId,
            ChildTypeName = c.ChildType.TypeName,
            StatusId = c.Status.StatusId,
            StatusName = c.Status.StatusName
        }).ToList()
    }).Single();

When dealing with dynamic data, things get a bit tricky. I'm not a huge fan of dynamic data structures as they don't use FK constraints so it's difficult to maintain data integrity. This poses performance problems as well when querying over large amounts of indirectly related details. IMHO having several nearly identical tables with FK constraints to their respective tables, or leveraging many to many linking tables doesn't cost anything significantly more space-wise, but keeps data integrity and offers better performance. That said, when faced with a dynamic structure like GenericLinks there are a couple options:

1. When dealing with loading links for a single entity, or a relatively small number of entities, load them separately:

eg.

var parent = context.Parents.Where(x => x.ParentId == idToSearchFor)
    .Select(x => new ParentDTO
    {
        ParentId = x.ParentId,
        Data1 = x.Data1,
        Data2 = x.Data2,
        Data3 = x.Data3,
        Children = x.Children.Select(c => new ChildDTO
        {
            ChildId = c.ChildId,
            ChildTypeId = c.ChildType.ChildTypeId,
            ChildTypeName = c.ChildType.TypeName,
            StatusId = c.Status.StatusId,
            StatusName = c.Status.StatusName
        }).ToList()
    }).Single();

var subItems = context.GenericLinks.Where(x => x.EntityTypeID == PARENT_TYPE 
    && x.EntityID == idToSearchFor)
   .Select(x => new SubItemDto 
   { 
       Data1 = x.SubItem.Data1,
       Data2 = x.SubItem.Data2,
       // ... 
   }).ToList();

parent.SubItems = subItems;

** edit: corrected above statement. Subitem to GL is 1-to-1 or many-to-1 so Select not SelectMany

When using this approach, note that there is no direct relationship in the entities for sub items on the parent. The relationship is only at the DTO level. These entities are loosely related. It can be possible to map loose relationships like this, but it can get tricky given that the PK (ParentId) forms a composite relationship on the GenericLink (EntityId + EntityType of "Parent")

2. When dealing with a larger set of data, such as search results, leverage a database View then bind specific entities to the View rather than tables. This is a good case for using a bounded context, basically a single-purpose context for these types of searches. The view returns just the data that the search or report needs, along with the relevant IDs. If you then want to load the details of one of the results (I.e. Edit page) then you give the applicable ID to your main entity context to load the relevant entity. The view manages the rules for loading the related links/sub items for the applicable parent. The limitation of using entities bound to views is that these entities would be read-only. You would need to use the regular Parent and Generic Link entities to make updates.

Edit: Option 3 - Mapping the loosely coupled elements. As mentioned above you can leave the relationship loose and use EF to join GenericLinks. It can get messy though. I pretty much exclusively use the Fluent Linq methods rather than Linq syntax, but the below should be translatable:

Starting with the base of our query:

var parent = context.Parents.Where(x => x.ParentId == idToSearchFor)

Before selecting our data, we can join in the GenericLinks:

var parent = context.Parents
    .Join(context.GenericLinks, 
         p => p.ParentId, 
         g => g.EntityId, 
        (p,g) => new {Parent = p, GenericLink = g})

Except this won't work because generic links are typed so it would link GenericLinks for Parents and Children and other entity types, so we filter the Generic links on join:

var parent = context.Parents
    .Join(context.GenericLinks.Where(g=> g.EntityTypeID == PARENT_TYPE), 
         p => p.ParentId, 
         g => g.EntityId, 
         (p,g) => new {Parent = p, GenericLink = g})

From there we will have a set of Parent + Generic Link. That relationship is 1 to many, so we will need to group

var parent = context.Parents
    .Join(context.GenericLinks.Where(gl => gl.EntityTypeID == PARENT_TYPE), 
         p => p.ParentId, 
         gl => gl.EntityId, 
         (p,gl) => new {Parent = p, GenericLink = gl})
   .Where(x => x.ParentId == idToSearchFor)
   .GroupBy(x => x.Parent)

From here we're pretty close to where we were before. The whole thing would look something like:

var parent = context.Parents
    .Join(context.GenericLinks.Where(gl => gl.EntityTypeID == PARENT_TYPE), 
         p => p.ParentId, 
         gl => gl.EntityId, 
         (p,gl) => new {Parent = p, GenericLink = gl})
   .Where(x => x.ParentId == idToSearchFor)
   .GroupBy(x => x.Parent)
   .Select(x => new ParentDTO
    {
        ParentId = x.Key.ParentId,
        Data1 = x.Key.Data1,
        Data2 = x.Key.Data2,
        Data3 = x.Key.Data3,
        Children = x.Key.Children.Select(c => new ChildDTO
        {
            ChildId = c.ChildId,
            ChildTypeId = c.ChildType.ChildTypeId,
            ChildTypeName = c.ChildType.TypeName,
            StatusId = c.Status.StatusId,
            StatusName = c.Status.StatusName
        }).ToList(),
        SubItems = x.Select(g => new SubItemDto 
        { 
           Data1 = g.SubItem.Data1,
           Data2 = g.SubItem.Data2,
           // ...
        }).ToList();
    }).Single();

Like I said, ugly... :) I'm building the above from memory so it most likely needs some tweaking, but the key points:

• We filter the GenericLinks by EntityType on the Join, not inside the ON equivalent expression like we would in SQL.
• We'll receive a Cartesian of parent to generic link so we need to GroupBy the result back on Parent.
• When dealing with the grouped results, the expression to fill our DTO doesn't change much, just now we need to deal with x.Key to reference the Parent.
• x in that grouped results would refer to our GenericLinks set, so we Select our SubItem from that grouped set. I corrected the example in #1 as I had that as a SelectMany but a GenericLink has a single SubItem.