lundi 6 avril 2015

NHiberate : How to add a criteria on "ROWID" (SQLite special column)

I have a view on an SQLite table that I create using NHibernate Criteria. Now I want to get the position of one specific row inside the view, of which I happen to know the Id.


Since I am using GUIDs as primary key I refer to ROWID column, which every SQLite table has.


The SQL should look like this (tested and works fine in SQLite):



SELECT COUNT(*) FROM Supplier
WHERE ROWID < (SELECT ROWID FROM Supplier WHERE Id = 'e3a279f6-6761-44a8-b037-f4ea82e9595f')
AND [view restrictions]


Now I want to make a NHibernate query that does the same using Criteria:



var rowidSubQuery = DetachedCriteria.For<Supplier>()
[.. flexible view criteria here ..]
.Add(Restrictions.Eq("Id", item.Id))
.SetProjection(Projections.SqlProjection(
"ROWID as row", new[] { "row" }, new IType[] { NHibernateUtil.String }));

int pos = session.CreateCriteria(typeof(Supplier))
.Add(Restrictions.Lt("ROWID", rowid))
.SetProjection(Projections.RowCount())
.UniqueResult<int>();


But it fails with a QueryException "could not resolve property: ROWID of: Supplier". I understand that I don't have a property Supplier.ROWID, but ROWID is a valid column in SQLite. How can I still access it?


The DetachedCriteria itself works. The problem seems to be in the line



.Add(Restrictions.Lt("ROWID", rowid))

Aucun commentaire:

Enregistrer un commentaire