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 ?

@Reniuz Sorry forgot to link my attempt with slapper, fixed it now — maxiangelo, Apr 09 '19 at 13:26

Roman · Accepted Answer · 2019-04-09T13:34:48.503

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>.

Thank you for your response. Wouldn't that create duplicate data (Facilities), in Manager and City ? — maxiangelo, Apr 09 '19 at 13:38
@maxiangelo, everything depends on your data and query. If there is facility for queried city and for queried manager then both will contain this facility in nested entities. If you do not want such behavior you can change query to filter all duplicates — Roman, Apr 09 '19 at 13:42
What if i wanted all Facilities with their Managers and City's would i need to create a seperate data model for that ? — maxiangelo, Apr 09 '19 at 13:58
@maxiangelo, no, you should just add `Manager` and `City` properties in `Facility` entity and proceed with one of the option. — Roman, Apr 09 '19 at 14:00
So basicly also include the foreign keys form the Facility table in the Facility entity ? How would i go from Facility's to Managers then, just search the list of Managers ? Thank you for your help @Roman Doskoch — maxiangelo, Apr 09 '19 at 14:14
@maxiangelo, yeah, you are right, you should have foreign columns to be able to query nested entities (you should have some condition to filter). You are welcome — Roman, Apr 09 '19 at 14:16

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

1 Answers1