1

How could I declare a parameter(ids) and add it to where part of select in NHibernate using ICriteria API?

DECLARE @ids xml ='<ScopeIds><id>417444AC-6C57-4CB7-91E1-6E0B5832EDBB</id></ScopeIds>'

SELECT * FROM MyTable p 
WHERE 
 /* other criterion list */ 
 AND @ids.exist('/ScopeIds[id=sql:column("ScopeId")]') = 1
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
dbardakov
  • 651
  • 1
  • 8
  • 22

1 Answers1

1

One way here could be SqlProjection:

// the source xml snippet            
var xml = "<ScopeIds><id>417444AC-6C57-4CB7-91E1-6E0B5832EDBB</id></ScopeIds>";

// this SQL statement will represent the xml creation and call to '.exist'
var sql = " CAST('" + xml + "' AS xml)" +
          "     .exist('/ScopeIds[id=sql:column(\"ScopeId\")]') " +
          " AS idExists";

// here we declare the SQL Project, NHibernate how to manage low level sql
var projection = Projections.SqlProjection( sql
            , new string[] {"idExists"}
            , new IType[] {NHibernateUtil.Int32}
            );


// the criteria
var criteria = session.CreateCriteria<MyEntity>();

// and here we compare the above restriction if == 1 (is true)
criteria.Add(Restrictions.Eq(projection, 1));

// all other restrictions
...
Radim Köhler
  • 122,561
  • 47
  • 239
  • 335
  • You saved a night of undisturbed sleep for me, Radim) I was stuck at creating complete `SqlCriterion`. – dbardakov Apr 21 '14 at 21:04
  • Steel I have a feeling of doing wrong things - what I really need is to create "IN (Very_Large_List_Of_Guids)" criterion. Any advice? – dbardakov Apr 22 '14 at 05:11
  • If possible, for IN statement is much more better to use just the `IN` clause (Restrictions.In()), and pass an array of searched GUIDS. If this array is too large, something is wrong with the requierement. I would prefer to place these into DB and then use `Subquery`. An example of subquery syntax http://stackoverflow.com/questions/16621961/ – Radim Köhler Apr 22 '14 at 05:28