NHibernate / Linq creating invalid SQL with SQL CE

Question

I'm using NHibernate 3.1 with SQL CE 4 (Using the MsSqlCe40Dialect). Using Linq to produce queries.

If I use String.Contains, String.StartsWith or String.EndsWith, invalid SQL is produced.

For example:

Session.Query<User>.Where(user => user.Name.Contains("Joe"))

produces SQL like this:

SELECT Name FROM User WHERE (User.Name like ('%'||@p0||'%'))

This is somewhat simplified from the actual SQL produced, but the problem is in the extra '||...||'

I expect this is a problem with the SQL CE dialect, but I'm not sure where to go from here. Any ideas for a fix / workaround?

This is actually part of a larger query. I'd like to keep it just Linq if possible. Are you aware of any way to combine Linq with HQL or criteria? — andypaxo
– andypaxo, Commented May 10, 2012 at 1:17

andypaxo · Accepted Answer · 2012-05-10 16:33:44Z

1

Looks like this was fixed shortly after the 3.1 release (in this commit). Unfortunately, I cannot upgrade NHibernate due to other dependencies. In the meantime, I have worked around the problem by subclassing MsSqlCe40Dialect with the following code:

using NHibernate;
using NHibernate.Dialect;
using NHibernate.Dialect.Function;

namespace DataAccess
{
    public class CustomMsSqlCe40Dialect : MsSqlCe40Dialect
    {
        public CustomMsSqlCe40Dialect ()
        {
            RegisterFunction("concat", new VarArgsSQLFunction(NHibernateUtil.String, "(", "+", ")"));
        }
    }
}

answered May 10, 2012 at 16:33

andypaxo

6,7193 gold badges40 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

NHibernate / Linq creating invalid SQL with SQL CE

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related