Querying and mapping complex objects using Linq-to-SQL

Question

I have the following two tables:

ChildTable
ID  ManyColumns ParentID
1   XXXX        1
2   YYYY        1
3   ZZZZ        4

ParentTable
ID  Name
1   aaaa
2   bbbb
3   cccc
4   dddd

I have the following classes representing the above tables:

public class Child  
{
    public string ID { get; set; }
    public string ManyColumns { get; set; }
    public string ParentID { get; set; }
}

public class Parent
{
    public string ID { get; set; }
    public string Name { get; set; }
}

BUT for data transfer, I have the respective classes:

public class ChildDTO  
{
    public string ID { get; set; }
    public string ManyColumns { get; set; }
    public ParentDTO Parent { get; set; } //Here is the only IMPORTANT difference
}

public class ParentDTO  
{
    public string ID { get; set; }
    public string Name { get; set; }
}

How can I use LINQ-to-SQL in way I can resolve Child to ChildDTO with:

• Minimum queries
• Minimum Mappings

I know I could use this option:

List<ChildDTO> ChildDTOs = (from C in context.Childs
        join P in context.Parents on C.ParentId equals P.Id
        select new ChildDTO(){
            ID = C.ID,
            ManyColumns = C.ManyColumns,
            Parent = P});

But, I am trying to avoid this multiple mappings I have to do on Select statement. Also, Child Class is constantly changing in the current Beta Phase. So, if I use the options above, I have to constantly update those mappings.

To make it easier for coding, I am using AutoMapper in this way

AutoMapper.Mapper.CreateMap<Child, ChildDTO>()
    .ForMember(dst => dst.Parent, opt => opt.ResolveUsing<Resolver_ParentId_to_Parent>().FromMember(src => src.ParentId))

public class Resolver_ParentId_to_Parent : ValueResolver<string, ChildDTO>
{
    protected override ChildDTO ResolveCore(string source)
    {
        return (from P in context.Parents 
                Where P.Id = source.ToString()
                select item).FirstOrDefault();
    }
}

Then, I can simply Map it:

List<Child> Childs = (from C in context.Childs select C);
List<ChildDTO> newChildDTOs = AutoMapper.Mapper.Map<List<ChildDTO>>(Childs);

This is good because:

• Fast
• Clean
• Minimum code effort... EVEN when Child Table has changed, by adding or deleting a column. You are always good to go as long as Class Child and Table Child are up-to-date with same properties/column names. AutoMapper does the trick.

The downside:

• I am hammering SQL with multiples "ResolveCore" for each new ChildDTO.

What you guys think? Is there a magic way to resolve Child to ChildDTO in 1 shot only, without manual mappings, without hammering SQL? Just dreaming:

List<ChildDTO> ChildDTOs = (from C in context.Childs
    join P in context.Parents on C.ParentId equals P.Id
    select SuperConversor(new ChildDTO())).ToList;

Answer 1

I would go with your approach using the automapper with a modification. Add a Parent member to your Child class. Then you can load it in the query directly (Loading Related Entities) so you will have it in the Child instance without using the automapper resolver and without having multiple queries for a single child.

Also, you could consider other mappers. We switched from using automapper to emitmapper, since we found it was performing ~10 times faster. But that requires some testing, we had complex classes going down several levels.