Querying in the Kinetic Framework

posted on 08/14/08 at 08:00:00 pm by Joel Ross

I was chatting with a few people in IRC the other day about different development approaches, and I brought up the Kinetic Framework. Someone took a look at source of the samples and didn't care for the SQL included in the class files.

Personally, I don't have a huge problem with it, but I understand the concern that others have. It definitely violates separation of concern, instead favoring encapsulation. Good or bad, that's the way it is.

But the more I thought about it, the more I wondered how hard it would be to create a simple interface to create your own queries easily. And then I started building it. By the time I was done, I had a solution in place that allowed me to create all of the queries created by the Kinetic Framework, and cover the majority of custom cases. And it has at least a bit of a fluent interface!

For custom queries, this is what we had before (this is from an example of getting a game with team's real names - there's a number of joins in there):

   1:  string commandText = @"
   2:  select " + Game.SelectFieldList + @",
   3:      [" + Week.TableName + @"].[Number] as 'WeekNumber',
   4:      ht.[Location] + ' ' + ht.[NickName] as 'HomeTeamDisplayName',
   5:      at.[Location] + ' ' + at.[NickName] as 'AwayTeamDisplayName'
   6:  from [" + Game.TableName + @"]
   7:  inner join [" + Week.TableName + @"]
   8:   on [" + Game.TableName + @"].[WeekId] = [" + Week.TableName + @"].[WeekId]
   9:  inner join [" + Team.TableName + @"] ht
  10:   on [" + Game.TableName + @"].[HomeTeamId] = ht.[TeamId]
  11:  inner join [" + Team.TableName + @"] at
  12:   on [" + Game.TableName + @"].[AwayTeamId] = at.[TeamId]
  13:  where [" + Game.TableName + @"].[SeasonId] = @SeasonId 
  14:  order by [" + Week.TableName + @"].[Number] asc;
  15:  ";
  17:  List<IDataParameter> parameters = new List<IDataParameter>();
  18:  parameters.Add(ServiceLocator.GetDataParameter("@SeasonId", season.SeasonId));
  20:  return EntityBase.GetList<FullGame>(commandText, parameters);

Notice the large string built and manually working with parameters. Lots of SQL syntax knowledge involved here.

Compare that to the way I came up with:

   1:  JoinCriteria joinCriteria = new JoinCriteria(typeof(Game), typeof(Week), 
   2:      Game.GameProperties.WeekId, Week.WeekProperties.WeekId)
   3:    .Add(new JoinCriteria(typeof(Game), typeof(Team), 
   4:      Game.GameProperties.HomeTeamId, Team.TeamProperties.TeamId, "ht"))
   5:    .Add(new JoinCriteria(typeof(Game), typeof(Team), 
   6:      Game.GameProperties.AwayTeamId, Team.TeamProperties.TeamId, "at"));
   8:  ICriteria criteria = new StandardCriteria(typeof(Game), Game.GameProperties.SeasonId, 1);
   9:  IQueryContainer query = (new QueryGenerator(typeof(FullGame), criteria, joinCriteria)).GetQuery();
  10:  return EntityBase.GetList<FullGame>(query.CommandText, query.Parameters);

I'm not sold on the exact semantics yet, but the idea is there. This doesn't require any SQL knowledge. You still have to know how your objects are related, but not SQL directly, and you don't have to worry about creating your own parameters. The QueryGenerator handles creating the query for you, and as it builds the query, it maintains the parameters as well.

The other nice thing about this is that, while it is meant to work with the Kinetic Framework, it doesn't require any changes to the framework to work - it's essentially an add-on that I could plug into any of my existing projects and immediately get the benefit of being able to build queries quickly and easily.

I've also been messing around with the ASP.NET MVC bits as well. I'm trying to figure out how I could use that with the Kinetic Framework, and be able to unit test the controllers. Because of all of the static Get methods, that's tough. So I've been messing around with the idea of adding Repositories to handle the actual retrieval and persistence of the entities. It's still a bit awkward, but I was able to unit test my controllers without ever hitting any of the static methods or any persistence methods in the framework - because I was able to mock my repositories. I need to tighten that up a bit, but I'll probably blog about that a bit in the future. Actually, the above querying technique makes the repositories easier to create and less coupled to the static methods in the entities - a good thing. But that's for another time.

So, if you use the Kinetic Framework, would this be something you'd be interested in? It still needs a lot of work, but I wanted to see if there's any interest in it before I pursue it too much.

Categories: C#, RCM Technologies