I cannot figure out what I'm doing wrong with this basic NHibernate query...

Question

Okay, I have a class, Company

public class Company
{
    public virtual int Id { get; set; }
    public virtual IList<Role> Roles { get; set; }
}

And another class, Role

public class Role
{
    public virtual int Id { get; set; }
    public virtual Company Company { get; set; }
    public virtual RoleLevel RoleLevel { get; set; }
}

I'm using the Fluent automappings, nothing special going on.

I have this method:

private RoleLevel GetRole(int companyId)
{
    var allRoles = Session.CreateCriteria<Role>().List<Role>();

    var role = Session.CreateCriteria<Role>()
        .CreateAlias(NameOf<Role>.Property(r => r.Company), "c")
        .Add(Restrictions.Eq("c.Id", companyId))
        .UniqueResult<Role>();

    return (role == null) ? RoleLevel.Restricted : role.RoleLevel;
}

companyId is being passed in as 102. If I check the allRoles array, one of them relates to Company #102. But the query that is supposed to return a single Role returns null.

What am I doing wrong?

EDIT 1

By request, here is the query being executed, according to NHProf:

SELECT this_.Id         as Id75_1_,
       this_.Version    as Version75_1_,
       this_.RoleLevel  as RoleLevel75_1_,
       this_.DbDate     as DbDate75_1_,
       this_.Account_id as Account5_75_1_,
       this_.Company_id as Company6_75_1_,
       c1_.Id           as Id71_0_,
       c1_.Version      as Version71_0_,
       c1_.Name         as Name71_0_,
       c1_.OnyxAlias    as OnyxAlias71_0_,
       c1_.DbDate       as DbDate71_0_,
       c1_.Parent_Id    as Parent6_71_0_
FROM   "Role" this_
       inner join "Company" c1_
         on this_.Company_id = c1_.Id
WHERE  c1_.Id = 102 /* @p0 */

EDIT 2

I tried changing the database to SQL Server. In SQL Server, it all works. I guess this is a bug with how NHibernate connects to SQLite databases? For now, I can use a SQL Server DB for testing, but I'd like to go for an In Memory SQLite DB in the future, for speed reasons.

Answer 1

First of all have you run the query against the database?

Second, if something is wrong with the mapping, could you try another CreateCriteria instead? You can then speficfy the jointype like below. Just to make sure we have tried all the basics before we move further :)

private RoleLevel GetRole(int companyId)
{
    var allRoles = Session.CreateCriteria<Role>().List<Role>();

    var role = Session.CreateCriteria<Role>("r")
        .CreateCriteria("Company", "c", JoinType.LeftOuterJoin)
        .Add(Restrictions.Eq("c.Id", companyId))
        .UniqueResult<Role>();

    return (role == null) ? RoleLevel.Restricted : role.RoleLevel;
}

Third, potential problem could be that you have double quotes around your table names. That indicates a mapping problem. Casing of table name mismatch or something similar.