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.