DataAccess layer to return domain objects in case of Ado.net?

Question

I am creating a project in which I will using ADO.NET as data access layer to interact with database.

Now the thing where i am pretty much confused is with :

1) Shall I have domain objects in this application?

2) Does my sql query result should always be binded with domain objects ?

3) If I dont use domain objects then shall I always return custom models from data access layer which have everything that I want to return in my web api service?

4) If I use domain models and if there is a scenario where i want to display data from multiple tables or scenario like this for eg :

public class Employee
   {
      int id;
      List<Skills>();
   }

I can easily do this in EF but with ado.net and with domain object which would have structure like below how I will achieve this :

public class Employee
   {
       int id;
   }

   public class Skill
   {
       int id;
       int EmployeeId;
   }

Ofcouse I can first get List of employee and then for each employee i can get list of skills based on employee id but isnt this will be painfull that i will have to fire query for each employee to get its corresponding skills which is quite simple in EF based on navigation property and avoiding overhead something like below :

var employees = //get listof employee in this case domain model 
                  List<Employee>
var employeeModel = new List<EmployeeModel>();
foreach(var employee in employees)
{
   EmployeeModel model = new EmployeeModel();
   model.id = employee.id;
   var skills = GetSkill(employee.id);//Get list of skills in this case 
                domain model List<Skill>;
   employeeModel.Skills =  new List<SkillModel>();
   foreach(var skill in skills)
   {
      SkillModel sm = new SkillModel();
      sm.Id = skill.Id;
      employeeModel.Skills.Add(smm);
   }
   employeeModel.Add(model);
}

Finally this EmployeeModel will be returned as response in my Web Api service hence this EmployeeModel will hold only those properties that I will return in my WebApi endpoint.

What should be the architecture that are being considered while working with ado.net as data access layer and I will really appreciate if someone could please help me address above 4 concerns.

Note : I do not want to use ORM(Entity Framework or Dapper etc).

Answer 1

I just wanted to provide you with another alternative for scenario 4.

4) If I use domain models and if there is a scenario where i want to display data from >multiple tables or scenario like this

Take a look at the answer I provided in the following question: Loading related objects in memory (without an ORM)

I'm using anonymous types and LINQ to map the data for a query across multiple tables.

Answer 2

I agree with Tengiz answer. However, You can use Active Record pattern and if by domain object you mean you would like to use a domain driven design approach then your DAL should abstract the data access logic inside and you better to use Repository pattern for each domain aggregate object. Also, you can separate your models to read and write then you can use CQRS pattern.

In any case you need to run your sql commands/stored procedures in your Data access objects and then populate your domain objects from the ADO.NET datareader, datasets, datatable and expose them to your domain model.

As a side note, DTO as its name stands is used to communicate with your internal domain and it is a good practice to separate your domain objects from DTOs (Data Contracts).

Answer 3

I will try to give more specific answers which should give you practical guidelines.

1) Shall I have domain objects in this application?

It depends. For very simple applications you don't need it. For more complex applications, it's absolutely recommended.

2) Does my sql query result should always be binded with domain objects ?

Yes if you are going to have the domain model. No if you don't have a domain model or if you are trying to return a simple value (e.g. integer).

3) If I dont use domain objects then shall I always return custom models from data access layer which have everything that I want to return in my web api service?

If you don't have the domain model, then your DAL becomes direct servant of the web api service. So, whatever web api needs, it needs to get from DAL. You can return all that in one go (one method call), or you can have api call DAL through several method invocations. Obviously, retrieving everything in one go is better, but that better fits into the service layer. DAL should be returning aggregate at a time. But without domain model and more advanced approaches, this can be too much to think about.

4) If I use domain models and if there is a scenario where i want to display data from multiple tables or scenario like this

I would run a SQL query with join. Then I would iterate over the rows and create the parent and child objects from rows, and then I would return the object that way. In fact, that's what Entity Framework does with navigation properties - it eager loads the whole structure using join. It does not execute several queries, and you should not either, if you want to optimize it to the max.

Let me know if you have questions about anything that I mentioned.

Answer 4

Creating Domain Model in application is good approach. You just need to bind the data to the models. Create models as per database table. If any table have reference to other include its reference in the model as well it will make easy for you to map the data to model. I would recommend you to use micro ORM it will help you in mapping data to the models else you would need to map you self.

Answer 5

1) Shall I have domain objects in this application?

only you can answer on this; right answer highly depends on what you do with database query results. If you have some fields-specific logic in your C# code it has sense to use DTOs (POCO entity classes). But sometimes you might want just execute some query and return results as JSON, and in this case DTOs may be overkill.

2) Does my sql query result should always be binded with domain objects ?

no, this is up to you. You can use DbDataReader directly to handle query results, or load them into DataTable (or similar more lightweight structure that can be offered by your data access library).

3) If I dont use domain objects then shall I always return custom models from data access layer which have everything that I want to return in my web api service?

If you don't use POCO models you can compose JSON in your code and return it as ActionResult.

4) If I use domain models and if there is a scenario where i want to display data from multiple tables

If you don't use ORM like EF 2 possible cases here:

• if this is result of 2 (or more) joined tables: for this purpose you can add special POCO model exactly for this query result, or use generic data container like DataTable to handle the result
• if this is parent-child (1-n): execute 2 queries and get result as 2 POCO-model collections (say, List<Employee> and List<Skills> of these employees). Then, if you need to access childs of concrete 'parent' entity perform additional filtering with LINQ.

If you don't want to use EF or Dapper, you can also check my library that can be used either with POCO models or with generic structures like DataTable (in addition to that it offers RecordSet structure).

Answer 6

Entity Framework and lazy loading of referenced objects is a reasonable approach. To extend your model, consider the following.

Create a SQL Server View that links the Employee and Skills table and add this to the EF model. The view can be as minimal or maximal as you want (e.g. minimal as in just the object ID's or maximal as in all fields).

From this view, select all records with a particular skill ID and you now have the Employees you require. Since the view is compiled in SQL Server it will be as quick as you can make it.

You can also join the Employee and Skill objects in your code and return just the employees. In LINQ you can view the SQL generated for your code