Linq mapping EntityDb to Dto is very slow, one record

Question

Linq mapping EntityDb to Dto is very slow.

I get the only ONE record from table with join other tables by Id.

Example:

DataAccess da;

var res = from t1 in da.Table1
          join t2 in da.Table2 on t1.rf_table2Id equals t2.Table2Id
          join etc...
          over 20 joins...

          where t1.Table1Id == 20 /*example*/
          select new MyDto
          {
            Id = t1.Table1Id,
            Name = t1.Name,
            Type = new ReferenceDto() 
            {
                Id = t2.Table2Id,
                Name = t2.Name
            },

            and etc...
            over 50 fields
          };

The problem in mapping. If I get the record without mapping all fields, only Id, res.FirstOrDefault() executes in 100-500 milliseconds, fast.

But, if I map all fields the res.FirstOrDefault() takes 3 seconds to execute, which is too slow.

My DTO is structure view.

In SQL Server Profiler, the query runs very fast.

What can I do ?

I need to get more information at the one time, by Id the record.

Answer 1

Solution is:

1) I get the original sql-query from SQL Server Profiler.

2) Create plain DTO class (over 200 fields) for execute query by context.ExecuteStoreQuery.

3) Then, I've done mapping the result plain DTO to my structure DTO Model.

Conclusion is:

About 3 seconds to execute linq statement with mapping to big structure DTO with about 200 fields.

And ONLY 500-600 milliseconds with new solution, described above!!!

Improved by 5-6 times!!!

With large DTO structure linq statement is bad, when we want to get the large record data (with many tables) by Id.

P.S. Structure DTO Model is class with sub-classes, example:

 select new MyDto
          {
             Id = t1.Table1Id,
             Name = t1.Name,
             Type = new ReferenceDto() 
             {
                Id = t2.Table2Id,
                Name = t2.Name
             },

             and etc...
             over 50 fields
          };