One of the new projects at my day job is using ActiveRecord for data access. I’m a huge fan of ActiveRecord (and of all things Castle), but I like the fact that LINQ makes it very easy to do ad-hoc queries with a compile-time safety net. Unfortunately, ActiveRecord does not support LINQ out of the box. Luckily though, ActiveRecord is built on top of NHibernate, and LINQ support is available via NHibernate Contrib, at least if you aren’t afraid to venture into the source. If you follow the steps below, you can build your own LINQ provider for ActiveRecord and get the syntactical sugar of language-integrated query with the simplicity of ActiveRecord. Note that you will need to know the basics of using Subversion in order to follow these steps (I highly recommend TortoiseSVN), and you must have NAnt installed and in working order.
- Checkout NHibernate.LINQ from https://nhcontrib.svn.sourceforge.net/svnroot/nhcontrib/trunk/src/NHibernate.Linq.
- Checkout Castle from http://svn.castleproject.org:8080/svn/castle/trunk.
- Copy NHibernate.dll from "NHibernate.Linq\lib" to "Castle\SharedLibs\net\2.0". This is necessary because the latest release build of NHibernate doesn’t work with NHibernate.LINQ. You could try to build NHibernate from its trunk and copy the DLL to both Castle and NHibernate.LINQ, but since the projects occasionally end up out-of-sync, I prefer the copy-and-paste method.
- Build Castle using NAnt by running this command in the root of your Castle checkout: nant rebuild -D:common.testrunner.enabled=false. Do *NOT* try to build using the solution; the NAnt scripts create some of the required AssemblyInfo files, and you can’t build without those.
- Copy the following DLLs from the Castle build output to "NHibernate.Linq\lib": Castle.Core.dll, Castle.DynamicProxy2.dll, Iesi.Collections.dll.
- Build NHibernate.Linq using MSBuild, Visual Studio, or whatever you want.
- That’s it (sort-of).
- (Recommended) I like to copy the required DLLs to a common folder that I keep under version control with my project. This insures that nothing gets lost since I’m lazy and really don’t want to jump through these hoops more often than absolutely necessary. If you go that route, copy the following DLLs from the various build output directories to a common folder: Castle.ActiveRecord.dll, Castle.Components.Validator.dll, Castle.Core.dll, Castle.DynamicProxy.dll, Iesi.Collections.dll, NHibernate.dll, and NHibernate.Linq.dll.
At this point, you should now have Castle, NHibernate, and NHibernate.LINQ all synced up where they can play together. All that remains is to create our ActiveRecord LINQ context. So, let’s begin!
- Create a new class library in Visual Studio.
- Add references to the DLL’s listed in the optional step 8 above. If you didn’t copy things to a central folder, you probably will do so now. :)
- Create a new class named ActiveRecordLinqContext. Go ahead and create a corresponding test fixture class now as well (you *do* practice unit testing, right??)
A quick note: I’m using SQLite for my tests. If you go that route, be sure you place the SQLite DLL where NHibernate can find it. Alternatively, you can test with any other database that NHibernate supports. A future post will detail how to use SQLite for clean and simple ActiveRecord testing.
That’s it for the boring stuff, on to the glorious code!
1: /// <summary>
2: /// Provides a LINQ-enabled data context that works with ActiveRecord.
3: /// </summary>
4: public class ActiveRecordLinqContext : NHibernateContext
5: {
6: #region Public Constructors
7:
8: /// <summary>
9: /// Creates and configures the context.
10: /// </summary>
11: public ActiveRecordLinqContext() : base(GetSession())
12: {
13: }
14:
15: #endregion
16:
17: #region Private Static Methods
18:
19: /// <summary>
20: /// Gets a correctly-initialized session.
21: /// </summary>
22: /// <returns></returns>
23: private static ISession GetSession()
24: {
25: ISessionScope scope = SessionScope.Current;
26:
27: if (scope == null)
28: {
29: throw new InvalidOperationException("You must have an active SessionScope object to use this class.");
30: }
31:
32: ISessionFactoryHolder holder = ActiveRecordMediator.GetSessionFactoryHolder();
33:
34: return holder.CreateSession(typeof (ActiveRecordBase));
35: }
That’s pretty simple, but how do we use it? Here’s the test fixture and a test class:
1: /// <summary>
2: /// A simple widget!
3: /// </summary>
4: [ActiveRecord]
5: public class Widget : ActiveRecordBase<Widget>
6: {
7: /// <summary>
8: /// The ID.
9: /// </summary>
10: [PrimaryKey]
11: public int Id { get; set; }
12:
13: /// <summary>
14: /// Its name.
15: /// </summary>
16: [Castle.ActiveRecord.Property]
17: public string Name { get; set; }
18:
19: /// <summary>
20: /// Its description.
21: /// </summary>
22: [Castle.ActiveRecord.Property]
23: public string Description { get; set; }
24: }
25:
26: /// <summary>
27: /// Test fixture for <see cref="ActiveRecordLinqContext"/>.
28: /// </summary>
29: /// <author>MBH</author>
30: /// <dateAuthored>7/22/08</dateAuthored>
31: [TestFixture]
32: public class SimpleLinqTests
33: {
34: #region Private Helpers
35:
36: /// <summary>
37: /// Initializes ActiveRecord to work with an in-memory temporary database
38: /// via SQLite.
39: /// </summary>
40: private static void SetupActiveRecord()
41: {
42: Dictionary<string, string> settings = new Dictionary<string, string>();
43: settings.Add("connection.driver_class", "NHibernate.Driver.SQLite20Driver");
44: settings.Add("dialect", "NHibernate.Dialect.SQLiteDialect");
45: settings.Add("connection.provider", "NHibernate.Connection.DriverConnectionProvider");
46: settings.Add("connection.connection_string", "Data Source=LinqTest.dat;Version=3;");
47:
48: InPlaceConfigurationSource config = new InPlaceConfigurationSource();
49: config.PluralizeTableNames = true;
50: config.Add(typeof(ActiveRecordBase), settings);
51:
52: if (ActiveRecordStarter.IsInitialized)
53: {
54: ActiveRecordStarter.ResetInitializationFlag();
55: }
56:
57: ActiveRecordStarter.Initialize(config, typeof (Widget));
58: }
59:
60: /// <summary>
61: /// Drops and recreates the database schema.
62: /// </summary>
63: private static void RecreateSchema()
64: {
65: ActiveRecordStarter.CreateSchema();
66: }
67:
68: #endregion
69:
70: /// <summary>
71: /// Initializes the data layer for first use.
72: /// </summary>
73: [TestFixtureSetUp]
74: public virtual void TestFixtureSetup()
75: {
76: SetupActiveRecord();
77: RecreateSchema();
78: }
79:
80: /// <summary>
81: /// Verifies that LINQ works as expected.
82: /// </summary>
83: [Test]
84: public void LinqTest1()
85: {
86: //First, insert a few test widgets models.
87: for (int i = 0; i < 10; i++)
88: {
89: Widget widget = new Widget { Name = "Test" + i, Description = "Test Description " + i };
90: widget.SaveAndFlush();
91: }
92:
93: using (new SessionScope())
94: {
95: ActiveRecordLinqContext context = new ActiveRecordLinqContext();
96:
97: //The real beauty of this is not testable: it is actually only
98: //querying the database for the names, not the full Widget
99: //objects! You can turn on log4net to verify this.
100: string[] names = (from w in context.Session.Linq<Widget>()
101: select w.Name).ToArray();
102:
103: Assert.AreEqual(10, names.Length);
104:
105: //This is all translated into SQL and executes in the DB, not in
106: //memory!
107: names = (from w in context.Session.Linq<Widget>()
108: where w.Id > 5
109: select w.Name).ToArray();
110:
111: Assert.AreEqual(5, names.Length);
112: }
113: }
114: }
Run the tests, and you should see green. You can turn on logging (by calling log4net.Config.BasicConfigurator.Configure()) if you want to see the raw SQL being sent to the database to confirm that LINQ is working.
Big, big props go out to Ken Egozi; his post got me on the right track to get all this working.
Hi,
I’m using AR (RC found in site) which depends on NH 1.2
Now I’m trying to follow your instruction but NHibernate.Linq requires NH 2.1 and
I haven’t found AR which works with NH 2.1.
Which AR are you using?
Thanks a lot,
Dumb question , you are using the trunk.
I’ll try it. 🙂
I haven’t had time to check it yet, but I *think* LINQ is now supported on the trunk of AR. It might be a lot simpler now than what I went through last year…
I downloaded the trunk it’s much simpler now with ActiveRecord.Linq
I don’t know why I’m getting this error:
Unable to cast object of type ‘Finisar.SQLite.SQLiteConnection’ to type ‘System.Data.Common.DbConnection’
WTF? Isn’t it obvious?
Any thoughts?
Hmm, if memory serves, there are actually two different SQLite providers. One is obsolete and doesn’t work with NHibernate/AR, but the other one does. Maybe check to make sure you have the correct SQLite library?