I love
SubSonic (especially the latest 3.x version with
T4 template support) and its ability to map data to model objects/
data contracts. However, I've hit a bit of a snag when it comes to mapping not just the high-level object properties, but also its (complex type) children.
Say for example I have the following classes defined:
[DataContract]
public class User
{
[DataMember]
public int ID { set; get; }
[DataMember]
public Role Role { set; get; }
[DataMember]
public Profile Profile { set; get; }
[DataMember]
public string UserName { set; get; }
[DataMember]
public string Password { set; get; }
}
[DataContract]
public class Role
{
[DataMember]
public int ID { set; get; }
[DataMember]
public string Name { set; get; }
}
[DataContract]
public partial class Profile
{
[DataMember]
public int ID { set; get; }
[DataMember]
public string FirstName { set; get; }
[DataMember]
public string LastName { set; get; }
[DataMember]
public string EmailAddress { set; get; }
}
Then using SubSonic, I can run the following fluent query to hydrate a list of users:
List<User> users = db.Select
.From<User>()
.InnerJoin<role>(RolesTable.Role_IDColumn, UsersTable.Role_IDColumn)
.InnerJoin<profile>(ProfilesTable.Profile_IDColumn, UsersTable.Profile_IDColumn)
.ExecuteTypedList<User>();
However, the problem is that User's Role and Profile properties will remain null - projection mapping FAIL! Now I've spent quite some time googling around for a possible solution to this, thinking that maybe I'll find a "trick" of some sort to have SubSonic auto-magically hydrate all lower-level properties of my User object, without making a bunch of trips to the database and back to hydrate all properties. But I found nothing.
First of, let me work backwards and start with the comments following this blog post.
Rob Conery (of SubSonic fame) responded to Ben and suggested using ExecuteSingle(), which should map things appropriately. However, I've tried that as well:
User user = db.Select
.From<User>()
.InnerJoin<Role>(RolesTable.Role_IDColumn, UsersTable.Role_IDColumn)
.InnerJoin<Profile>(ProfilesTable.Profile_IDColumn, UsersTable.Profile_IDColumn)
.ExecuteSingle<User>();
And again, User's Role and Profile properties remained null, while all other simple type properties got set appropriately. Sadly, SubSonic alone couldn't hydrate all my object's children without some assistance.
So, I moved forward with Ben's suggested solution, and it worked beautifully (with some minor tweaks)!
First, define a custom attribute class:
[AttributeUsage(AttributeTargets.Property)]
internal class MapToColumnAttribute : Attribute
{
private readonly string _columnName;
protected internal MapToColumnAttribute(string columnName)
{
_columnName = columnName;
}
protected internal string ColumnName
{
get { return _columnName; }
}
}
Then, define a projection mapper class, which will do all the leg work for you:
internal class ProjectionMapper
{
protected internal 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 property.GetCustomAttributes(typeof(MapToColumnAttribute), true))
{
object value = dr[attr.ColumnName];
if (!(value is DBNull))
{
property.SetValue(target, value, null);
}
}
}
return target;
}
}
You'll notice there's a slight difference between this definition and the one in Ben's blog. First of all, he's referencing some ReflectionUtil, which I don't have and wasn't sure what it was. So I simply replaced the ReflectionUtil.GetAttributes() call with property.GetCustomAttributes(). Also, I added a DBNull check of the value - better safe than sorry.
Next, you must decorate your objects' properties with the new MapToColumn attribute, to map the properties to the appropriate columns (their names) returned by the database call:
[DataContract]
public class User
{
[MapToColumn("ID")]
[DataMember]
public int ID { set; get; }
[DataMember]
public Role Role { set; get; }
[DataMember]
public Profile Profile { set; get; }
[MapToColumn("UserName")]
[DataMember]
public string UserName { set; get; }
[MapToColumn("Password")]
[DataMember]
public string Password { set; get; }
}
[DataContract]
public class Role
{
[MapToColumn("ID")]
[DataMember]
public int ID { set; get; }
[MapToColumn("Name")]
[DataMember]
public string Name { set; get; }
}
[DataContract]
public partial class Profile
{
[MapToColumn("ID")]
[DataMember]
public int ID { set; get; }
[MapToColumn("FirstName")]
[DataMember]
public string FirstName { set; get; }
[MapToColumn("LastName")]
[DataMember]
public string LastName { set; get; }
[MapToColumn("EmailAddress")]
[DataMember]
public string EmailAddress { set; get; }
}
And finally, let's use this bad boy:
List<User> users = new List<User>();
User user = null;
SqlQuery query = db.Select
.From<User>()
.InnerJoin<Role>(UsersTable.Role_IDColumn, RolesTable.IDColumn)
.InnerJoin<Profile>(UsersTable.Profile_IDColumn, ProfilesTable.IDColumn);
ProjectionMapper mapper = new ProjectionMapper();
using (IDataReader dr = query.ExecuteReader())
{
while (dr.Read())
{
user = mapper.Map<User>(dr);
user.Role = mapper.Map<Role>(dr);
user.Profile = mapper.Map<Profile>(dr);
users.Add(user);
}
}
The solution is clean and relatively efficient; this makes Kon a happy boy. Again, I must give most of the credit to
Ben Scheirman for his help.
