Is this the right way to use Dapper, or am I doing it all wrong?

Question

I am trying to get away from the Entity Framework since I have to support HANA databases other than SQL Server databases in our solution.

I do some research using dapper, so I created a quick test environment with some kind of dummy script.

I have the following POCOs that resemble my database schema (I have more, but I limited myself to their simplicity):

public class Adopter { public int Id { get; set; } public string FirstName { get; set; } public string LastName { get; set; } public string Address { get; set; } public string Address2 { get; set; } public string City { get; set; } public State State { get; set; } public int StateId { get; set; } public string Zip { get; set; } public string Email { get; set; } public string Phone { get; set; } public string Fax { get; set; } public IEnumerable<Pet> Pets { get; set; } } public class State { public int Id { get; set; } public string Name { get; set; } public string Abreviation { get; set; } } public class Pet { public int Id { get; set; } public string IdTag { get; set; } public string Name { get; set; } public DateTime AdmitionDate { get; set; } public Status Status { get; set; } public int StatusId { get; set; } public string Notes { get; set; } public DateTime AdoptionDate { get; set; } public bool IsAdopted { get; set; } public int? AdopterId { get; set; } public int Age { get; set; } public decimal Weight { get; set; } public string Color { get; set; } public Breed Breed { get; set; } public int BreedId { get; set; } public Gender Gender { get; set; } public int GenderId { get; set; } public IEnumerable<PetImage> PetImages { get; set; } } public class Status { public int Id { get; set; } public string Name { get; set; } public string Description { get; set; } } public class Gender { public int Id { get; set; } public string Name { get; set; } } 

I use the following in the repository to return a list of all adoptive parents:

  using (SqlConnection connection = new SqlConnection(_connectionString)) { var adopters = connection.Query<Adopter>("SELECT a.* FROM Adopters a"); foreach (var adopter in adopters) { adopter.State = connection.QueryFirst<State>("Select s.* FROM States s WHERE s.Id = @Id", new { Id = adopter.StateId }); adopter.Pets = connection.Query<Pet>("Select p.* FROM Pets p WHERE p.AdopterId = @Id", new { Id = adopter.Id }); foreach (var pet in adopter.Pets) { pet.Status = connection.QueryFirst<Status>("Select s.* FROM Status s WHERE s.Id = @Id", new { Id = pet.StatusId }); pet.Gender = connection.QueryFirst<Gender>("Select g.* FROM Genders g WHERE g.Id = @Id", new { Id = pet.GenderId }); } } return adopters; } 

As you can see, I extract the data for each POCO individually in accordance with the previous one and manually make Joins in the code.

Is this the right way to do this or should I make a large query with multiple joins and match the result somehow through dapper and LINQ?

Answer 1

A possible improvement to your actual solution is to use the QueryMultiple extension as follows:

 using (SqlConnection connection = new SqlConnection(_connectionString)) { string query = @"SELECT * FROM Adopters; SELECT * FROM States; SELECT * FROM Pets; SELECT * FROM Status; SELECT * FROM Genders;"; using (var multi = connection.QueryMultiple(query, null)) { var adopters = multi.Read<Adopter>(); var states = multi.Read<State>(); var pets = multi.Read<Pet>(); var statuses = multi.Read<Status>(); var genders = multi.Read<Gender>(); foreach (Adopter adp in adopters) { adp.State = states.FirstOrDefault(x => x.Id == adp.StateID); adp.Pets = pets.Where(x => x.IsAdopted && x.AdopterID.HasValue && x.AdopterID.Value == adp.AdopterID) .ToList(); foreach(Pet pet in adp.Pets) { pet.Status = statuses.FirstOrDefault(x => x.Id == pet.StatusID); pet.Gender = genders.FirstOrDefault(x => x.Id == pet.GenderID); } } } } 

The advantage here is that you reach the database only once and then process everything in memory.

However, it can be a performance hit and a memory bottleneck if you have really big data to retrieve (and from a remote location). Take a closer look at this approach and try, if possible, some kind of asynchronous and / or pagination processing.

Answer 2

I do not like to be negative, but ... do not do this! Do not even think so. You want to reset the EF, but you go into the trap, wanting to emulate the EF. The bridge between your application and your database is not something that needs to be built once at a time, for every conceivable goal. Specifically, you should never return the entire table, and, of course, should not loop on each row and generate more queries. You may feel unfairly criticized, you just check the tools! If so, then perhaps tell us which aspect of the instrument you are studying, and we will focus on that.

Dapper or QueryFirst greatly simplifies query execution and consumes results, so only return what you need, only when you need It. Then denormalize a bit, for specific work in hand. Why are there no joins in your queries? RDBMSs are amazing and amazingly good at combining. If you join data outside the database, crazy is the only word, even if Linq gives you super (sql-like) syntax for that. The senseless assumption that 1 table corresponds to 1 class is the beginning of many problems.