1

I've got linq to nhibernate query:

var listka = 
    from i in FakturyZakupu.Queryable 
    where String.Compare(i.REJESTRY.REJ_KOD,sbWartoscBetween1.ToString()) >= 0 
    && String.Compare(i.REJESTRY.REJ_KOD,sbWartoscBetween2.ToString()) <= 0 
    select i;

lista = listka.ToList();

And it is compiling very well, but if I use it, the exception is thrown:

NotSupportedException int32 CompareTo(System.String, System.String)

How could I take using linq query string values between two values. Like in SQL: select * from table where id between a and b ?

Kevin Aenmey
  • 13,259
  • 5
  • 46
  • 45
BKl
  • 415
  • 1
  • 8
  • 23

3 Answers3

4

NHibernate's Linq provider is very extendable. You could extend it to allow any expression, as long as you can write that expression in HQL, since NHibernate's Linq is converting to HQL.

So, if you write the extension for Between, your code could look like this:

var listka = 
    from i in FakturyZakupu.Queryable 
    where i.REJESTRY.REJ_KOD.Between(sbWartoscBetween1, sbWartoscBetween2) 
    select i;

lista = listka.ToList();

Here are some links to get you started:

Miroslav Popovic
  • 12,100
  • 2
  • 35
  • 47
  • Your first and fifth links are the same. Maybe you were willing to link to http://fabiomaulo.blogspot.fr/2010/07/nhibernate-linq-provider-extension.html for your first. And you may want to add http://weblogs.asp.net/ricardoperes/custom-linq-extensions-for-nhibernate to your list, it is the lightest way to add some SQL function support. – Frédéric Mar 24 '16 at 06:58
  • @Frédéric thanks. You're right. Fixed the link and added new "Custom LINQ Extensions for NHibernate" link. – Miroslav Popovic Mar 24 '16 at 11:20
0

In NHibernate v3.3.3, String.Compare is supported. A String.Compare(MyProp, "value") > 0 in a Where expression would produce sql similar to where MyProp > 'value'.

Chad
  • 2,378
  • 2
  • 26
  • 27
-1

With queries like that, you can avoid the compare to by simply using the greater than (>) or less than (<) operators instead of String.Compare. For example:

var listka =
    from i in FakturyZakupu.Queryable
    where i.REJESTRY.REJ_KOD > sbWartoscBetween1.ToString() &&
    i.REJESTRY.REJ_KOD < sbWartoscBetween2.ToString()
    select i;

Your success with may depend upon your database's interpretation of string comparisons, but should generally work just fine.

Bytemaster
  • 166
  • 7