Is this the right way of using 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 aside from SQL server Databases in our solution.

I am doing some research with dapper so I created a quick test environment with some fictitious scenario.

I have the following POCOs that resemble my Database schema (I have more but I limited to showing these for 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 am using the following in a repository to return a list of all the adopters:

        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 am retrieving the data for each POCO individually based on the previous one and doing the Joins manually in code.

Is this the right way of doing it or should I be doing a big query with multiple joins and mapping the result somehow thru dapper and LINQ?

Answer 1

A possible improvement to your actual solution is through the use of QueryMultiple extension like this:

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 benefit here is that you reach the database just one time and then process everything in memory.

However this could be a performance hit and a memory bottleneck if you have a really big data to retrieve, (and from a remote location). Better to look closely at this approach and try also some kind of Async processing and/or pagination if possible.