Sunday, October 30, 2011

LINQ Pitfalls: Casting

One thing that needs to be always remembered when writing NHibernate.Linq queries is that it is going to be translated into SQL eventually. What this means is that we can't do everything in our Select or Where conditions - we are restricted by the capabilities of underlying database and SQL language itself.

Let's say our rivers have delta type stored in the database as plain string and in our domain model we introduced an enum type for convenience:

public class River
{
// ...
public virtual string DeltaType { get; set; }
}

public enum DeltaType
{
None,
WaveDominated,
TideDominated,
Gilbert,
Estuary
}

Now we're going to issue a query that should return all the rivers that has one of the delta types given:

repository.FetchByDeltaTypes(new[] { DeltaType.Estuary, DeltaType.Gilbert });

The first try can look like this:

public IEnumerable<River> FetchByDeltaTypes(IEnumerable<DeltaType> types)
{
IEnumerable<River> data;
using (var tx = _session.BeginTransaction())
{
data = _session.Query<River>()
.Where(r => types.Contains(
(DeltaType)Enum.Parse(typeof(DeltaType), r.DeltaType)
)).ToList();

tx.Commit();
}

return data;
}

Here I'm trying to convert string from entity into an enum value for comparison. When tried to run it, I've got NotSupportedException with quite reticent, but pretty clear message "System.Object Parse(System.Type, System.String)". NHibernate complains here that it can't translate Parse method call into corresponding SQL statement. Well, I can't think of that translation either - SQL doesn't know our enum types etc.

We have to think of another solution - we have to provide the database with strings that it can understand. What we can do here is to change all the enum values into strings first and then pass it to the query:

public IEnumerable<River> FetchByDeltaTypes(IEnumerable<DeltaType> types)
{
IEnumerable<River> data;
using (var tx = _session.BeginTransaction())
{
var typesStrings = types.Select(x => x.ToString()).ToList();

data = _session.Query<River>()
.Where(r => typesStrings.Contains(r.DeltaType))
.ToList();

tx.Commit();
}

return data;
}

No big difference for our code, big difference for NHibernate. It is now trivial to translate this Where clause into SQL. Conclusion - it is always important to think how the query is going to be translated into SQL.

Tuesday, October 25, 2011

LINQ Pitfalls: Nested queries

NHibernate's LINQ provider is very easy to start with and sufficient for majority of use cases, but when used in more complex scenarios it can mislead and surprise. Here is one of the pitfalls (or perhaps bugs?) I've recently run into. Note that I'm using NHibernate 3.0 and the issue is already fixed, but general conclusions possibly still apply.

I have simple model with two-step relationship: River has many Countries, Country has one Continent. I want to query for all rivers located within given continent. For code simplicity, let's try to use two separate LINQ queries:

var countries = session.Query<Country>()
.Where(c => c.Continent.Id == continentId);

var data = session.Query<River>()
.Where(r => (r.Countries.Where(c => countries.Contains(c))).Any())
.ToList();

The Where condition in second query is satisfied if at least one of river's countries exists in the country list for given continent. I don't call ToList() on first query, so I assume it is not run separately but merged (nested) into second one (or at least NotSupportedException is thrown or something). Here is the query that actually runs:

What do we have here? It gets all rivers, filter it for rivers that are within countries (country2_ alias), that are equal to ANY country (country3_ alias) - no continent condition at all! The query is merged, it runs without errors and silently produces unexpected, wrong results.

How can we enforce LINQ provider to filter the results correctly? The easiest way is to materialize the list of countries in separate query to avoid buggy query merging. So let's add ToList() to the first query:

var countries = session.Query<Country>()
.Where(c => c.Continent.Id == continentId)
.ToList();

var data = session.Query<River>()
.Where(r => (r.Countries.Where(c => countries.Contains(c))).Any())
.ToList();

This is how these queries look like in SQL:

This time we are fetching the list of countries first and the second query is correctly filtered using IN clause. But this is not the best solution, for sure. There are two queries instead of one and we don't really need country values, especially if there may be thousands of rows in real-life model. In this case, we should try to rewrite the queries to express our filtering logic in single query. In River-Country-Continent example, it will look like this:

data = session.Query<River>()
.Where(r => r.Countries.Any(c => c.Continent.Id == continentId))
.ToList();

This time the SQL statement contains continent filter and results are as expected:

Conclusion: Use the latest stable versions of your software. Be careful when merging LINQ queries in NHibernate. Ensure all ToList's are in place or find different way to express your filtering. And always check what SQL is your query producing!

Saturday, October 22, 2011

FluentNHibernate: How to globally set id's of all entities as assigned?

Sometimes you have a model in which all entities identifiers are controlled externally from your database - i.e. they are from external systems or you have some kind of sequence generator running separately from the database. In this case, when using Fluent NHibernate, we can turn off generating identity values in database in every mapping using:

Id(x => x.Id).GeneratedBy.Assigned();

But for larger models this may be a bit repetitive. And again, this is where conventions are to help. We can implement very simple convention, that will cover all our entities:

public class AssignedIdConvention : IIdConvention
{
public void Apply(IIdentityInstance instance)
{
instance.GeneratedBy.Assigned();
}
}

And then register it in Fluent NHibernate configuration like this:

Fluently.Configure()
.Mappings(...)
.Conventions.Add<AssignedIdConvention>()

That's all!

But be advised that in general, managing identity values on our own is not the best practice and there is only one use case I can think of, when it is necessary - when we use external data sources (i.e. when our database contains data imported from other databases, like global customers database or authorization system). Note that using natural keys, like Tax Identification Numbers or similiar, can be misleading, as the real world can surprise us in many ways and we'll have no way to handle user without that number or two users with duplicate numbers (and this may happen for sure).

Wednesday, October 19, 2011

Preview of method return value in VS Debugger

I'm probably not going to invent a wheel here, but this is something I'm quite satisfied to figure out. One of the things that annoyed me when using Visual Studio Debugger was that I coudn't see the value that will be returned from a method I'm currently debugging, i.e.:

public int Test()
{
return GetSomeValue();
}

When in line 3, I would expect debugger to allow me to see the value returned by GetSomeValue() method as it certainly knows it. I used to modify the code so that I have a temporary variable for the value or repeat the method call in immediate window. Both ways are quite bizzare.

It turns out that VS already can show the value to me pretty easily using Quick Watch window. I just need to select the expression I want to check and press Ctrl+Alt+Q (or choose Quick Watch from context menu).

Note that it is not exactly the preview of the value, that was returned by GetValue(). It is actually evaluating the selected expression, so be careful when the method being debugged has side effects. See this example:

public class DebugViewTest
{
private int counter;

public int Test()
{
return GetValueWithSideEffect();
}

private int GetValueWithSideEffect()
{
counter++;
return counter;
}
}

When checking the value returned from the GetValueWithSideEffect() method, we'll encounter the different value than normal execution path encounters, as Quick Watch will execute the method's code and increment the counter. Anyway, this method will be sufficient for our debugging in most cases.

Tuesday, October 18, 2011

Mapping collection of strings as nvarchar(max) using FluentNHibernate

The main feature of FluentNHibernate that seems to simplify user experience with NHibernate is its easily chainable, fluent API. But when we want something more complex, we may find it quite surprising.

One of the most common examples of non-default mapping of a property is when we need to change the column size for string property. By default, string properties are mapped tonvarchar(255) or so. We very often need to handle longer strings, too. For single string property, the way to do it is quite strange, but easy:

Map(x => x.Name).Length(4001);

I use SQL Server's magic number 4001 here - column can't be that long and column generated in the database is actually nvarchar(max). The situation gets complicated when we have a collection of strings and we want to keep those strings in the table with nvarchar(max) column named Value. It seems it should go like this:

HasMany(x => x.Names).Element("Value", x => x.Length(4001));

Second lambda parameter in Element method is to provide custom element mapping - sounds like exactly what we want. But no - it has no effect and generated schema still contains column of default length.

To achieve this pretty simple task, I needed to map it like this:

HasMany(x => x.Names).Element("Value", x => x.Columns.Single().Length = 4001);

So this is where FluentNHibernate's fluent API ends. I'm not an XML configuration fan, but using XML it will be obvious how to do this mapping. In Fluent though, it took me some time to figure it out.

NOther welcome post

Hi. Welcome to yet another developer blog. We'll see how it looks like - I don't know yet. I came from web development world, now working mainly on ASP.NET MVC applications. My general interests are somewhere in between applications design & architecture. More specifically, recently I am spending quite a lot of time enjoying or cursing NHibernate and that is probably the topic that we'll start with. Hope we all enjoy it!