LINQ to SQL querying against a view to populate an object model

Question

We have the following data model:

CalendarAppointment 1 <------> * AppointmentRole * <--------> 1 Person (or Group)

A calendar appointment can have multiple persons associated with it. Each person can be in a different role (attending, driving, picking up or dropping off). (A person can also be a member of a group and groups can be associated with appointments as well, but that's for extra credit for the purpose of this question).

At the beginning of a web request to render a calendar, we want to pull in all of the appointments relevant to the logged in member for a given date range.

The "driving" table is the Calendar appointment. Rendering looks like:

foreach (var appointment in dataContext.Appointments.Where(person has appointment role)]
{
   ... render appointment ...
   foreach (role)
      ... render role...

Currently, this is being done via LINQ to SQL querying against a view that we've defined in SQL Server to join columns from CalendarAppointment, AppointmentRole, and Person. This is the best way to make the query, but once the data is in the object world it would be more convenient to work with objects that map to the constituent tables that make up the view.

So in object world, I want to deal with...

 class CalendarAppointment
 {
     IEnumerable<AppointmentRole> Roles ...

 ...

 class AppointmentRole
 {
    Person Person ...

We're also running into a related problem with unit tests. It's much easier to set up a scenario by populating the discrete tables, but then querying against the view. Our in-memory data representation won't do this for us, so we end up populating an in-memory representation of the view instead.

So what we want to do is query against the view, but then crack" the result into an object structure that mirrors the underlying tables. It seems the only way to do that is to make separate queries against the tables:

1. Query appointment role table
2. Query calendar appointments using the set of appointment foreign keys from the first query.
3. Query person table using the set of person foreign keys from the first query.

This seems pretty convoluted. This seems like it would be a common problem in the ORM world. Does anybody have any insight into either 1) how to perform a query against the view and have it populate table objects on the other side, or 2) a different pattern or approach to the problem?

Answer 1

Have you looked at AutoMapper?

AutoMapper uses a fluent configuration API to define an object-object mapping strategy. AutoMapper uses a convention-based matching algorithm to match up source to destination values. Currently, AutoMapper is geared towards model projection scenarios to flatten complex object models to DTOs and other simple objects, whose design is better suited for serialization, communication, messaging, or simply an anti-corruption layer between the domain and application layer.

I've been using this recently to convert LINQ entities into simpler, flatter objects and it's worked well so far.