How to select from a subselect on NHibernate
How can I map this SQL using NHibernate Criteria API?开发者_如何学编程
Sql:
SELECT COUNT(*) FROM (
SELECT FirstName, LastName FROM Employees GROUP BY FirstName, LastName
) AS Query
This is a very very simple query, my query has a SubSelect much more complex.
So, any idea?
I found a solution for the question, it's a very big hack but it works as expected.
I had to get the generated SQL and surround it with the SELECT COUNT(*) query. Here is the code to do that:
public ISQLQuery BuildCountQuery(ICriteria criteria)
{
CriteriaImpl c = (CriteriaImpl)criteria;
SessionImpl s = (SessionImpl)c.Session;
string entityOrClassName = ExtractRealClassName(c);
SessionFactoryImpl factory = (SessionFactoryImpl)s.SessionFactory;
String[] implementors = factory.GetImplementors(entityOrClassName);
string implementor = implementors.Length == 0 ? null : implementors[0];
var persister = (IOuterJoinLoadable)factory.GetEntityPersister(implementor);
CriteriaLoader loader = new CriteriaLoader(persister, factory, c, implementor, s.EnabledFilters);
SqlString sql = loader.SqlString.Insert(0, "SELECT COUNT(*) FROM (");
sql = sql.Append(") AS Query");
var parameters = loader.Translator.CollectedParameters;
var sqlQuery = this.session.CreateSQLQuery(sql.ToString());
for (int i = 0; i < parameters.Count; i++)
sqlQuery.SetParameter(i, parameters.ElementAt(i).Value, parameters.ElementAt(i).Type);
return sqlQuery;
}
private string ExtractRealClassName(CriteriaImpl criteria)
{
Type rootEntityType = criteria.GetRootEntityTypeIfAvailable();
if (rootEntityType.GetInterfaces().Contains(typeof(INHibernateProxy)))
return criteria.GetRootEntityTypeIfAvailable().BaseType.FullName;
else
return criteria.EntityOrClassName;
}
DetachedCriteria criteriaEmployees = DetachedCriteria.For<Employees>();
criteriaEmployees.SetProjection(Projections.CountDistinct("FirstName"));
ICriteria executableCriteria = criteriaEmployees.GetExecutableCriteria(Session);
int count = executableCriteria.UniqueResult<int>();
DetachedCriteria
can be used to create subqueries. Some examples are in the documentation.
精彩评论