Can I use Dapper to map SQL tables with multiple relationships?

Question

I'm developing a C# WPF desktop application where I need to read/write to an SQL database (SQL server) regularly. Now I want to map the data from the database to objects in C#. I can't use Entity Framework so I'm doing all my data access through Dapper and stored procedures.

As an example, I have modeled this sample database

The C# objects would look similar to this.

public class Manager {

   public string Name { get; set; }
   public string Phone { get; set; }
   public List<Facility> Facilities {get; set;}
} 

public class City {

   public string Name { get; set; }
   public string Description{ get; set; }
   public List<Facility> Facilities {get; set;}
} 

public class Facility {

   public string Name { get; set; }
   public string Description{ get; set; }
} 

I have tried to map the data with slapper automapper but it didn't work. Can I use Dapper to map all these ? Do i even need to map every Table to a class in C# with its relationships ? Or could I just write a stored procedure that returns all the entries already matched and create one big class with all the data as properties ?

Answer 1

You can use Dapper to make it work and you have several options:

First option

You can query parent entity and then query all child entities. The code will look like this:

var manager = await connection.QueryFirstOrDefaultAsync<Manager>("SELECT * FROM Manager AS m WHERE m.Name = @name", new {name = name}); // use your query and your parameters

manager.Facilities = await connection.QueryAsync<Manager>("SELECT * FROM Facilities AS f WHERE f.ManagerId = @managerId", new {managerId = manager.ManagerId}); 

// use similar queries to get Cities

Second option

You can use multimapping to get multiple entities (but you should know, that it allows to query up to 7 dependent entities - for your case it will be enough):

public async Task<IEnumerable<Manager>> GetManagerWithFacilitiesByManagerName(string name)
{
    var managersDictionary = new Dictionary<int, Manager>();

    await connection.Query<Manager, Facility, Manager>(
       @"SELECT * 
         FROM Manager AS m 
         INNER JOIN Facilities as f ON f.ManagerId = m.ManagerId
         WHERE m.Name = @name", 
       (manager, facility) => 
       { 
           Manager managerEntity;

           if (managersDictionary.ContainsKey(manager.ManagerId)
           {
               managerEntity = managersDictionary[manager.ManagerId];
           } 
           else 
           {
               managerEntity = manager;
               managerEntity.Facilities = new List<Facilities>();
               managersDictionary.Add(managerEntity.ManagerId, managerEntity);
           }

           managerEntity.Facilities.Add(facility);

           return managerEntity;
       }
       new {name = name},
       splitOn: "ManagerId,FacilityId") // properties where to split entity

    return managersDictionary.Values;    
} 

If you want only first Manager just use managersDictionary.Values.FirstOrDefault() and change return type to Task<Manager>.