1

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?

0

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.

0

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.

  • How would you go about retrieving the Manager in the same context ? Would you query a list of all Facility's with corresponding Managers and City's and let the ORM map the list onto the objects ? – maxiangelo Apr 9 at 9:30
  • It will depend on the ORM, and while I have experience with Entity Framework I do not have experience (yet) with Dapper. – Glorfindel Apr 9 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.