0

We have a search routine that uses criteria to build SQL query (because its restrictions added dynamically). In a particular case (a very complicated case) we need to search over a table-valued function.(our model object is mapped to the function).

The result would be something like this :

SELECT count(*) FROM dbo.GetSubStaffsLetterInstances(@staffId) WHERE LetterNumber="1234";

The problem is I don't know how to pass @staffId to my criteria(I tried adding an Eq restrictions without success since restrictions are working on properties) I know I can add a parameter to an IQuery but I don't know how I can do it using an ICriteria object.

Beatles1692
  • 5,214
  • 34
  • 65

1 Answers1

1

If I understand your question completely, you can resort back to standard SQL:-

var sql = "SELECT count(*) FROM dbo.GetSubStaffsLetterInstances(:staffId) 
   WHERE LetterNumber=:letterNum";

var count = session.CreateSqlQuery(sql)
  .setInt32("staffId", 12345)
  .setString("letternum", "A1")
  .UniqueResult<int>();

or try .UniqueResult<long>(); as I can't remember which one HQL returns

Rippo
  • 22,117
  • 14
  • 78
  • 117
  • SELECT count(*) FROM dbo.GetSubStaffsLetterInstances(:staffId) WHERE LetterNumber=:letterNum is the production of a criteria and is produced dynamically.It can be different according to what a user wants to search. – Beatles1692 Jul 04 '12 at 21:26
  • Only HQL will allow you to work with table valued functions. Have you read this http://stackoverflow.com/questions/4249885/can-i-use-table-valued-function-as-query-source-in-nhibernate – Rippo Jul 04 '12 at 21:33