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

UPDATE

As others pointed out, flattening the results (as shown below) is not needed when working with Entity Framework 4.0, since it can translate the LINQ query to an efficient flattened result for you. Therefore, the following code is only needed when working with LINQ to SQL (or possibly other LINQ providers). Note that I have only tested this with EF over SQL Server and not over Oracle, since this behavior could be LINQ provider specific, which means that the Oracle provider (still in beta) or the commercial Devart provider for Oracle could still be doing N + 1.

---

What you are trying to do is to get a set of objects that are structured like a tree. Without any special care, you will be triggering many queries to the database. With one level of nesting you would be triggering N + 1 queries, but since your nesting is two levels deep, you will be triggering M x (N + 1) + 1 queries, which will almost certainly be very bad for performance (no matter what the size of your data set is). What you want is to make sure that there is only a single query sent to the database. To ensure this, you must create an intermediate query that flattens the result, just as you would have done in the good old SQL days, to retrieve tree like data :-). Take a look at the following example:

var records =
    from record in db.TableC
    where ... // any filtering can be done here
    select record;

// important to call ToArray. This ensures that the flatterned result
// is pulled in one single SQL query.
var results = (
    from c in records
    select new
    {
        tableA_rowid = c.B.A.Id,
        tableA_Prop1 = c.B.A.Property1,
        tableA_Prop2 = c.B.A.Property2,
        tableA_PropN = c.B.A.PropertyN,
        tableB_rowid = c.B.Id,
        tableB_Property1 = c.B.Property1,
        tableB_Property2 = c.B.Property2,
        tableB_PropertyN = c.B.PropertyN,
        tableC_rowid = c.Id,
        tableC_Property1 = c.Property1,
        tableC_Property2 = c.Property2,
        tableC_PropertyN = c.PropertyN,
    })
    .ToArray();

The next step is to transform that in-memory data structure (using that anonymous type) into the tree structure of DTO objects:

// translate the results to DTO tree structure
TableA_DTO[] dtos = (
    from aresult in results
    group aresult by aresult.tableA_rowid into group_a
    let a = group_a.First()
    select new TableA_DTO
    {
        tableA_rowid = a.tableA_rowid,
        tableA_Prop1 = a.tableA_Prop1,
        tableA_Prop2 = a.tableA_Prop2,
        TableB_records = (
            from bresult in group_a
            group bresult by bresult.tableB_rowid into group_b
            let b = group_b.First()
            select new TableB_DTO
            {
                tableB_rowid = b.tableB_rowid,
                tableB_Prop1 = b.tableB_Prop1,
                tableB_Prop2 = b.tableB_Prop2,
                TableC_records = (
                    from c in group_b
                    select new TableC_DTO
                    {
                        tableC_rowid = c.tableC_rowid,
                        tableC_Prop1 = c.tableC_Prop1,
                        tableC_Prop2 = c.tableC_Prop2,
                    }).ToList(),
            }).ToList()
     })
    .ToArray();

As you can see, the first part of the solution is actually the 'old' way of doing this, way back when we would still write our SQL queries by hand. Nice however is, that once we get this typed set of in-memory data, we can leverage LINQ (to Objects) again to get this data in the structure we want.

Note that this also allows you to do paging and sorting. This will be a bit more tricky, but certainly not impossible.

Answer 2

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 3

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.

Answer 4

I would make a factory method, ie: TableA_DTO CreateDTO(TableAItem item);

Using this, you could just rewrite your query as:

IEnumerable<TableA_DTO> = TableA.AsEnumerable().Select(CreateDTO);

This would give you the collection of "DTO" objects directly.

That being said, if you're using Entity Framework, the EF Code First added in recent versions might be more useful in this case.