.NET - ORMs and all possible combinations - ViewModel?

Question

How would you approach this problem with an ORM? This is a hypothetical (simplified) example:

I have a table of cities:
1 - New York
2 - London
3 - San Francisco
4 - New Orleans

I have a table of scores: (first column Unique Primary Key, second Month Code, third FK to City, fourth Score (int))
1 - 352 - 1 - 9
2 - 352 - 2 - 10

For month 352 only New York and London are mentioned.

When I present this to a user in a user interface I'd like a grid showing all 4 cities for this month. And nulls (blanks) for the cities San Fran and New Orleans.

What is the best approach for this with an ORM? Bring back your Business Objects in "model" form and then transform them into a "viewmodel" form? How have you handled similar situations in ORMs? I've handle this before in ADO.NET with my SQL statements, but have never done this kind of thing in an ORM and I'm looking for advice, guidance, or an approach.

Answer 1

I use LINQ to map domain objects to view models. (I use the Entity Framework, but the technique works in any ORM with good LINQ support.) First write a POCO view model, then project onto it:

var pm = from c in Context.Cities
         let score = c.Scores.Where(s => s.MonthCode == selectedMonthCode).FirstOrDefault()
         select new CityScoresPresentation
         {
             City = c.Name,
             Score = score.Score
         };

This is LINQ to Entities. LINQ to SQL works similarly. YMMV with other ORMs LINQ implementations.

I strongly recommend using a view model instead of binding views to ORM-aware types.

Answer 2

We actually practice a form of Command Query Separation (Greg Young version -- not the Meyer version) so we would at the very least use the NHibernate ICriteria to select the details we want and then use the AliasToBeanTransformer to inject them directly into a DTO.

For our most complex entities we actually have a separated table with all the details a screen needs to display collapsed into a single row (aka OLAP). We can then run queries directly against this table which by-passes the cost of loading a complex entity which contains much more information then our screen needs.