How to map multiple SQL relationships to c# objects

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, slapper automapper 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; }
} 

Now i query the Data from the Tables with Dapper in the underscore Notation from Slapper:

const string sql = @"SELECT 
                                    m.Name,
                                    m.Phone,
                                    m.ManagerId,
                                    f.FacilityId As Facility_FacilityId,
                                    f.WorkerCount As Facility_WorkerCount,
                                    f.SquareFoot As Facility_SquareFoot,
                                    f.Description As Facility_Description,
                                    c.CityId AS Facility_City_CityId,
                                    c.Name AS Facility_City_Name,
                                    c.Description AS Facility_City_Description,

                                    from Manager m  
                                    INNER JOIN  dbo.Facility f  ON  m.ManagerId = f.ManagerId
                                    INNER JOIN  dbo.City c  ON  f.CityId = c.CityId";


            using (System.Data.IDbConnection _connection = new System.Data.SqlClient.SqlConnection("Connstrint"))
            {
               var test = _connection.Query<dynamic>(sql);
                Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(Facility), new List<string> { "FacilityId" });
                Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(Manager), new List<string> { "ManagerId" });
                Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(City), new List<string> { "CityId" });



                var testNetwork = (Slapper.AutoMapper.MapDynamic<Manager>(test) as IEnumerable<Manager>).ToList();
                System.Diagnostics.Debugger.Break();
            }

I understand that the mapping cant fully work because facility cant go to City in the Objects. Should i map twice ? one time from Manager down then from City ? Or is there another design pattern that i should use?

Answer 1

If you are using Dapper I will advice to do smaller query to retrieve the data rather than try to do complex mapping. I would write the code in this way:

const string sqlManager = @"SELECT 
                                    m.Name,
                                    m.Phone,
                                    m.ManagerId,
                                    from Manager m";  
 
const string sqlFacility = @"SELECT f.ManagerId,
                                    f.CityId,
                                    f.FacilityId,
                                    f.WorkerCount,
                                    f.SquareFoot,
                                    f.Description,
                                    from dbo.Facility f";

const string sqlCity = @"SELECT 
                                    c.CityId,
                                    c.Name,
                                    c.Description,
                                    from  dbo.City c";

using (System.Data.IDbConnection _connection = new System.Data.SqlClient.SqlConnection("Connstrint"))
{
    List<Manager> managers = _connection.Query<dynamic>(sqlManager);
    List<Facility> facilities = _connection.Query<dynamic>(sqlFacility);
    List<City> cities = _connection.Query<dynamic>(sqlCity);

    foreach (var manager in managers)
    {
        manager.Facilities = facilities.Where(x => x.ManagerId == manager.ManagerId).ToList();
    }
    foreach (var city in cities)
    {
        city.Facilities = facilities.Where(x => x.CityId == city.CityId).ToList();
    }
}

As well the code above match yours but I will advice to use async/await to your calls specially, here is some sample call for dapper with stored procedure:

var mainTables = await _sql.LoadDataAsync<MainTable, dynamic>(
    "dbo.spMainTableGetAll",
    new { });

List<SubTable> subTables = await _sql.LoadDataAsync<SubTable, dynamic>(
    "dbo.spSubTableGetAll",
    new { });

foreach (var mainTable in mainTables)
{
    mainTable.SubTables = subTables.Where(x => x.MainTableId == mainTable.Id).ToList();
}