DynBack

How to “pretty” get one entity? SubSonic try …

SubSonic - is a tool, that generates typed model of database and gives many easy possibilities to retrieve data.

I have ambiguous feelings to this tool, sometimes it looks:

• ugly (names of queries, 2 query engines with strange names),
• something missed, this tool lookes like maded without any architect in mind
• some features, like Many-to-Many, looks amazingly usefull.

But what I most like is his very lite and small code access to data. What I mean is this:

// Subsonic example Person person = new Person(personId);

Its too simple to believe, no transactions, no contexts, simply id and data is here.

LinqToSql try …

What we have for Linq-to-Sql, that tries also make our life much easier. Even if we forget about creating DataContext, the code will look complex.

// LinqToSql first example
Person person = model.Personals.Single(p => p.PersonId == personId);

Firstly, I was tryng to move all this operations in static part of partial Person class. So it begin looks better.

partial class Person {
     public static Person GetById(Guid id)         {
          return model.Persons.Single(p => p.PersonId == id);
     }
}
// LinqToSql second example
Person person = Person.GetById(personId);

That looks better for me, but I am too lazy to write Copy-Past code for every entity, especially If I can do same task, with 100-200 lines of unhuman Reflection and Expresion Trees code)).

So the best way is unified Repository, that could GetById, Delete, and maybe some other standart for current program things.

// LinqToSql the best for now
Person person = Rep.GetById(personId);

Mmm. Lets code!

So what we need to automize “model.Persons.Single(p => p.PersonId == id);” this code.
- We need to know that type we are trying to getById is really Table. It is really Table, if type has TableAttribute on class.
- We need to find our PrimaryKey
- And we need to build lambda, that LinqToSql could understand.

I know tables could have several PrimaryKeys, that would be in next post, sorry.

To check table you need only read attribute:

/// Check TableAttribute
/// true - if attr exists, false - if not
private static bool CheckTable(Type entType) {
   return entType.GetCustomAttributes(typeof(TableAttribute), false).Length != 0; }

Next we need to find PrimaryKeys:

/// Todo - work only with one primary key objects
private static PropertyInfo GetPrimaryKeyName(Type entType) {
     if (!cachePrimaryKey.ContainsKey(entType))  {
           foreach (PropertyInfo prop in entType.GetProperties()) {
                var attrs = prop.GetCustomAttributes(
                       typeof(ColumnAttribute), false).Cast();
                foreach (ColumnAttribute attr in attrs)
                {
                     if (attr.IsPrimaryKey == true)
                     {
                            cachePrimaryKey.Add(entType, prop);
                     }
                }
          }
     }
     return cachePrimaryKey[entType];
 }
private static Dictionary cachePrimaryKey =
              new Dictionary();

I know that operation of finding PrimaryKey is costly, so I am caching finded PrimaryKeys for Types.

And to the end - GetById method:

/// If type has no attribute Table
public static T GetById(Guid id) {
     Type entType = typeof(T);
      if (!CheckTable(entType)) {
         throw new TypeLoadException(string.Format(
             "{0} is not Table Entity, has no attribute Table", entType.FullName));
     }
      string property = GetPrimaryKeyName(entType).Name;
      ParameterExpression cs;
      var lambda = Expression.Lambda>(
             Expression.Equal(
                     Expression.Property(
                             cs = Expression.Parameter(entType, "p"),
                             entType.GetProperty(property).GetGetMethod()
                     ),
                     Expression.Constant(id)
             ), new ParameterExpression[] { cs }
     );
      return model.GetTable().Single(lambda);
 }

So we can achieve any table through GetTable, and then generate and execute lambda expression. No, I am not so genius, to write such lambda codes, I cheated a bit and get generated by compiler lambda code, thats how it looks.

“model” means here standart DataContext. It is getting here from nowhere, because I use my implementation of singleton, that I will post next time. For now you can simply put your model as second param.

Full source code you can get here

Tags: C#, LinqToSql, Reflection

This entry was posted on Thursday, November 27th, 2008 at 9:10 pm and is filed under Database | . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.