Posts Tagged “activerecord”


NHibernate (and ActiveRecord’s) show_sql in web application


Looking into When using an OR/M of any kind, it is quite worthwhile to be able to look at the SQL generated by the tool, for various reasons (such as tuning the DB, finding SELECT N+1 issues, and sheer curiosity).

  Solution #1 One way of doing that is to start a profiler on the DB engine, but it has its downsides. For one, you would need a profiler tool, which is not always freely available. You might also not be able to access the DB engine with that kind of tool on various hosted environments.


In NHibernate’s configuration (and it is also exposed to Castle’s ActiveRecord users) you can set a property names “show_sql” to true. This will cause NHibernate to spit every SQL query, along with its parameters, onto the Console. Very useful when running Tests, but when running within a Web Application, you do not have access to the Console window, and can’t really see what is going on.

That also leads to another problem with using a profiler on the DB engine – you won’t be able to figure out which queries belong to which web request.

  Solution #2 One comprehensive solution is to use the excellent tool from Oren Eini – NhProf. I will not cover this tool here; it does lots of great stuff, and can help your development cycle. However not everyone will be willing to pay the price for using it.


not to worry, I hereby offer you two more options, which gives you less options, but are good enough for the problem at hand, and are free.

  Free solution #1 NHibernate is using log4net. it stores a lot of what it’s doing there. So, one can always setup a logger named “NHibernate.SQL” and get a hold of the queries. I do not cover log4net usage here. Google it up, and then set up the NHibernate.SQL logger.

You’d also want to setup a log4net variable for you to group the queries it gets by the web-request that needed them. You can do so by setting a property on log4net’s GlobalContext. e.g., in Application_BeginRequest:

log4net.GlobalContext.Properties["page_url"] = Context.Request.RawUrl + "|" + Guid.NewGuid();

then you’d set the log appender to write the page_url value, and you’ll be able to Group By it.



But this suck. You need to depend on log4net even if you do not want to, and setup that hack-ish global property, then read it from the log4net storage, and lots of complexities. wouldn’t it be great if you could simply got a hold of the Console’s output (or at least the NH parts of it?)

  Free solution #2 – even simpler The Console object allow you to set a custom Writer to its Out stream. Meaning, you can grab the Console’s output into an in memory string during a request, and then at the end of the request, grab all lines that starts with “NHibernate:” and you’re done:


on Application_BeginRequest:

var writer = new StringWriter();
Context.Items[SqlLogFilter.SQL_LOG_WRITER_KEY] = writer;

on Application_EndRequest:

var writer = HttpContext.Current.Items[SQL_LOG_WRITER_KEY] as StringWriter;
if (writer != null)
	var lines = writer.ToString().Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
	var relevantLines =
		lines.Where(l => l.StartsWith("NHibernate") || l.Length > 0 && char.IsWhiteSpace(l[0])).ToArray()
	var queries = string.Join(Environment.NewLine, relevantLines)
		.Split(new[] {"NHibernate:"}, StringSplitOptions.RemoveEmptyEntries)
		.Select(q => q.Trim());


within DoSomethingWith you can do whatever you like with the queries string collection


The more complete solution that I use, is taking advantage of a feature in Monorail’s AspView called ViewFilter (you can do this with ASP.NET’s output filter; look up HttpFilter. it’s not as clean, but workable). I create a filter that wrap the stuff that’s on the Application_EndRequest, turn the queries collection into a bunch of <pre> elements, and stick it within the view-engine’s output by simple string.Replace call, injecting these <pre> elements into a marker location in the markup.

I’d then use jQuery to make these <pre> elements visible when clicking somewhere secret on the screen.


The ViewFilter’s code (for reference):

public class SqlLogFilter : IViewFilter
	public static readonly string SQL_LOG_PLACEHOLDER = &quot;SQL_LOG_PLACEHOLDER&quot;;
	public static readonly string SQL_LOG_WRITER_KEY = &quot;SQL_LOG_WRITER_KEY&quot;;
	public string ApplyOn(string input)
		var log = &quot;&quot;;
		var writer = HttpContext.Current.Items[SQL_LOG_WRITER_KEY] as StringWriter;
		if (writer != null)
			var lines = writer.ToString().Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
			var relevantLines =
				lines.Where(l => l.StartsWith(&quot;NHibernate&quot;) || l.Length > 0 &amp;&amp; char.IsWhiteSpace(l[0])).ToArray()
			var queries = string.Join(Environment.NewLine, relevantLines)
				.Split(new[] {&quot;NHibernate:&quot;}, StringSplitOptions.RemoveEmptyEntries)
				.Select(q => q.Trim());
			log = queries
				.Select(q => &quot;&lt;pre&gt;&quot; + q + &quot;&lt;/pre&gt;&quot;)
				.Aggregate(&quot;&quot;, (q1, q2) => q1 + q2);
			var count = queries.Count();
			log = &quot;&lt;p&gt;Queries: &quot; + count + &quot;&lt;/p&gt;&quot; + log;
		return input.Replace(SQL_LOG_PLACEHOLDER, log);

Cloning a DetachedCriteria


I always need to look it up, so I might as well put it here for future reference:

What is a DetachedCriteria?

This is an ICriteria object, that is detached from any ISession, therefore is suitable to be passed around as a specification that is being built up dynamically.

Why would I want to clone it?

Well, say I want to run two similar queries (same WHERE part) but with a different projection or aggregation. For example - when fetching a paged collection, you’d want to create the spec criteria, then add Count projection on A to get the ‘total number of records’ ,and add the paging restrictions (sort, then from row 101 take 10 records). Now if you’d try to add the restrictions to the criteria to which you have already applied Count on, and error would occur.


There’s CriteriaTransformer class, which holds a few useful static methods, amongst them, Clone(DetachedCritetia):

DetachedCriteria spec = DetachedCriteria.For&lt;Whadever&gt;() ...; &lt;= setup criterionsDetachedCriteria countCriteria = CriteriaTransformer.Clone(spec)    ...; <= setup the count projectionDetachedCriteria pagedCriteria = CriteriaTransformer.Clone(spec)    ...; <= setup the pagingICollection<Whadever&gt; stuff = whadeverRepository.FindAll(pagedCriteria);
int total = whadeverRepository.Count(countCriteria);

Using nhibernate's named queries with ActiveRecord


One of the methods of querying the DB when using NHibernate, is to issue HQL queries. HQL stands for “Hibernate Query Language”. It has a SQL-like syntax and is very intuitive for people with SQL background.

The way this works is that NHibernate ‘compiles’ the HQL query into SQL, and then issues the SQL query (using ADO.NET’s facilities) to the DB.

Sounds pricey?

enter Named Queries.

now these are HQL (or SQL) queries, each has a name (obviously), that are been supplied to NH through the mapping. The queries are being translated and cached as IDbCommand objects as part of the framework initialisation, which mean that you get rid of the HQL->SQL overhead throughout the life of the process.

One other major benefit, is that the mechanism to actually execute these named queries, does not differentiate between HQL and SQL queries (for the simple fact that these queries have already been transferred to SQL at runtime). That gives you the possibility to replace HQL queries into tighter SQL queries (with the same parameters and which returns the same resultset) should your DBA figure out a better one.

But if you’re using ActiveRecord, you usually do not have direct access into the mapping (.hbm) files. So how would you use named queries with AR?

enter HqlNamedQueryAttribute (not such a great name, as I did state that it would also work for SQL queries).

So, for an example, on this blog’s source code, within PostRepository.cs you’d see this code:


#region queries[assembly: HqlNamedQuery(Queries.FindPostsInArchive, Queries.FindPostsInArchive)][assembly: HqlNamedQuery(Queries.FindByUrlFriendlyTagName, Queries.FindByUrlFriendlyTagName)]namespace KenEgozi.Com.Domain{ internal partial class Queries { internal const string FindPostsInArchive = @" from Post p where  year(p.Lifecycle.CreationDate) = :year and month(p.Lifecycle.CreationDate) = :month  order by p.Lifecycle.CreationDate desc"; internal const string FindByUrlFriendlyTagName = @" select p  from  Post p  join p.Tags t where t.UrlFriendlyName like :urlFriendlyTagName order by p.Lifecycle.CreationDate desc;"; }}#endregion


 public ICollection&lt;Post&gt; FindInArchive(int year, int month) { 

 return session .GetNamedQuery(Queries.FindPostsInArchive) .SetParameter("year", year) .SetParameter("month", month) .List&lt;Post&gt;(); }


Queries class is marked as partial, as other queries might be presented on other repositories or services that would need to add more named queries. I considered grouping of queries into groups by the using repository, or by aggregate roots, but the thing is - having all of the queries under the same namespace helps discoverability, and helps with preventing duplications

Executing Plain ol' SQL in ActiveRecord Transaction


I’m getting asked for this a lot (lately on the Castle’s usergroup, and on many other occasions)

You friend is ActiveRecordMediator.Execute

So here is a sample:

ActiveRecordMediator.Execute(typeof(ActiveRecordBase), delegate (NHIbernate.ISession session, object data){ IDbConnection con = session.Connection; IDbCommand cmd = con.CreateCommand();

   // That's the key part - joining the current AR transaction scope session.Transaction.Enlist(cmd); 

   // now you have a IDbCommand instance - use it at will

}, null);

If you want to create a proper method for handling the delegate instead of the anonymous one, then you can pass data in there (using the third argument) and it would get into the delegate as the “object data” thing.

On Linq for SQL and POCO


I’m looking at the option of using Linq To SQL for persistence.

Basic assumptions:

Today I’m using NHibernate (so 1 and 3 are set), and AR Attributes (so 4 is set). As for querying, I resort to hql (nice, yet too stringy), ICriteria (still stringy) and NHQG (cool, super cool, yet coupled with NH, while Linq is a “query everything” language)

I tried Linq for SQL (on a VS C# 2008 Express Beta2). No designer. Hand coded the entity, and have used the attributes for mapping.

First problem encountered: in order to make a column lazy, I need to change the underlying type to Link<MyOriginalType>, and then I can tell the context (using a LoadingOptions) about whether to load the lazy properties.

Couldn’t yet find a way to actually lazy load that property once the instance has already been loaded.

I much better like the way NH is handling things, with a runtime-generated proxy that takes care of lazy loading (among other stuff), so I get it without hassling my entities code.

Didn’t even mention First and Second Level Caches.

I guess I’d have to try and hop into NHibernate.Linq, and try to help Ayende with bringing it forward. That would mean diving into NH code, something I haven’t done for quite some time now …

ActiveRecord.Linq - naive but Working


I’ve spent some times lately with Linq To SQL and have played a bit with the Mapping namespace.

Why I do not like it very much is a matter for a different post. The matter at hand is that I want the power of Linq, and I want the power of NHibernate, and I want the easy road of ActiveRecord.

What do I mean by that? I’d like:

the needed prequisites:

So, Ayende has kick-started it, and with some help from Bobby Diaz, we have a prototype level NHibernate provider for Linq.

To make it work with ActiveRecord, all you need is to add:

using System;using Castle.ActiveRecord;using Castle.ActiveRecord.Framework;using NHibernate;namespace NHibernate.Linq{    public class ActiveRecordContext : NHibernateContext { public ActiveRecordContext() : base(null) { session = GetSession(); }  private ISession GetSession() { ISessionScope scope = SessionScope.Current; if (scope == null) throw new InvalidOperationException("You should be in a SessionScope()"); ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder(); return holder.CreateSession(typeof(ActiveRecordBase)); } }}

and now you can do stuff like:

using (new SessionScope()){ ActiveRecordContext context = new ActiveRecordContext(); var q =  from c in context.Session.Linq&lt;Category&gt;() select c;  foreach (Category c in q) Console.WriteLine(c.Name); }

Assuming Category has [ActiveRecord] mapping.

Summing up the last two and a half years - Bye bye SQLink


It was absolutely brilliant.

I started to work for SQLink on late December 2005 as a Team Leader in the Web Projects Department.

Colleaged by Oren Ellenbogen, it has been a pleasure. Our department head, Moti, was doing the best he can to create a very likeable working environment, and all our developers were enjoing a workplace that enabled them to learn a lot. Oren and I were directing all kinds of sessions with the developers, showing them stuff about .NET and the CLR, from “what are nullables”, through “What does the ‘using’ reserved word mean”, to “how the GC is actually working”.

We have built this great website called GotFriends, that gave the company a great new source for recruiting new employees, and actually is ground breaking in the Israeli HR world. Building that site, we’ve used many technologies to make it work smoothly with the company’s inner legacy HR system, and with the aid of the SQL master Moran Benisty, it even worked in an efficient way, and coded in a maintainable manner.

Mixing WebForms and Monorail, CodeSmith based BLL with ActiveRecord/NH, ASP.NET WebServices (asmx) and POX/JSON services, it was a very fun thing to work on, in addition to the business benefit to the company.

However, a few months ago the Web Project Department was closed, and the company started a new R&D team, leadedby Elad, the company’s VP of Business Development. We were two developers (Moran and I), and we worked on several initiatives that the CEO Tamir, and Elad, were cooking all the time. Those projects wereall Community-Driven-So-Called-Web-2.0-Kinda-Websites. It was a real delight, and I got the chance to learn a lot about the business side of running an Internet related initiative, as both Tamir and Elad are experienced and intelligent, and the process of refining ideas, with those two, was a real treat.

They also gave me the freedom to make all the technology decisions, and they’ve had enough faith in me to allow me run the projects using MonoRail and AspView, and running Castle ActiveRecord for DB access. Actually, most of the drive behind creating AspView was actually driven by Elad and Tamir, as I’ve promised to do the best I can to make sure that future additions to the team won’t need to learn Boo / Velocity in addition to learn the Monorail MVC and hql.

Which actually worked great. Moran has left the team about two months ago, and we’ve brought three new guys along (Ili, Ofir and last but not least, Itay), and they have seam to easily get control of all the “funky” technologies I’ve put in use in our projects.

Sadly enough, one of the initiatives has stalled just before airing, due to some business decisions. Then we started a new one, and in about 3 weeks we’ve had a working proof-of-concept, and I really hope that the site will air during August. I give the credit to the team, and to the use of MonoRail/ActiveRecord, as it’s such agile and suits highly-changing-environment, as most web initiatives are.

A point of interest: This very blog’s engine was actually a beta testing for some of the stuff we were using on our last project.

That’s it folks. I wish the SQLink family all the best, and I’m going to keep an eye on the cool stuff the R&D team is doing, and hopefully I’ll report on their success (which would be an AspView success too …) right here on my blog.

Regionerate - c# region maker


Check out this nice tool from Omer Rauchwerger.

It could help a team to manage a coding convention. Should be very useful in Open Source environments. For example, when committing changes to castle project (or sending a patch) there is a coding standard than needs to be followed. Regionerate could help a lot with that effort.

I need to see if it can select methods and properties by attributes. It could then help making MonoRail controllers and ActiveRecord decorated classes more readable (region of all actions with a “SkipFilterAttribute”, region of “Property” and region of “HasMany”, etc.)


Silly me, I forgot to mention that I found about this great tool at Roy Osherov’s blog. A very good one, that is. Many Agile related stuff, and funny little things, too. So go on and subscribe to it’s feed.

Eagerly loading multiple collections in one roundtrip


Today at work I did a session with my team, showing them several methods to query the DB in a NH/AR enviroment.

When we talked about eager fetching, I said that doing it for more than one collection in one query isn’t good (as advised in hibernate’s site), since it might build up a rather large cartesian product.

Why? If we have a type Entity, that looks a bit like that:

class Entity{ ... [HasMany ...] public IList&lt;Item&gt; Items ... [HasMany ...] public IList&lt;AnotherItem&gt; AnotherItems ... ...}

An eager fetch will do:

SELECT *FROM  Entities INNER JOIN Items ON Entities.Id = Items.EntityIdINNER JOIN AnotherItems ON Entities.Id = AnotherItems.EntityId

The result length will be eia, where e is the number of rows in Entities table, i is the number of rows in Items table , and a .. (you fill the blank).

But, luckily for us, NH is keeping a single concrete copy of each entity in a session. Ayende is abusing that in a way, that makes the above possible, without the 3rd (or more) dimention in the cartesian product.

How it works? Using a MultiCriteria,he isgettinghis entities,eagerly fetching one collection at a time. So it would return ei+earows. So for the first query, NH will populate the Entity instances, with the first collection (Items) populated. For the second query, NH will populate another Entity instances with the second collection (AnotherItems) populated. But, since it’s in the same session, actually the first Entity instances (with Items already populated) will get their AnotherItems collection populated.

That’s why at the end of the snippet, you see that he actually deals with list[0]. That’s where the first set of references is placed, and the other items in that list (list[1] … list[n]) are just copies of the references to the same Entity objects.

Quite a similar approach was seen when Ayende has shown us how to eager fetch using SQL (at the end of the post). The query there returns an array of Message items and an array of User items, but the User instances actually are wired to their Message instances, so he is using only the first array.

Castle build from the trunk - revision 3812


From Castle Project users group:

I’ve just built the trunk yesterday (rev 3812).

Main reason: I needed the BaseControllerTest from Castle.MonoRail.TestSupport.

The build was targeting .NET 2.0, in release, and no tests were run.

You can download it from: that it is not tested, so use it at your own risk. If you’re not going production this week, it should not be a problem …

My blog is running on ActiveRecord-MonoRail-AspView


So long dasBlog. It was great to have you, but it’s time to move on.

After a lot of work, I am proud to announce that my blog is running on MonoRail, using AspView for the views, and ActiveRecord to do DB stuff.

Not too fancy codewise, since I have very little spare time.

Most of the time spent on the blog upgrade process was on:

  1. Exporting the data from the “old” blog, and

  2. making a decent markup and design for the new one.

oh. and 3. letting WindowsLiveWriter do the edits, since I wasn’t in the mood to create a backoffice.

I’ll blog more about the process, and I’ll make the source available.

Please leave your comments here about the overall look’n’feel. There must be tons of bugs and I want your feedback.

ActiveWriter - 2nd preview is out


ActiveWriter is a VS2005 plugin, that adds a new item type for your projects. This item is actually a visual designer for ActiveRecord classes. Quite neat, and hopefully will increase the penetration of Castle’s ActiveRecord to the “If there’s no VS wizard, I do not use it” kinda guys.

You can read about it and download it from

Long time no posting. Must write.


I haven’t written much lately, since I was:

a. Learning for my final exam so I’d get my Bachelor’s Degree at this decade.b. Under a lot of preassure at work, since we have a cool web2.0 thingie approaching a public beta real soon (will be followed).c.Sneasing my heart out, darn flew.d.Got into a new project at my personal business. This one is driven by Castle’s ActiveRecord+MonoRail+AspView, and due to the client’s request it’ll use Access as the backend DB, and that would be a first-time-ActiveRecord/access for me. I still hope to convince him to at-least go for embedded FireBird.

So, stay tuned to some experiences with AspView, and hopefully in about a month you’ll havetwo MonoRail/AspView driven websites out in the open. I am excited. Are you?

NHibernate ' Could not find constructor for: ' in ' select new ' projection query


There are two facts here:1. I love NHibernate.2. I hate NHibernate’s exception messages.

And here’s my story.

On a project I’m working on, I need to show a projection of “top 10” from the database. let’s show this on the good old Blog scenario:

So I want to show the posts with the longest comments measured by the comment’s length. Stupid, huh? but it’s a demo only (I cannot expose the actual ERD). Let’s say I want the top 5.

I am using Castle ActiveRecord. There is a Post and a Comment classes. However, I do not wish to load Posts objects, since It will load the Comments, too, and maybe other stuff that the Post class is related to. So I have defined a PostProjection class:

   1:  publicclass PostProjection
   2:  {
   3:  publicstring Title;
   4:  publicint Length;
   5:  public PostProjection(string title, int length)
   6:      {
   7:          Title = title;
   8:          Length = length;
   9:      }
  10:  }

I have also added an [Import] attribute on the Post. The actual querying is done using the next hql:

   1:  publicstatic PostProjection[] GetTopPosts(int postsToGet)
   2:  {
   3:      SimpleQuery&lt;PostProjection&gt; q =
   4:  new SimpleQuery&lt;PostProjection&gt;(typeof(Post), @"
   5:          select new PostProjection(p.Title, sum(c.LineCount)) 
   6:          from 
   7:              Post p inner join 
   8:              p.Comments c 
   9:          order by sum(o.LineCount) desc
  10:          group by p.Title");
  11:      q.SetQueryRange(postsToGet);
  12:  return q.Execute();
  13:  }

It worked great.

Yesterday I’ve upgraded my Castle dll’s to the ones from build 229. It includes NHibernate

Now the “select new” started to fail, and NHibernate started to claim than “Could not find constructor for: PostProjection”.

I’ve been scratching my head, trying various approaches, and even was keen to skip the “new” and use an object[ ] and populate the Projection Array by hand, but then I tried changing the “length” parameter of the constructor from “int” to “long”. Magically it solved the problem.

Now, if only NHibernate would have said :

Could not find constructor for: PostProjection.Looking for: PostProjection(string, long)

I would’ve known what the problem was, and what should I change.

So what have we learned today?

  1. NHibernate expects sum() to return “long” rather than “int”2. NHibernate’s error messages suck.

I’ve svn-ed the NHibernate trunk, added some code so this message would be more developer friendly, and I’m going to send the patch to NHibernate’s JIRA.

Things I've been doing lately


I’ve been off the radar lately, due to some extensive work I do on a website for my employer.

It is still in early stages and I cannot talk about the site itself, but I can talk a little about the technology that drives it.

In one word: Castle.

In a few more words:

Data Access and OR/m: Castle’s ActiveRecord over NHibernate

Web UI Engine: Castle’s MonoRail

ViewEngine: AspView (by yours truely, inspired by Ayende’s Brail). All my spare time from work goes there.

Client Side Javascript stuff: Prototype and Scriptaculous.

I really believe in the efforts made by Castle group, and I hope the the site I’m working on will be successful, as to serve for yet another prove of concept.

One more thing I’m doing last month, is that I’ve decided to finally end the thing with my Bachelor’s degree. I needed to retake Linear Algebra I (yep, that one the all the non-bullet-time Matrixes … I H-A-T-E adjoined matrixes. yuck) so that’s actually why AspView doesn’t progress as I’d want it too (and as some of my readers want, too).

Building Application using Castle RC2


Ayende has recently posted a walkthrough for building Web Apps using the Castle Project’s libraries.

He covers ActiveRecord and MonoRail basics, showing off some of the shiny and new abilities (AR scaffolding, ARSmartDispatchers, Generics integration and so on).

The only thing missing is IoC-ing using Winsdor or even Binsdor. Maybe to hook some BL layer or something.

So it concludes (as of now) a Part I, Part II , and source-code.

If you wanna see a decent web development framework at action - tune up to those posts.

MonoRail vs. WebForms


Ayende has posted about the matter, only he’ve used the title “MonoRail vs. ASP.NET” instead. I think that his title isn’t accurate since MonoRail is actually a WebForms replacement that is built ontop of ASP.NET, exploiting all of the cool features found in ASP.NET and in the .NET framework alltogether. It’s the Web development framework that Microsoft forgot. So it has the benefits of the Rails interpretaion for MVC, with the power of .NET.

Here is the comment I’ve posted to Ayende’s post:

“My point of argue in favor of MR for people who are afraid of “non-microsoft” is, that actually MR is ASP.NET. It is just not WebForms. You get all the good stuff from ASP.NET: the .NET framework for start, compiled views (on Brail - kudos to you), the HttpModule stuff, seeamless integration with AR. This is what I say to the PMs, etc. To the developers themselves who are afraid, There’s a more detailed approach. Developers (especially ones who didn’t do web programming aside ASP.NET, and probably has a lot of VB experience but no PHP/JSP/ASP) like WebForms cuz they can get to the txtUsername.Text, txtPassword.Text, and txtAge.Text easily, not needing Request.Form[“Password”] (hey - it’s not typed !!!) . But then I reffer them to the fact that actually txtAge.Text isn’t typed too. it’s a string, and should be parsed, tried, validated etc. They should also manually create a User instance, and fill it’s properties. Doing the same on MR with Dispatching and AR is there without any line of code. I show them an example aqnd then hold them as they almost fall off their chairs. Not’n like a good example. Maybe having a ViewEngine that’s use c# or VB.NET instead of boo (not the WebForm ViewEngine which suck, but somthing similar to Brail) can make the move from WebForms to easier for developers, but frankly, as you’ve said, people who can’t get it OJT with a few examples aren’t the one’s I’m likely to work with anyway.”

On Wheels and Squares


On a small debate with a friend, about weather he should design and implement a Data Access Layer himself, or use an existing framework, I came across a piece by Ayende about 25 Reasons Not To Write Your Own Object Relational Mapper.

I’d like to add a quote from Code Complete 2 about programmers who are reluctant to read (and use) existing solutions to known problems (on p. 823):

… even if you want to reinvent the wheel, you can’t count on success. You might reinvent the square instead.

It’s definitely not a Dave Barry kinda funny punch line, however it’s very much to-the-point.

A Generic Execute Callback for ActiveRecord - Execute<T>


I am working with AR for a few month now, ignorantly ignoring the Castle Project’s wiki.

Stupid I am. I could have learned a lot and save a bunch of wandering around the net and the intellisense to learn obvious stuff.

Thanks to hammett who pointed me there.

Anyway - now I read it from <html> to </html>, and I saw the part about running HQL using the Execute Callback

There are examples, each one with two flavors: “not using generics”, and “using generics”. Well, the one about the Execute Callback is misleading. It should have been “not using anonymous method” and “using anonymous methods”, since the second one does not use generics.

So I thought - let’s make the API simpler.

We would have like to allow the user execute her hql like that:

   1:  publicstatic Post[] GetPostsByAuthorName(string authorName)
   2:  {
   3:  return (Post[])Execute(typeof(Post), "from Post p where p.Author = ?", authorName);
   4:  }

Or even better, by using generics (this time for real):

   1:  publicstatic Post[] GetPostsByAuthorName(string authorName)
   2:  {
   3:  return Execute&lt;Post&gt;("from Post p where p.Author = ?", authorName);
   4:  }

Here is the magic:

   1:  publicstatic T[] Execute&lt;T&gt;(string hql, paramsobject[] parameters)
   2:  {
   3:      IList untypedResults = (IList)Execute(delegate(ISession session, object data)
   4:      {
   5:  object[] queryParams = (object[])data;
   6:          IQuery query = session.CreateQuery(hql);
   7:  for (int position = 0; position &lt; queryParams.Length; ++position)
   8:              query.SetParameter(position, queryParams[position]);
   9:  return query.List();
  10:      }, parameters);
  11:      T[] results = new T[untypedResults.Count];
  12:      untypedResults.CopyTo(results, 0);
  13:  return results;
  14:  }

I’ll add an overload that will accept named parameters. I am not sure about the right way to do that, though.

My options are:

   1:  Execute<T&gt;(string hql, 
   2:  string[] paramNames, 
   3:      Type[] paramTypes, 
   4:  object[] parameters)


   1:  Execute&lt;T&gt;(string hql, IParameter[] parameters)

where IParameter definition is something like:

   1:  publicinterface IParameter&lt;T&gt;
   2:  where T: Type
   3:  {
   4:  publicstring Name { get; set;}
   5:  public T Value { get; set;}
   6:  }

Maybe both?

And maybe I haven’t read the wiki thoroughly enough and there are already implementations for all that?

Projection using ActiveRecord's ImportAttribute and HQL's select new clause


WARNING:I’m going to use some lousy examples in this post. Please bear with me, and stick to the Point rather than to the actual Classes being presented.

What is a projection?

Consider this genius ERD:

A sample projection could be a kind of a view:

The SQL is (surprise!)

   1:  SELECT
   2:      p.Title as PostTitle, c.Content
   3:  FROM 
   4:      Comments c INNER JOIN
   5:      Posts p ON c.PostId = p.Id

But we live in the Classes/Code/Compiler/Types/etc. domain, not the SQL domain, so I’d need a Post and a Comment classes that would derive from ActiveRecordBase<>. But what about the CommentsView? I can select into an array of objects using a simple HQL’s select:

   1:  IActiveRecordQuery query = new HqlBasedQuery(typeof(Job),

2: “select c.Post.Title, c.Content from Comment as c”); 3: 4: ArrayList report = ActiveRecordMediator.ExecuteQuery(query) as ArrayList; But I do not want to, since every item in the ArrayList is of type object[]. yuck.

No, I want to use a CommentView class, and this syntax: 1: IActiveRecordQuery query = new HqlBasedQuery(typeof(Job), 2: “select new CommentView(c.Post.Title, c.Content) from Comment as c”); 3:
4: ArrayList report = ActiveRecordMediator.ExecuteQuery(query) as ArrayList; and now the ArrayList contains CommentView objects.

In order to achieve that, we need to use the peculiar named attribute, [Import] like that:

   1:  [Import(typeof(CommentView), "CommentView")]
   2:  [ActiveRecord("Comments")]
   3:  publicclass Comment : ActiveRecordBase&lt;Comment&gt; { /* blah blah */ }

Now ActiveRecord (assuming Comment class was initialized using ActiveRecordStarter) knows that CommentView refers to a projection of the Comment ActiveRecord, using the class CommentView.

The use of [Import] is not documented too well, so I’ve included ImportAttribute in the post’s title so It could be easily found. After a few refinements I hope to post it to the CastleProject wiki.

Note Ayende’s post, with some better example for projection, but it lacks the demonstration of using Import attribute, so that’s actually the reason for this post.

That’s for the “how you do that”.

now for the “How I think you should be able to do that”:

well, INHO, the Import attribute is poorly named.

The name of the Attribute makes no sense to me.The lack of default name makes no sense to me. I should be able to

   1:  [Import(typeof(CommentView))] 

And what if I need some different projections to the same AR type? Multiple Import attributes make no sense, too.

Does the Import attribute serves a different purpose? If not – why not call it “Projects”? (since it projectsto some other classes)


   1:  [Projects(typeof(CommentProjectionOrWhatever))] 
   2:  publicclass Comment : ActiveRecordBase&lt;Comment&gt; { /* blah blah*/ }

or even better yet:

   1:  [Projects(
   2:  typeof(CommentProjectionOrWhatever), 
   3:  typeof(YetAnotherCommentProjectionNeededForDemonstrationPurposesOnly )]

Inheritence in Castle's ActiveRecord


The reference I’ve used whilelearning to use Castl’e ActiveRecord implementation is the Blog/Post demos that can be found on Castle’s site.

Let’s look at the Type Hierarchy example, that can be found here.

It shows an implementation of a class diagram that look a little bit like this:Class Diagram

Let’s look at the code : [ActiveRecord(“entity”), JoinedBase]publicclassEntity : ActiveRecordBase{privateint _id;[PrimaryKey]publicint Id{get { return id; }set { id = value; }}}[ActiveRecord(“entitycompany”)]publicclassCompanyEntity : Entity{privateint comp_id;[JoinedKey(“comp_id”)]publicint CompId{get { return comp_id; }set { comp_id = value; }}}[ActiveRecord(“entityperson”)]publicclassPersonEntity : Entity{privateint person_id;[JoinedKey(“person_id”)]publicint PersonId{get { return person_id; }set { person_id = value; }}} But look what happens. since the Id property on entity is public and inherited to the subclasses, you get something like this:Should I use .Id or .PersonId?

So there is a duplicate field here !!!. and it’s not only a getter-setter thingie. It also have different private members.

My solution for this is to virtualize the base Id, and protectedize (hehe) the _id member, like this:

[ActiveRecord(“entity”), JoinedBase]publicclassEntity : ActiveRecordBase{protectedint _id;[PrimaryKey]publicvirtualint Id{get { return id; }set { id = value; }}}[ActiveRecord(“entitycompany”)]publicclassCompanyEntity : Entity{[JoinedKey(“comp_id”)]publicoverrideint Id{get { return _id; }set { _id = value; }}}[ActiveRecord(“entityperson”)]publicclassPersonEntity : Entity{[JoinedKey(“person_id”)]publicoverrideint Id{get { return _id; }set { _id = value; }}} Now it makes more sence:I should use .Id

And before you hit me with a big stick - I do know of ActiveRecordBase<T> . :) the above code is for demonstration purposes only, not to be Copy&Pasted to your Brand-New-Best-Erp-Ever-Made-And-Will-Make-You-Rich

ActiveRecordBase vs ActiveRecordMediator


So after a lot of talking about the matter, I’m starting a little (but real) project with Castle’s ActiveRecord as an ORM service.

What I’m still not sure about, is weather I should inherit everything from ActiveRecordBase, or have my own base class and use ActiveRecordMediator?

Sure, I can derive my base class from ActiveRecordBase and have common behaviour for my model, but I am still not sure that I’m fully into the ActiveRecord pattern as a whole. It’s tempting to exploit Castle’s implementation but to keep the methods in a seperate class rather than in the model itself.

I also have some problem with the need to do FindAll, Find, etc. on each class so to expose the static methods in a typed way.

Well, I take back the last paragraph, since Icould use ActiveRecordBase<> and it solves this problem.

To conclude: I tend to go with subclassing ActiveRecordBase<> as a base class for my model, and I’m starting to code (and test) that way,but I could still use the knowledge gained by people with real experience withthis implementation…

I’ve looked for insights on the matter on the web, and have found nothing.If anyone reading this has an insight about the matter, please comment here, so people who do their first steps in Castle’s ActiveRecord implementation would have a better kick start regarding this issue.

ActiveRecord Beginner's Resources


I’ll post here links for stuff that I find usefull, for people who are doing their first steps in the Castle’s ActiveRecord’s world.

This post is to be updated while finding more resources.

Note that this is not a replacement for the official ducomentation that can be found on Castle’s website, but merely a place to do the beginner’s life somewhat easier.

Posts by Hamilton hammett Verissimo, who is the engine behind the whole story: sure that you are following his blog.

An article by Ayende about using Generics with ActiveRecord:[,.Net_2.0_and_Generics](,.Net_2.0_and_Generics)as before - stay tuned to Ayende’s blog for he very experienced in ActiveRecord and in NHibernate too, for the matter. He is also avery readablewriter.

Follow @kenegozi