I've said in the past that we use SubSonic on my current project.  It does what it says it does (mostly) and generates some usable code off of an existing database.  I do have my qualms about it, mostly because of my bias to look at things from the object/domain world rather than the database world.  But I am a committer, so my energy complaining would be better off contributing goodness to the project.

With the most recent release of the new query API in SubSonic, we were given a lot of flexibility.  This was much needed in our project because getting single results and collections off of a single entity is rarely sufficient for complex applications.  Often times the data needed to display on a page comes from multiple tables.

In the past, I was forced to do this sort of ugly nastiness:

//get schedules (1 query)
Schedule[] schedules = _repository.GetSchedulesInRange(start, end);

//get employee info (2 queries)
Employee[] employees = _repository.GetEmployeesInSchedules(schedules);

ScheduleDisplayMapper mapper = new ScheduleDisplayMapper();
return mapper.Map(schedules, employees);

This function returned an array of ScheduleDTO objects that were used to bind to a view.  The lack of joins at the database level was really killing our productivity and in some cases, performance.  It often took more code to write the query than you'd expect, because you would have to perform it in steps.

Note: I'm really pragmatic about performance, so 2 queries when 1 is possible doesn't usually matter in the long run.  Of course if this *is* a perf bottleneck, then of course, optimize.  I much prefer to balance programmer performance over unnoticeable application performance.

With the new query API, I can now do this:

SqlQuery query = new Select()
   .From<OnCallSchedule>()
   .InnerJoin(Area.AreaIdColumn, OnCallSchedule.AreaIdColumn)
   .InnerJoin(EmployeeProfile.EmployeeProfileIdColumn, OnCallSchedule.EmployeeProfileIdColumn)
   .Where(OnCallSchedule.StartDtColumn).IsLessThanOrEqualTo(date)
      .And(OnCallSchedule.EndDtColumn).IsGreaterThanOrEqualTo(date)
      .OpenExpression()
          .And(Area.AreaIdColumn).IsNull()
          .Or(Area.AreaIdColumn).IsEqualTo(areaId)
      .CloseExpression()
   .OrderAsc(OnCallSchedule.PriorityColumn.ColumnName);

I get complete refactoring support, intellisense, and type-safety.  But now we have a different problem:  we're not returning a single entity anymore.  We're returning a combination of columns from different tables.

To build my DTO objects, I then had to resort to functions like this:

public static ScheduleDTO FetchFromRow(IDataReader dr)
{
    int i = 0;
    ScheduleDTO schedule = new ScheduleDTO();
    schedule.OnCallScheduleId = dr.GetGuid(i++);
    schedule.Start = dr.GetDateTime(i++);
    schedule.End = dr.GetDateTime(i++);
    schedule.Comments = dr[i++] as string;
    schedule.Priority = dr.GetInt32(i++);
    schedule.EmployeeProfileId = dr.GetGuid(i++);

    string firstName = dr.GetString(i++);
    string lastName = dr.GetString(i++);
    schedule.EmployeeName = firstName + " " + lastName;

    return schedule;
}

Excuse me while I go vomit.  This code gives me nightmares from the days when I wrote my own data access layer by hand.  We as programmers should never be writing code that is simply of the form copy this value to that value.  Cast X to Y.  It's specifically the reason why data binding exists for forms.

After writing two of these methods I decided we need to come up with a better way.  I wanted to declaratively say, this DTO maps to these columns.  We don't have .NET 3.5, so magic-y lambda syntax is out.

I decided to lean on attributes.  Yes, this does add some "database-y" things on my DTOs, but I figured the baggage was worth it.  I created a custom attribute to map properties on my DTOs to columns on a result set.

[AttributeUsage(AttributeTargets.Property)]
public class MapToColumnAttribute : Attribute
{
    private readonly string _columnName;

    public MapToColumnAttribute(string columnName)
    {
        _columnName = columnName;
    }

    public string ColumnName
    {
        get { return _columnName; }
    }
}

Now to decorate my DTO....

public class ScheduleDTO
{
        [MapToColumn(OnCallSchedule.Columns.Priority)]
        public int Priority
        {
            get { return _priority; }
            set { _priority = value; }
        }

        [MapToColumn(EmployeeProfile.Columns.EmployeeProfileId)]
        public Guid ProfileId
        {
            get { return _profileId; }
            set { _profileId = value; }
        }

        /* snip */

SubSonic has a struct called Columns that contains all of the column names. I had to edit the default template to make these constants rather than statics, so that they would be usable in the attribute constructors.

Now that all of the mapping information has been provided declaratively, I can now leverage a generic mapper class to do the copying form DataReader over to DTO.

public T Map<T>(IDataReader dr) where T : new()
{
    Type type = typeof(T);
    T target = new T();
    foreach(PropertyInfo property in type.GetProperties())
    {                
        foreach(MapToColumnAttribute attr in ReflectionUtil.GetAttributes<MapToColumnAttribute>(property))
        {
            object value = dr[attr.ColumnName];
            property.SetValue(target, value, null);
        }
    }

    return target;
}

This function takes a DataReader that currently on a record, loops over the properties of the DTO looking for our attribute.  It then sets the value using reflection.  The beauty of this is, I don't have to worry about casting anything.  As long as the types of the columns match up with the type of the property, then we're golden.

The act of mapping projections of entities onto flat data transfer objects is now incredibly simple:

ProjectionMapper mapper = new ProjectionMapper();
using(IDataReader dr = query.ExecuteReader())
{
   while(dr.Read())
      results.Add(mapper.Map<ScheduleDTO>(dr));
}

You can take this one step further to encapsulate the looping over the data reader as well.

It was a nice feeling to be able to remove 17 lines of query code and 1 40 line mapping class and replace it with a tighter query and a few attributes.

Deleting code is fun.