nHibernate named query returns not all results

Question

I have a few simple named queries with only joins, ant couple subselects. All of them ar working perfectly except one. The problem is, that when i run SQL code in Management Studio, i get 177 results, and when i run named query with the same SQL code, i get 20 results. I can't figure out why is that. I Call named query the as all other:

public IList<InstitutionIndexDTO> GetInstitutionIndexByWorkTimeSearch(int time, int institutionType)
{
    IQuery query = GetCurrentSession()
        .GetNamedQuery("GetInstitutionsListByTime")
        .SetInt32("Type", institutionType)
        .SetInt32("TimeUntilClose", time)
        .SetResultTransformer(Transformers.AliasToBeanConstructor(typeof(InstitutionIndexDTO).GetConstructors()[0]));

        return query.List<InstitutionIndexDTO>();
}

Even when i harcoded parameters in SQL, i still got the same result. I tried checking with Profiler, but generated SQL is perfect and in management studio returns all 177 results.
InstitutionIndexDTO is working correctly, because i use it with other named queries.

I have a working named query, and the one causing problems was made from that one, adding additional INNER JOIN and changing WHERE clause. Both queries returns same columns.
Maybe somebody has an idea, what i could have done wrong?

Can you post the query itself please – Nexus Jan 23 '12 at 16:08 — Nexus, Jan 23 '12 at 16:08

score 0 · Answer 1 · answered Jan 24 '12 at 11:48

I tested my code and realised that this line is causing the problem:
(CASE WHEN (DATEPART(dw, GETDATE())) = 1 THEN 7 ELSE (DATEPART(dw, GETDATE()) - 1) END)

If i understand well, the problem is in DATEPART function. Has anybody encountered this problem?

nHibernate named query returns not all results

1 Answers1