How do I populate a Data Access Layer Model Efficiently?

Question

I'm working on developing my first Data Driven Domain using Dependency Injection in ASP.net.

In my Data Access Layer if have created some domain data models, for example:

public class Company {
   public Guid CompanyId { get; set; }
   public string Name { get; set; }
}

public class Employee {
   public Guid EmployeeId { get; set; }
   public Guid CompanyId { get; set; }
   public string Name { get; set; }
}

I have then developed an interface such as:

public interface ICompanyService {
   IEnumerable<Model.Company> GetCompanies();
   IEnumerable<Model.Employee> GetEmployees();
   IEnumerable<Model.Employee> GetEmployees(Guid companyId);
}

In a separate module I have implemented this interface using Linq to Sql:

public class CompanyService : ICompanyService {

    public IEnumerable<Model.Employee> GetEmployees();
    {
        return EmployeeDb
            .OrderBy(e => e.Name)
            .Select(e => e.ToDomainEntity())
            .AsEnumerable();
    }
}

Where ToDomainEntity() is implemented in the employee repository class as an extension method to the base entity class:

public Model.EmployeeToDomainEntity()
{
     return new  Model.Employee {
         EmployeeId = this.EmployeeId,
         CompanyId = this.CompanyId,
         Name = this.Name
     };
}

To this point, I have more or less followed the patterns as described in Mark Seeman's excellent book 'Dependency Injection in .NET' - and all works nicely.

I would like however to extend my basic models to also include key reference models, so the domain Employee class would become:

public class Employee {
   public Guid EmployeeId { get; set; }
   public Guid CompanyId { get; set; }
   public Company { get; set; }
   public string Name { get; set; }
}

and the ToDomainEntity() function would be extended to:

public Model.Employee ToDomainEntity()
{
     return new  Model.Employee {
         EmployeeId = this.EmployeeId,
         CompanyId = this.CompanyId,
         Company = (this.Company == null) ? null : this.Company.ToDomainEntity()
         Name = this.Name
     };
}

I suspect that this might be 'bad practice' from a domain modelling point of view, but the problem I have encountered would also, I think, hold true if I were to develop a specific View Model to achieve the same purpose.

In essence, the problem I have run into is the speed/efficiency of populating the data models. If I use the ToDomainEntity() approach described above, Linq to Sql creates a separate SQL call to retrieve the data for each Employee's Company record. This, as you would expect, increases the time taken to evaluate the SQL expression quite considerably (from around 100ms to 7 seconds on our test database), particularly if the data tree is complex (as separate SQL calls are made to populate each node/sub-node of the tree).

If I create the data model 'inline...

public IEnumerable<Model.Employee> GetEmployees();
{
    return EmployeeDb
        .OrderBy(e => e.Name)
        .Select(e => new Model.Employee {
            EmployeeId = e.EmployeeId,
            /* Other field mappings */
            Company = new Model.Company {
               CompanyId = e.Company.CompanyId,
               /* Other field mappings */
            }
        }).AsEnumerable();
}

Linq to SQL produces a nice, tight SQL statement that natively uses the 'inner join' method to associate the Company with the Employee.

I have two questions:

1) Is it considered 'bad practice' to reference associated data classes from within a domain class object?

2) If this is the case, and a specific View Model is created for the purpose, what is the right way of populating the model using without having to resort to creating inline assignment blocks to build the expression tree?

Any help/advice would be much appreciated.

Answer 1

The problem is caused by having both data layer entities and domain layer entities and needing a mapping between the two. Although you can get this to work, this makes everything very complex, as you are already experiencing. You are making mappings between data and domain, and will soon add many more mappings for these same entities, because of performance reasons and because other business logic and presentation logic will need different data.

The only real solution is to ditch your data entities and create POCO model objects that can directly be serialized to your backend store (SQL server).

POCO entities is something that is supported in LINQ to SQL from day one, but I think it would be better to migrate to Entity Framework Code First.

When doing this, you can expose IQueryable<T> interfaces from your repositories (you currently called your repository ICompanyService, but a better name would be ICompanyRepository). This allows you to do efficient LINQ queries. When querying directly over a query provider you can prevent loading complete entities. For instance:

from employee in this.repository.GetEmployees()
where employee.Company.Name.StartWith(searchString)
select new 
{
    employee.Name, 
    employee.Company.Location 
};

When working with IQueryable<T>, LINQ to SQL and Entity Framework will translate this to a very efficient SQL query that only returns the employe name and company location from the database with filtering inside the database (compared to do filtering in your .NET application when GetEmployees() returns an IEnumerable<T>).

Answer 2

You can ask Linq2Sql to preload certain entities (as opposed to lazy load them) using DataLoadOptions.LoadWith method see: http://msdn.microsoft.com/en-us/library/bb534268.aspx. If you do this with the Company entity then I think Linq2Sql won't have to reach to the database to fetch it again.