Branching SQL relationship to C# object model

Question

I'm developing a C# WPF desktop application where I need to read/write to an SQL database 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.

I know of this very similar question but I have run into a problem with the mapping of some relationships.

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; }
} 

The problem with this is that I now have 2 lists of Facility's, one in Manager and one in City with duplicate data. The facility could have many child tables, so this can be large amounts of data. Is there a better way to represent the data in C# or another design pattern?

Answer 1

Just grab all of the facilities in their own collection, and reference it accordingly.

Public List<Facility> Facilities => _connection.GetAll<Facility>(); // Dapper.Contrib

public class Manager {

    public string Name { get; set; }
    public string Phone { get; set; }
    public List<Facility> Facilities => Facilities.Where(x => x.ManagerName.Equals(Name));
} 

The Linq statement is just an illustration. In practice, you're going to have Facility IDs, Manager IDs and City IDs in proper one-to-many relationships.

Answer 2

It's not duplicate data, or at the least it doesn't need to be. Your ORM framework will take care of this; if you have the Facility with FacilityId 1 in a City's list, and (in the same context) retrieve the Facility's Manager, the Facility record in the Manager's list will actually point to the same object in memory. This will hold for all child tables under Facility too. As far as I can tell your design is fine.