Mapping Linq Query results to a DTO class

Question

I want to get records from the database using EF and assign the values to a DTO class.Consider the following tables for a Linq query.

TableA,TableB, TableC

For each TableA record there are multiple records in TableB. For each TableB record there are multiple records in TableC. Now my DTOs look like this

public class TableA_DTO
{
    public int tableA_rowid { get; set; }
    //remaining tableA field definitions

    public List<TableB_DTO> TableB_records { get; set; }
}

public class TableB_DTO
{
    public int tableB_rowid { get; set; }
    //remaining tableB  field definitions

    public List<TableC_DTO> TableC_records { get; set; }
}

public class TableC_DTO
{
    public int tableC_rowid { get; set; }
    //remaining tableC field definitions
}

my linq query looks something like this

var qry = from ent in TableA
          select ent;

In my mapping class I loop through items in query result like so:

    foreach (var dataitem in query)
    {
        TableA_DTO dto = new TableA_DTO();
        dto.tableA_rowid =  dataitem.ID;
        //remaining field definitions here
    }

Now this works for all fields in TableA where it brings out one record from the database and sets the required properties in TableA_DTO for each field in the table TableA. I want to also populate all matching records in TableB in the TableA property field by the name TableB_records and also in TableB_DTO all the matching records from TableC in TableB_DTO's property by the name TableC_records

Can this be done? What do I need to change? Is it the linq query or the way I do my mapping

Thanks for your time...

Answer 1

I would change your DTO from List to IEnumerable and than do everything in a LINQ query.

var query = 
    from ent in TableA
    select new TableA_DTO
    {
        TableAProperty = a.Property,
        TableB_records = 
            from b in TableB
            where ent.Key == b.Key
            select new TableB_DTO
            {
                TableBProperty = b.Property,
                TableC_records =
                    from c in TableC
                    where b.Key == c.Key
                    select new TableC_DTO
                    {
                        TableCProperty = c.Property
                    }
            }
    };

Answer 2

First thing, I just need to ask whether you can use Entity Framework 4.1 and POCOs (DbContext) and avoid the need for DTO's altoghther?

Assuming that the answer is no, that must be because you are not pulling back all of the fields, or you are somehow altering the "shape" of the data.

In that case, you could change your LINQ query to look something like this:

from t in table
where ...
select new DTOA()
{
  TheDtoProperty = theTableProperty,
  AndSoOn = AndSoOn
};

The benefit of doing it this way: If you turn on SQL Profiler, you should see that only the columns that you request make it into the actual SQL query. If you query it all first and then pull the values, all of the columns will be pulled down the wire.