NHibernate Linq : how to use StartsWith on an integer type

I'd like to execute a query like this but it fails :

session.Linq&开发者_开发百科lt;Product>().Where(p=>p.Id.ToString().StartsWith("123"))

Is there a workaround ? I'd prefer to use LINQ... maybe there is a easy way to extend the linq provider ?

I'm using NHibernate 2.1.2

EDIT: Yes, it fails because NHIbernate.Linq doesn't handle ToString()

I agree with SLaks in the comments that if you need string comparison on the product Id field, then it should be a char or varchar field to begin with.

Is the number of digits in a product Id constant? In that case you could write something like:

session.Linq<Product>().Where(p=>p.Id >= 10000 && p.Id < 20000)

Even if you don't know the number of digits, you could write

session.Linq<Product>().Where(p=> 
    (p.Id >= 10 && p.Id < 20) || 
    (p.Id >= 100 && p.Id < 200) || 
    (p.Id >= 1000 && p.Id < 2000) || 
    (p.Id >= 10000 && p.Id < 20000) || ... );

Well, it's ugly, but it will work :-)

By the way, you can get the first digit in T-SQL by using the following expression:

(Product.Id / POWER(10, FLOOR(LOG10(Product.Id))))

However, I don't think the Linq provider of NHibernate 2.1.2 is able to map the .Net math functions to corresponding T-SQL functions. You could give it a try:

session.Linq<Product>().Where(p => p.Id / Math.Pow(10, Math.Floor(Math.Log10(p.Id))) == 1)

If you upgrade to NHibernate 3, you can extend the LINQ provider so it recognizes an expression like p.Id.NumberStartsWith(1) (this will wrap any of the approaches suggested as answers to this question)

It's a little bit of work, but once it's done, the client code will look pretty nice.

Plus, you'll have a lot of fun :-)

If you absolutely need to do this, what you might want to do is create a parallel column that could be the string version of your key. Using NHibernate interceptors, a database trigger, or a host of other options would keep the field updated and in sync without muddying up your code.

I think I would avoid doing this, but that is one way you could go.

The query likely fails because NHibernate does not know how to translate a ToString() (or the various parses) to CAST/CONVERT methods.

I agree with Elian and SLaks - if you need a single "character" out of the product ID, this ID should be a varchar. There is no truly elegant way to get this query to work otherwise; if it were a string, the StartsWith() function would be converted to a ProductId LIKE 'x%' condition with little fuss.