I am back to blogging at http://davesquared.blogspot.com. My feed has been re-pointed so if you have subscribed you won't need to change anything.
There are at least a couple of ways to view the SQL generated by NHibernate. The easiest way is probably to use the hibernate.show_sql configuration property to dump it all to Console.Out:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section
name="nhibernate"
type="System.Configuration.NameValueSectionHandler, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
</configSections>
<nhibernate>
<add
key="hibernate.connection.provider"
value="NHibernate.Connection.DriverConnectionProvider"
/>
...
<add key="hibernate.show_sql" value="true" />
</nhibernate>
</configuration>
Another way is to configure log4net. NHibernate logs a wealth of information, but you can restrict it to just the generated SQL by doing the following:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section
name="nhibernate"
type="System.Configuration.NameValueSectionHandler, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net"/>
</configSections>
<nhibernate>
...
</nhibernate>
<log4net>
<appender name="ConsoleAppender" type="log4net.Appender.ConsoleAppender">
<layout type="log4net.Layout.PatternLayout">
<conversionPattern value="%date - %message%newline"/>
</layout>
</appender>
<logger name="NHibernate.Loader.Loader" additivity="false">
<level value="INFO" />
<appender-ref ref="ConsoleAppender" />
</logger>
</log4net>
</configuration>
This configuration means that anything logged from the NHibernate.Loader.Loader class will go through the logger with the matching name. This class happens to log NHibernate-generated SQL as INFO messages.
To get this log4net configuration to work, I had to make sure the following code was executed once in the relevant AppDomain: log4net.Config.XmlConfigurator.Configure();. In my case, I chucked it in the test fixture setup. Calling it multiple times doesn't seem to break anything, but you really only need to call it once to pickup your configuration.
Unlike the hibernate.show_sql option, this does not show the values of the parameters passed for substitution in each SQL statement.
Viewing logs in NUnit
NUnit has a Log tab that lists log4net logging information, as well as displaying Console.Out and Console.Error. If you go to the Tools menu --> Options -->Text Output, you can get NUnit to "Display TestCase Labels" for each test. Combined with the logging options above, you can then get a nice view of the SQL used for each of your tests.
More information
I found this post from Mike Nichols a very helpful reference on NHibernate logging. There is also some official documentation on the subject.
This was originally posted to my previous blog. You can view the original post and any comments here.
Frans has finally had enough of the bickering on the ALT.NET lists. The last straw was when he mentioned the "W" word -- "Waterfall development", at which point he was allegedly set upon by a band of Kent Beck-loving, test-infected, over-zealous Agile thugs :-). Frans' main complaint (and probably a fair one) was the lack of evidence or science behind any of the arguments. Chad Myers had a good reply, where he used an industry example to try and show Waterfall development is fairly useless compared to iterative development.
All this reminded me of something I heard ages ago: Waterfall never really existed. I decided to have a quick look into it. First stop, Wikipedia, which states that the first reference to Waterfall-style development by Winston W. Royce in 1970 was in showing a model that was good in theory but flawed in practice. The purpose of his paper was to describe how the model could be fixed by adopting a more iterative approach. (He didn't use the term "waterfall", but he does use the familiar waterfall phase diagram.)
In the original paper, Managing the Development of Large Software Systems [PDF], Royce asserts that "the [Waterfall] implementation described above is risky and invites failure", his main complaint being that it takes too long to get feedback from testing and that any changes to design required become too disruptive late in the process. He then goes on to describe the steps to "transform a risky development process into one that will provide the desired product". Royce even concludes that the "simpler
method [Waterfall] has never worked on large software development efforts."
This 2003 piece from Conrad Weisert has a bit more on the topic. His conclusion seems in rough agreement with Royce's: that phased software development is fine in theory and can work "when practiced with sensible judgment and flexibility" (or even agility ;-) ). But he is quite adamant that strict "Waterfall development", in which one never revists previous phases nor adapts to feedback from later in the process, does not and has never existed. This lends a bit of weight to Chad's view that strictly phased development processes "eventually devolve into necessary sub-standard Agile".
So according to my 5 minute, quasi-research, all this debate over Waterfall development is really just discussing a theoretical model presented over 37 years ago for the purpose of illustrating the advantages of iterative development. :-) Now can we all stop arguing about waterfall and get back to attacking people that misuse code comments please? :-)
This was originally posted to my previous blog. You can view the original post and any comments here.
Sometimes when I'm working on something I'll get totally absorbed, lose track of time, and end up with my head glued to the computer for hours. When I eventually finish and come up for air, I feel completely drained for the rest of the day, and I can't help but think that if I had paced myself better I would probably be more productive overall. I'm assuming this is not uncommon for geeky typed people.
Anyway, there are a few applications out there for helping you remember to take a break. One of these is WorkRave, which is specifically designed for prevention and treatment of RSI, and is released free of charge under the GPL.
I have just started playing with it but it looks great. It gives you the option of scheduling breaks of varying durations and frequencies. All the schedules work on timers that pause during idle time (so if you take a break between timer events you won't end up over-breaking :)). I have set it up to be pretty discreet (not enforcing breaks, no micro breaks etc), but it is serving as a good reminder to come up for air once an hour or so.
It also tracks daily statistics on mouse movements and keystrokes if you are interested in that sort of thing. One of the top features for me was the Exercises screen, which runs you through a few basic exercises you can do at your workstation to make sure you don't maintain a static posture for too long.
Definitely recommend having a look at this to help stay productive, and to avoid ending up with Programmer's Hands or other afflictions associated with the treacherous occupation of "computer geek".
This was originally posted to my previous blog. You can view the original post and any comments here.
During a practice coding session last night, I was writing some tests and ran into a small dilemma. I wanted to execute a method call in a loop. Wow, what a tough problem, right? :-) Anyways, my problem was that putting the method call in a for loop sort of obscured the intention of the test. Unrolling the loop was clearer, but was a pain to write.
/* Unrolled loop. Pretty obvious what calls we are expecting. */
using (mockery.Record()) {
renderer.WriteCurrentPage(1);
renderer.WritePageLink(2);
renderer.WritePageLink(3);
renderer.WritePageLink(4);
renderer.WritePageLink(5);
}
...
/*Less typing, but I'm expecting, um, 4 calls, oh I see, rendering pages 2 to 5 */
using ( mockery.Record()) {
renderer.WriteCurrentPage(1);
for (int page=2; page<=5; page++) {
renderer.WritePageLink(page);
}
}
Parsing the second sample is admittedly pretty straight forward, but I found myself liking the first style as the scenarios being tested became more complicated. What I wanted was something a bit like the Python range() function. It works like this:
>>> print range(1,10)
[1, 2, 3, 4, 5, 6, 7, 8, 9]
>>> range(10, 1, -1)
[10, 9, 8, 7, 6, 5, 4, 3, 2]
>>> print sum(range(1,10))
45
>>> print (map(lambda i: i * 10, range(1,10)))
[10, 20, 30, 40, 50, 60, 70, 80, 90]
So I thought I'd code up something similar in C# for a bit of fun (depending, of course, on your definition of "fun"). Just to be clear, I wanted a different implementation to Python's. In Python the range stops before the second parameter, so range(1, 3) gives [1, 2]. In my case I want [1, 2, 3].
A few provisos. I like provisos, because when someone points out my stupidity I can always say "It's ok! I had provisos!". First, this has probably been done before. Second, this is probably a bad idea, breaking normal loop semantics and so on. Third, this is probably a bad idea for reasons I haven't thought of yet. But, in the name of fun (definition pending), I will proceed. You can download the code I ended up with if you want to have a play around with it.
LINQ version
.NET 3.5 and LINQ already have something similar to what I want. For example:
[Test]
public void Should_be_able_to_get_enumerator_with_linq() {
List<int> rangeValues = new List<int>();
var range = (from number in new int[] { 1, 2 } select number);
foreach (int i in range) {
rangeValues.Add(i);
}
Assert.That(rangeValues, Is.EqualTo(new int[] { 1, 2 }));
}
I can even compress this to one statement, but I'm not really achieving my overall goal of test readability here.
List<int> rangeValues = new List<int>();
(from number in new int[] { 1, 2 } select number)
.ToList()
.ForEach(i => rangeValues.Add(i));
Assert.That(rangeValues, Is.EqualTo(new int[] { 1, 2 }));
Even though the (from number in new int[] { 1, 2 } select number) part makes sense, it is a lot of reading to do to determine I am working with a range of 1 to 2.
Test driving the Range class
Let's write a few tests that show how we would like this to work. (When I did this I did the tests and corresponding implementations one at a time, TDD-style. I also did them in a different order to that presented here. I'm combining stuff for the sake of brevity and coherency.)
[Test]
public void Should_be_able_to_get_enumerator() {
List<int> rangeValues = new List<int>();
foreach (int i in Range.From(1).To(2)) {
rangeValues.Add(i);
}
Assert.That(rangeValues, Is.EqualTo(new int[] { 1, 2 }));
}
[Test]
public void Should_be_able_to_get_range_from_1_to_10() {
Assert.That(Range.From(1).To(10).ToArray(),
Is.EqualTo(new int[] { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }));
}
The implementation to pass them looked a bit like this:
public class Range : IEnumerable<int> {
private readonly int start;
private int stop;
public Range(int start) {
this.start = stop = start;
}
public static Range From(int startRange) {
return new Range(startRange);
}
public Range To(int endRange) {
stop = endRange;
return this;
}
public IEnumerator<int> GetEnumerator() {
int current = start;
while (current <= stop) {
yield return current;
current++;
}
}
IEnumerator IEnumerable.GetEnumerator() {
return GetEnumerator();
}
}
Right, before plunging into my goal of mapping method calls across this range, I started thinking about negative ranges. In Python you can use range(10, 1, -1) to get [10, 9, 8, .., 2]. Maybe we should have that? I don't want to have to specify a step of -1 though. It should figure that out for itself.
[Test]
public void Should_be_able_to_get_range_from_10_to_1() {
Assert.That(Range.From(10).To(1).ToArray(),
Is.EqualTo(new int[] { 10, 9, 8, 7, 6, 5, 4, 3, 2, 1 }));
}
And while we're at it, let's give users the option to specify a step if they really want to. They might want every second number in a range.
[Test]
public void Should_be_able_to_set_a_step_value() {
Assert.That(Range.From(1).To(10).WithStep(2).ToArray(),
Is.EqualTo(new int[] { 1, 3, 5, 7, 9 }));
}
We don't want them getting OutOfMemoryExceptionS thrown while specifying steps though, so we'll need to make it tough to specify an infinite range.
[Test]
public void Should_handle_step_values_that_immediately_go_out_of_range() {
Assert.That(Range.From(1).To(10).WithStep(-1).ToArray(),
Is.EqualTo(new int[]{1}));
Assert.That(Range.From(10).To(1).WithStep(2).ToArray(),
Is.EqualTo(new int[] { 10 }));
Assert.That(Range.From(-10).To(1).WithStep(-1).ToArray(),
Is.EqualTo(new int[] { -10 }));
}
So carefully, test at a time, we get an implementation for GetEnumerator<int>() that looks a bit like this:
public class Range : IEnumerable<int> {
...
private int step;
public Range WithStep(int step) {
this.step = step;
return this;
}
...
public IEnumerator<int> GetEnumerator() {
int max = Math.Max(start, stop);
int min = Math.Min(start, stop);
if (step == default(int)) {
step = (start == min) ? 1 : -1;
}
int current = start;
while (current >= min && current <= max) {
yield return current;
current += step;
}
}
...
}
So far so good. Now let's make it easy to perform an operation over a Range. We'll add a Do() method on Range.
[Test]
public void Should_be_able_to_do_sum_with_range() {
int sum = 0;
Range.From(1).To(5).Do(i => sum += i);
Assert.That(sum, Is.EqualTo(1 + 2 + 3 + 4 + 5));
}
And to pass that:
//Range.cs
public void Do(Action<int> f) {
foreach (int i in this) { f(i); }
}
As an aside, it's also nice to know that our current implementation plays nicely with LINQ. The following test passes without modification:
[Test]
public void Should_be_able_to_use_linq_with_range() {
String oneToThree = "";
foreach (int num in Range.From(1).To(5).Where(i => i <= 3) ) {
oneToThree = oneToThree + num + " ";
}
Assert.That(oneToThree, Is.EqualTo("1 2 3 "));
}
Original goal within range...
Bad pun, sorry. We can now set our sights on rewriting the original test from the start of this post using the Range class.
[Test]
public void Test_original_mocking_scenario() {
MockRepository mockery = new MockRepository();
IPagerRenderer renderer = mockery.CreateMock<IPagerRenderer>();
using (mockery.Record()) {
//This is what we expect Range.Do to call,
//so we obviously won't use the Range class here.
renderer.WriteCurrentPage(1);
renderer.WritePageLink(2);
renderer.WritePageLink(3);
renderer.WritePageLink(4);
renderer.WritePageLink(5);
}
using (mockery.Playback()) {
renderer.WriteCurrentPage(1);
Range.From(2).To(5).Do(i => renderer.WritePageLink(i));
}
}
public interface IPagerRenderer {
void WriteCurrentPage(int i);
void WritePageLink(int i);
}
The emphasised section of the code has the same effect as the code snippets at the start of this post. You can compare the mockery.Record() to the mockery.Playback() sections to see the difference. To me this reads nicely: for each number in the Range From 2 To 5 Do the renderer.WritePageLink operation. I'm not about to going using this everywhere or anything, but I'm considering using it when it helps to communicate the intention of a test. All in all, 'twas a bit of harmless fun.
Grabbing the code
You can get a CS file containing the tests and Range code used for this post from here [links direct to CS file download]. It requires .NET 3.5, NUnit 2.4+ and Rhino Mocks (I used v3.3) to get it to work without modification. Should go without saying, but don't assume it works or is suitable for real life -- it's for solely for the purpose of mucking around :-)
This was originally posted to my previous blog. You can view the original post and any comments here.
This post is part of a small series on .NET ORM tools. You
can find the rest of them
here.
After getting a surprise kick, I got a suggestion from Josh to take a look at the Castle ActiveRecord project. So here goes, using Castle 1.0 RC3.
Scene refresher
I have a table of suppliers, and a table of states (or
provinces, territories, prefectures etc.). Both suppliers and
states have names, which are stored as strings/varchars, and IDs,
which are stored as Guids/uniqueidentifiers. Each supplier can
service many states. So we have a simple many-to-many relationship
between the two main entities. It looks a bit like this:
I am using Aussie states for my tests, so I have populated the
State table with the following names: NSW, VIC, QLD, TAS, SA, WA,
ACT, NT.
Setting up Castle ActiveRecord
I created a new C# class library project, then added references to:
- Castle.ActiveRecord.dll
- Castle.Core.dll
- Castle.Components.Validator.dll
- Castle.DynamicProxy.dll
- NHibernate.dll
I also decided to store the ActiveRecord configuration information in App.Config (there are several configuration methods mentioned in the documentation). My App.config looked like this:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="activerecord" type="Castle.ActiveRecord.Framework.Config.ActiveRecordSectionHandler, Castle.ActiveRecord" />
</configSections>
<activerecord>
<config>
<add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
<add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2005Dialect" />
<add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
<add key="hibernate.connection.connection_string" value="Data Source=127.0.0.1\SQLEXPRESS;Initial Catalog=SubSonicWorkshop;Integrated Security=True" />
</config>
</activerecord>
</configuration>
The next step was to create my entities, State and Subject:
[ActiveRecord]
public class State : ActiveRecordBase<State> {
[PrimaryKey(PrimaryKeyType.GuidComb)]
public Guid StateId { get; set; }
[Property]
public String Name { get; set; }
}
[ActiveRecord]
public class Supplier : ActiveRecordBase<Supplier> {
[PrimaryKey(PrimaryKeyType.GuidComb)]
public Guid SupplierId { get; set; }
[Property]
public String Name { get; set; }
[HasAndBelongsToMany(
typeof(State), RelationType.Bag,
Table="Supplier_StatesServiced", ColumnKey="SupplierId", ColumnRef="StateId",
Lazy=true)
]
public IList<State> StatesServiced { get; set; }
}
ActiveRecord uses attributes to specify the mapping between entities and the database. This saves some of the work I went through producing mapping files for the NHibernate example, although as ActiveRecord is built on NHibernate, I am really just specifying the same information in a different form and letting ActiveRecord fill in the blanks.
I just needed to specify a [PrimaryKey] for each entity, add an [ActiveRecord] attribute on the class and a [Property] attribute on persistent properties, and then map the Supplier.StatesServicedcollection to the many-to-many relationship in the database using [HasAndBelongsToMany]. Besides the attributes, the entities need to inherit from ActiveRecordBase<T>, which gives the entity all the ActiveRecord-smarts, but also ties it to this persistence mechanism.
Populating the database
As per my previous posts, I'll use a test fixture to run the
remainder of the code. Before we start ActiveRecording, we need to initialise ActiveRecord so it can load up our entity mappings. I did this via a static constructor in the fixture. By calling ActiveRecordStarter.Initialize() without arguments, ActiveRecord will examine the current configuration (we used App.config for this earlier) and assembly to load the appropriate mappings. I assume this is synonomous with the NHibernate SessionFactory initialisation.
[TestFixture]
public class CastleFixture {
static CastleFixture() {
ActiveRecordStarter.Initialize();
}
...
We are now ready to populate the database. The method to create a single Supplier is really nice using ActiveRecord:
private static void createSupplier(String name, String[] statesServiced) {
Supplier supplier = new Supplier();
supplier.Name = name;
supplier.StatesServiced = State.FindAll(Expression.In("Name", statesServiced));
supplier.Create();
}
As usual, we'll run the code below to populate the database with our test data:
createSupplier("Dave^2 Quality Tea", new string[] { "NSW", "VIC" });
createSupplier("ORMs'R'Us", new string[] { "NSW" });
createSupplier("Lousy Example", new string[] { "TAS", "VIC" });
createSupplier("Bridge Sellers", new string[] { "QLD" });
Querying the data
As ActiveRecord is built on NHibernate, you can use most (all?) of the standard NHibernate features for querying your entities, such as HQL (Hibernate Query Language). In addition to that power, ActiveRecord provides a simpler query layer on top of NHibernate via the ActiveRecordBase<State> base class your entities use.
Here's the two simple queries I've been using in the previous posts, first getting all suppliers and then suppliers that have an "s" in their name:
[Test]
public void Should_be_able_to_get_all_suppliers() {
IList<Supplier> suppliers = Supplier.FindAll();
Assert.That(suppliers.Count, Is.EqualTo(4));
}
[Test]
public void Should_be_able_to_get_all_suppliers_with_s_in_their_name() {
IList<Supplier> suppliers =
Supplier.FindAll(Expression.Like("Name", "%s%"));
Assert.That(suppliers.Count, Is.EqualTo(3));
}
And here's the code to get data over the supplier-state relationship, retrieving all suppliers that service NSW:
[Test]
public void Should_be_able_get_all_suppliers_that_service_NSW() {
IList<Supplier> suppliers = Supplier.FindAll(
DetachedCriteria.For<Supplier>()
.CreateCriteria("StatesServiced")
.Add(Expression.Eq("Name", "NSW"))
);
Assert.That(suppliers.Count, Is.EqualTo(2));
}
To be honest I had trouble finding good examples on querying using ActiveRecord in the documentation. I ended up reproducing the NHibernate query. I'm not sure if there is a neater way of doing this -- it wouldn't surprise me if I've missed something obvious here. Still the query works, and I think that, for the first two queries in particular, you aren't going to find a simpler, more concise or easier to understand syntax.
Vague semblance of a conclusion
There is some great information around about configuring ActiveRecord, and it was trivially easy to setup. The only real downside I found during this simple exercise was finding ActiveRecord-specific documentation on querying. I guess the required approach is to use the basic Find/FindAll methods where possible and drop back into NHibernate queries whenever you need more power.
Another consideration when using Castle ActiveRecord is the underlying design pattern itself. If you can't or won't use the ActiveRecord design pattern (for example, want to keep entities and persistence logic seperate), then it isn't going to help you much.
And finally, Castle ActiveRecord also integrates with Monorail, which may be a plus for you if you are developing MVC web applications.
This was originally posted to my previous blog. You can view the original post and any comments here.
I recently had a quick muck around with a few .NET ORM tools. I took a basic, contrived scenario and tried to implement the same operations using the tools. Here is a quick index:
The examples all go through same steps: the basic configuration required to get going, inserting a couple of records, and then getting some data back out with some simple queries, including traversing a many-to-many relationship.
8 Jan 2008: Quick clarification -- this series is not intended as a review of these tools. It is simply a quick guide to configuring and performing some very basic operations with them. Because all the posts use a (very simple) common scenario, you may be able to draw some basic conclusions about the tools, but in the end you'll probably have to evaluate them for yourself with respect to your own requirements. In which case these posts may help you get started :)
This was originally posted to my previous blog. You can view the original post and any comments here.
This post is part of a small series on .NET ORM tools. You
can find the rest of them
here.
While I was checking out a number of .NET ORM tools, I thought
I'd work through the exercise using the ORM-related bits from the
ADO.NET Entity Framework Beta 3 and the
Entity Designer CTP2.
Scene refresher
I have a table of suppliers, and a table of states (or
provinces, territories, prefectures etc.). Both suppliers and
states have names, which are stored as strings/varchars, and IDs,
which are stored as Guids/uniqueidentifiers. Each supplier can
service many states. So we have a simple many-to-many relationship
between the two main entities. It looks a bit like this:
I am using Aussie states for my tests, so I have populated the
State table with the following names: NSW, VIC, QLD, TAS, SA, WA,
ACT, NT.
Setting up Entity Framework
I created a new C# class library project, then added an ADO.NET
Entity Data Model file (.edmx) as a project item, which I named
WorkshopModel.edmx. Adding the project item runs a wizard that
lets you choose the connection, and then select which database
items to include in the model (including tables, views and SPs):
Once the wizard completes the model is opened in the designer. Note the recognition of the many-to-many relationship between suppliers and states. I edited to default association name from Supplier_StatesServiced to StatesServiced to make it a bit easier to read.
The designer provides access to loads of properties for the model, so it is possible that some of the code I am going to write further on could be simplified by tweaking the model. At this stage it feels very much like a WYSIWYG way of generating an NHibernate-style mapping file and accompanying entity classes. The designer generates a WorkshopData.Designer.cs file from my WorkshopData.edmx, which includes definitions of the Entity Framework-based classes.
Populating the database
As per my previous posts, I'll use a test fixture to run the
remainder of the code. Here is the method used to create a supplier
and map the states it services:
private static void createSupplier(String name, String[] statesServiced) {
using (WorkshopEntities entityModel = new WorkshopEntities()) {
Supplier supplier = Supplier.CreateSupplier(Guid.NewGuid(), name);
foreach (String stateName in statesServiced) {
supplier.State.Add(entityModel.State.Where(s => s.Name == stateName).First());
}
entityModel.SaveChanges();
}
}
The WorkshopEntities class gives us access to the relevant Entity Framework bits (it derives from System.Data.Objects.ObjectContext). The designer has generated a static Supplier.CreateSupplier(...) method, but I couldn't find a nice way of adding all the relevant states at once. I would have loved to do something like this:
List<State> states = (from state in entityModel.State
where statesServiced.Contains(state.Name)
select state).ToList();
states.ForEach(state => supplier.State.Add(state));
But this gave me an exception:
EntityFrameworkWorkshop.EntityFrameworkFixture.DatabaseSetup : System.NotSupportedException : The 'Boolean Contains[String](System.Collections.Generic.IEnumerable`1[System.String], System.String)' method is not recognized by LINQ to Entities, and cannot be translated into a store expression.
The Entity Framework (as of this release) cannot map the Contains method to Entity SQL. The similar LINQ to SQL query works fine. The difference is that the LINQ to SQL provider is mapping direct to TSQL, whereas the LINQ to Entities provider is mapping to Entity SQL (I think, please correct me if I'm wrong), which needs to work over a variety of sources (not just relational databases). I'm assuming it's tougher to implement something that works over all sources than simply targeting TSQL, but maybe this is just because we aren't in RTM yet.
I had a similar problem when I wanted to write entityModel.State.Where(s => s.Name == stateName).Single(), where I was told that it wouldn't map Single(), but that I might like to try First() instead. Which I did, and it all worked fine. There are a few more comments about this kind of thing here, as well as a list of supported and unsupported methods on MSDN.
Still, at least we have a nice recognition of the relationship between suppliers and states, which was a big drawback with the LINQ to SQL example. We'll then run the code below to populate the database with our test data:
createSupplier("Dave^2 Quality Tea", new string[] { "NSW", "VIC" });
createSupplier("ORMs'R'Us", new string[] { "NSW" });
createSupplier("Lousy Example", new string[] { "TAS", "VIC" });
createSupplier("Bridge Sellers", new string[] { "QLD" });
Querying the data
I'm going to favour using LINQ to Entities for querying in these examples, rather than Entity SQL. Similarly to NHibernate, which has HQL (Hibernate Query Language), Entity Framework can use a SQL-like language to query the domain objects. This gives both tools a lot of query power, at the cost of embedding logic in strings that aren't checked by the compiler and that generally won't work with refactoring tools. I used the ICriteria approach for strongly-typed queries in the NHibernate example, so I'll use LINQ to Entities for this one.
I'll quickly run through the first two simple queries I've been using in the previous posts, first getting all suppliers and then suppliers that have an "s" in their name:
[Test]
public void Should_be_able_to_get_all_suppliers() {
using (WorkshopEntities entityModel = new WorkshopEntities()) {
var suppliers = from supplier in entityModel.Supplier select supplier;
Assert.That(suppliers.Count(), Is.EqualTo(4));
}
}
[Test]
public void Should_be_able_to_get_all_suppliers_with_s_in_their_name() {
using (WorkshopEntities entityModel = new WorkshopEntities()) {
var suppliers = from supplier in entityModel.Supplier
where supplier.Name.ToLower().Contains("s")
select supplier;
Assert.That(suppliers.Count(), Is.EqualTo(3));
}
}
And here's the code to get data over the supplier-state relationship, retrieving all suppliers that service NSW:
[Test]
public void Should_be_able_get_all_suppliers_that_service_NSW() {
using (WorkshopEntities entityModel = new WorkshopEntities()) {
var suppliers = from supplier in entityModel.Supplier
where supplier.State.Any(state => state.Name == "NSW")
select supplier;
Assert.That(suppliers.Count(), Is.EqualTo(2));
}
}
This is much nicer than the LINQ to SQL version, which required explicitly joining over the relationship:
var suppliers = from supplier in db.Suppliers
join servicedState in db.Supplier_StatesServiceds
on supplier.SupplierId
equals servicedState.SupplierId
where servicedState.State.Name == "NSW"
select supplier;
Vague semblance of a conclusion
We've only really looked at the ORM side of the Entity Framework in this example, and it definitely seems very capable in this regard (at least for this simple example), but as noted by David Laribee, ORM is a very small part of the Entity Framework. It's main potential is in querying across a model that incorporates different data sources and services (say, different databases, XML sources etc), which is definitely an exciting aim.
The only real issue I had using the tool were the unmapped methods, but this was easy to work around. I think the real test of the tool will be how much you can get into the Entity Data Model and how easy it is to work with. Earlier releases faced some criticisms regarding persistance ignorance and the fact you can't use POCOs for entities, but I believe some of the issues are being addressed (thanks to Tom for the link) by the ADO.NET team based on the feedback they have received.
This was originally posted to my previous blog. You can view the original post and any comments here.
This post is part of a small series on .NET ORM tools. You can find the rest of them here.
As part of my continuing efforts to fragrantly misuse a number of .NET ORM tools, here is my effort with LinqToSql. The usual proviso applies: all of this is really quick and hacky, as it is just to get a little familiarity with the tool rather than to uncover any "best practices" or similar.
Scene refresher
I have a table of suppliers, and a table of states (or provinces, territories, prefectures etc.). Both suppliers and states have names, which are stored as strings/varchars, and IDs, which are stored as Guids/uniqueidentifiers. Each supplier can service many states. So we have a simple many-to-many relationship between the two main entities. It looks a bit like this:
I am using Aussie states for my tests, so I have populated the State table with the following names: NSW, VIC, QLD, TAS, SA, WA, ACT, NT.
Setting up LinqToSql
I created a new C# class library project, then added a LinqToSql Classes project item, which I named WorkshopDb.dbml. In true Microsoft style, you simply drag and drop the tables from the database onto the designer, which generates the necessary classes for you:
This adds an app.config file to the project, containing the relevant connection string. We are now ready to go!
Populating the database
As per my previous posts, I'll a test fixture to run the remainder of the code. After cleaning out my little database again, I'll add a method to encapsulate the process of creating a supplier and mapping the states it services:
private static void createSupplier(String name, String[] statesServiced) {
WorkshopDbDataContext db = new WorkshopDbDataContext();
Supplier supplier = new Supplier();
supplier.SupplierId = Guid.NewGuid();
supplier.Name = name;
List<State> states = (
from state in db.States
where statesServiced.Contains(state.Name)
select state
).ToList();
states.ForEach(
state => supplier.Supplier_StatesServiceds.Add(
new Supplier_StatesServiced() {
SupplierId = supplier.SupplierId,
StateId = state.StateId
}
));
db.Suppliers.InsertOnSubmit(supplier);
db.SubmitChanges();
}
As with SubSonic, LinqToSql does not automatically let me traverse the many-to-many relationship, but the new ForEach method makes it pretty easy to map each state to the supplier.Supplier_StatesServiceds collection (man, I really should have aliased that mapping table first).
We can now use that method to add the following test data:
createSupplier("Dave^2 Quality Tea", new string[] { "NSW", "VIC" });
createSupplier("ORMs'R'Us", new string[] { "NSW" });
createSupplier("Lousy Example", new string[] { "TAS", "VIC" });
createSupplier("Bridge Sellers", new string[] { "QLD" });
Querying the data
Let's run through the usual tests. First, getting a list of all suppliers.
[Test]
public void Should_be_able_to_get_all_suppliers() {
WorkshopDbDataContext db = new WorkshopDbDataContext();
var suppliers = from supplier in db.Suppliers select supplier;
Assert.That(suppliers.Count(), Is.EqualTo(4));
}
So far so good. Now let's get all suppliers with an "s" in their name.
[Test]
public void Should_be_able_to_get_all_suppliers_with_s_in_their_name() {
WorkshopDbDataContext db = new WorkshopDbDataContext();
var suppliers = from supplier in db.Suppliers
where supplier.Name.ToLower().Contains("s")
select supplier;
Assert.That(suppliers.Count(), Is.EqualTo(3));
}
And finally, let's navigate over the supplier-state relationship to get all suppliers that service NSW:
[Test]
public void Should_be_able_get_all_suppliers_that_service_NSW() {
WorkshopDbDataContext db = new WorkshopDbDataContext();
var suppliers = from supplier in db.Suppliers
join servicedState in db.Supplier_StatesServiceds
on supplier.SupplierId
equals servicedState.SupplierId
where servicedState.State.Name == "NSW"
select supplier;
Assert.That(suppliers.Count(), Is.EqualTo(2));
}
Pretty straight forward. This actually generates very similar SQL to the NHibernate example, but because I never actually get a list from the suppliers expression, the suppliers.Count() call actually uses SELECT Count(*) ... (I believe you can do similar queries in both NHibernate and SubSonic). The following is roughly what is executed via sp_executesql:
SELECT COUNT(*)
FROM [dbo].[Supplier]
INNER JOIN [dbo].[Supplier_StatesServiced] ON [Supplier].[SupplierId] = [Supplier_StatesServiced].[SupplierId]
INNER JOIN [dbo].[State] ON [State].[StateId] = [Supplier_StatesServiced].[StateId]
WHERE [State].[Name] = @p0
Vague semblance of a conclusion
LinqToSql was extremely easy to use, especially in the initial configuration department. Like NHibernate, the query syntax takes a bit of getting used to, but it is something that becomes familiar fairly quickly.
I should point out that both SubSonic and NHibernate currently target the .NET 2.0 world, so the "Language INtegrated Query" part of LinqToSql was always going to give LinqToSql a bit of an expressiveness advantage. If you are a big fan of LINQ queries, they may be coming to an ORM tool near you in the not-too-distant future now that .NET 3.5 has been released. That said, I still quite like how the query criteria works in the NHibernate example.
I also noticed that NHibernate was more domain model (i.e. classes) focused, whereas the LinqToSql query for retrieving all the suppliers that service NSW was more data-schema focussed (using the JOIN construct rather than have a working knowledge of the relationship). This isn't meant as praise or criticism of either, just a difference in the approaches. As a quick side note, I believe the ADO.NET Entity Framework is meant to have more advanced support for many-to-many relationships.
So far I've actually felt SubSonic was the most difficult to use for this particular scenario, but this is largely a result of the contrived example I used. I have used SubSonic a few times and in general it is exceptionally straight forward to get working.
As I was mainly looking into how each tool tackled this particular scenario, I have not gone into the different architectural approaches of the tools (ActiveRecord vs. DataMapper, implications for testability and persistence ignorance etc.). It's definitely worth looking into this side of things if you are unfamiliar with the tools. Ian Cooper has a great post on some of these issues as applied to LinqToSql.
That's it for now. Good luck on your ORM travels!
This was originally posted to my previous blog. You can view the original post and any comments here.
This post is part of a small series on .NET ORM tools. You can find the rest of them here.
After messing around with SubSonic, I thought I'd run through the same scenario with NHibernate. A quick disclaimer: both tools a very different, so comparing them is a bit like comparing apples and oranges. As an example, a large part of SubSonic is the generation of DAL classes. There are ways of generating relevant NHibernate artifacts, but they aren't really an integral part of the tool.
Still, I'm not going to let a simple thing like facts get in the way of proceeding. I was interested to see how to perform the same basic scenario as last time around using NHibernate. And as per last time, all of this is really quick and hacky, as it is just to get a little familiarity with the tool rather than to uncover any "best practices" or similar. So here goes...
Scene refresher
I have a table of suppliers, and a table of states (or provinces, territories, prefectures etc.). Both suppliers and states have names, which are stored as strings/varchars, and IDs, which are stored as Guids/uniqueidentifiers. Each supplier can service many states. So we have a simple many-to-many relationship between the two main entities. It looks a bit like this:
I am using Aussie states for my tests, so I have populated the State table with the following names: NSW, VIC, QLD, TAS, SA, WA, ACT, NT.
Setting up NHibernate
I created a new C# class library project and added a reference to NHibernate. I chucked in an app.config for NHibernate's benefit that ended up like this:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="nhibernate"
type="System.Configuration.NameValueSectionHandler, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
/>
</configSections>
<nhibernate>
<add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
<add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2005Dialect" />
<add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
<add key="hibernate.connection.connection_string" value="Data Source=127.0.0.1\SQLEXPRESS;Initial Catalog=SubSonicWorkshop;Integrated Security=True" />
</nhibernate>
</configuration>
Rather than using any NHibernate auto-mapping or code generation, I manually created the classes I wanted. I am using the automatic property accessors in C# 3.0, so if you're still using 2.0 you'll need to manually add a backing store for these.
/* State.cs */
public class State {
public Guid StateId { get; set; }
public String Name { get; set; }
}
/* Supplier.cs */
public class Supplier {
public Guid SupplierId { get; set; }
public String Name { get; set; }
public IList<State> StatesServiced { get; set; }
}
I then need to tell NHibernate how I want to map between these classes and my relational data. You can do this by creating Hibernate Mapping files (.hbm.xml), and setting them to be included as embedded resources in the compiled DLL. Again, I did this manually, but you specify use attributes on your classes to do this auto-magically, or generate the classes, database, and/or mapping files using a variety of tools.
First let's look at the mapping for states (State.hbm.xml):
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NHibernateWorkshop.Tests"
namespace="NHibernateWorkshop.Tests">
<class name="State" table="State" lazy="false">
<id name="StateId" type="guid">
<generator class="guid" />
</id>
<property name="Name" type="String" />
</class>
</hibernate-mapping>
Here I am telling NHibernate that a State has a StateId, which is the primary key. I am also telling NHibernate that it can generate this ID as a new Guid. I also let it know it can persist the Name property as a String. By default NHibernate will match the property name with the column name, but you can also specify that a property matches to a different column name if you like.
Now for the more interesting entity mapping, Supplier.hbm.xml:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
assembly="NHibernateWorkshop.Tests"
namespace="NHibernateWorkshop.Tests">
<class name="Supplier" table="Supplier" lazy="false">
<id name="SupplierId" type="guid">
<generator class="guid" />
</id>
<property name="Name" type="String" />
<bag name="StatesServiced" table="Supplier_StatesServiced">
<key column="SupplierId" />
<many-to-many column="StateId" class="State" />
</bag>
</class>
</hibernate-mapping>
This is very similar to the last mapping, with the exception of the <bag> property. This tells NHibernate that I have a many-to-many relationship between State and Supplier, and that it can navigate this relationship using the Supplier_StatesServiced table and the SupplierId and StateId keys.
Creating a test fixture for messing around
As I mentioned last time, I like working in test fixtures, so I created a new test fixture to run the remainder of the code in this post. Before I start using NHibernate within the fixture, I want to get access to the NHibernate ISession. I guess you could think of ISession as a conversation between NHibernate and the database. I'll do this by exposing a ISessionFactory property (note, there are optimal ways of doing this... this ain't it):
protected static readonly ISessionFactory SessionFactory = initialSessionFactory();
private static ISessionFactory initialSessionFactory() {
Configuration config = new Configuration();
config.AddAssembly("NHibernateWorkshop.Tests");
return config.BuildSessionFactory();
}
I am going to use this property to create sessions as required. The AddAssembly(...) call gets NHibernate to load in all the .hbm.xml mapping files we created earlier, so the sessions we create know how to persist our entities.
Populating the database
Now we can get to work populating our database (I cleaned it out from last time). We are now on par with the SubSonic example. The steps and method signatures are going to be very similar from here on in. So like last time, I'll write a method to encapsulate the process of creating a supplier and mapping the states it services:
private static void createSupplier(String name, String[] statesServiced) {
using (ISession session = SessionFactory.OpenSession())
{
Supplier supplier = new Supplier();
supplier.Name = name;
IList<State> states = session
.CreateCriteria(typeof (State))
.Add(
Expression.In("Name", statesServiced)
)
.List<State>();
supplier.StatesServiced = states;
session.SaveOrUpdate(supplier);
session.Flush();
session.Close();
}
}
Wow. To me all that additional setup now starts to seem worth it. I create a normal object and set the name property. I then create a criteria object that is going to query states, and add a restriction that the state's name must be in the array of statesServiced we passed in. I then assign the results to the supplier object, and ask the NHibernate session to SaveOrUpdate the supplier. (SaveOrUpdate means NHibernate will automatically determine whether it needs to INSERT or UPDATE the relevant database record.) As ISession represents a chat with a database, the whole thing is wrapping in a using (...) { } to ensure any resources used are cleaned up nicely.
I then used the following code to insert the same data as last time:
createSupplier("Dave^2 Quality Tea", new string[] { "NSW", "VIC" });
createSupplier("ORMs'R'Us", new string[] { "NSW" });
createSupplier("Lousy Example", new string[] { "TAS", "VIC" });
createSupplier("Bridge Sellers", new string[] { "QLD" });
Querying the data
Let's get a list of all the suppliers:
[Test]
public void Should_be_able_to_get_all_suppliers() {
using (ISession session = SessionFactory.OpenSession()) {
IList<Supplier> suppliers = session.CreateCriteria(typeof (Supplier)).List<Supplier>();
Assert.That(suppliers.Count, Is.EqualTo(4));
}
}
That's pretty much on a par with the SubSonic equivalent. The test passes. Now let's get all the suppliers with an "s" in their name:
[Test]
public void Should_be_able_to_get_all_suppliers_with_s_in_their_name() {
using (ISession session = SessionFactory.OpenSession()) {
IList<Supplier> suppliers = session
.CreateCriteria(typeof (Supplier))
.Add(Expression.Like("Name", "%s%"))
.List<Supplier>();
Assert.That(suppliers.Count, Is.EqualTo(3));
}
}
Again, pretty simple, and very similar to the SubSonic version. Now to the tricky example. We want to navigate over the supplier-state relationship and get all the suppliers that service NSW. This one wasn't pretty in SubSonic.
[Test]
public void Should_be_able_get_all_suppliers_that_service_NSW() {
using (ISession session = SessionFactory.OpenSession()) {
IList<Supplier> suppliers = session
.CreateCriteria(typeof (Supplier))
.CreateCriteria("StatesServiced")
.Add(Expression.Eq("Name", "NSW"))
.List<Supplier>();
Assert.That(suppliers.Count, Is.EqualTo(2));
}
}
Again, wow. That was too easy. First we create a criteria that is going to return Supplier data. Then we want a sub-criteria that is going to use the StatesServiced property of the Supplier entity (not the table -- we are strictly dealing in domain objects here). We then say we only want the StatesServiced collection to include NSW. So what SQL ends up hitting the database?
SELECT {fields for supplier and state}
FROM Supplier
INNER JOIN Supplier_StatesServiced ON
Supplier.SupplierId=Supplier_StatesServiced.SupplierId
INNER JOIN State ON Supplier_StatesServiced.StateId=State.StateId
WHERE State.Name = @p0
This is the query executed via a call to sp_executesql on SQL Server (slightly modified to better communicate the point, but the actual generated query is still very neat), with "NSW" passed as @p0.
This was very different to the experience with SubSonic, which I couldn't get to sensibly navigate over the many-to-many relationship.
Vague semblance of a conclusion
Ignoring the initial setup and configuration (as this can all be generated, but to be honest it was all fairly quick), I really enjoyed working with NHibernate. While the querying looked a little daunting to me at first glance, after about 3 seconds I found it very intuitive. Sometimes when I was working with SubSonic I found myself scratching my head as to how to get back specific information. Not so with NHibernate, as the query options I needed* were all fairly discoverable, and I never felt like I was going to have to fight the tool to get stuff out.
Again, there are vast differences between the SubSonic and NHibernate approaches, and there a probably situations to which one is better suited than the other. Rob Conery has a good post about the strengths and weaknesses of both tools, as well as LinqToSql.
Here are a couple of chapters from the official documentation that I used to get NHibernate going:
* Luckily I didn't need Projections or similar. That may have started to get a bit hairier.
This was originally posted to my previous blog. You can view the original post and any comments here.
More Posts
Next page »