Inconsistent results between NHibernate Query and intended results

Question

Hello all,

I have the following query in HQL :

public IEnumerable<Player> PlayersNotInTeam(Team team)
{
    return Session.CreateQuery("from Player p where p.Sex = :teamSex and p.Visible and p.Id not in (select pit.Player from PlayerInTeam as pit join pit.Roster as roster join roster.Team as team where team = :teamId)")
        .SetParameter("teamId", team.Id)
        .SetParameter("teamSex", team.Sex)
        .Enumerable<Player>();
}

When I run this query with NHibernate, it will return 2 rows.

If I run the SQL script generated by NH in my database browser (SQLite Explorer):

    select player0_.Id as Id26_, player0_.Sex as Sex26_, player0_.FirstName as FirstName26_, player0_.LastName as LastName26_, player0_.DefaultNumber as DefaultN5_26_, player0_.Visible as Visible26_, player0_.DefaultPosition_id as DefaultP7_26_ 
    from Players player0_ 
    where player0_.Sex='Male' 
        and player0_.Visible=1 
        and (player0_.Id not in  
            (select playerinte1_.Player_id 
            from "PlayerInTeam" playerinte1_ 
            inner join "Roster" roster2_ on playerinte1_.Roster_id=roster2_.Id 
            inner join Teams team3_ on roster2_.Team_id=team3_.Id, 
            Players player4_ 
            where playerinte1_.Player_id=player4_.Id 
                and team3_.Id=2));

I have 3 rows, which is what I should have.

Why are my results different?

Thanks in advance

Mike

Answer 1

I have noticed that sometimes the logged SQL is not exactly the same as the one being really used against the database. The last time I had this issue, it was a problem with trimming the Id value, e.g., where the generated SQL has something like and team3_.Id=2, the SQL being used was actually and team3_.Id='2 ' (or perhaps, player_0.Sex='Male '), which would always fail.

I would suggest you try this HQL:

string hql = @"from Player p where p.Sex = 'Male' 
                and p.Visible and p.Id not in 
                (select pit.Player from PlayerInTeam as pit join pit.Roster as roster join roster.Team as team where team = 2)";
return Session.CreateQuery(hql).Enumerable<Player>();

If that works, you need to check if your values have spare whitespaces in them.

Answer 2

I've changed my query like this:

return Session.CreateQuery("from Player p where p.Sex = :teamSex and p.Visible and not exists (from PlayerInTeam pit where pit.Player = p and pit.Roster.Team = :teamId)")
            .SetParameter("teamId", team.Id)
            .SetParameter("teamSex", team.Sex)
            .Enumerable<Player>();

And it now works. I had the idea to use "not exists" after I changed my mappings to try to use LINQ, which gave me the hint.

If you ask why I don't keep LINQ, that's because currently I hide the relationships between my entities as private fields, to force the users of the entities to use the helper functions which associate them. But the wrong thing is that in most cases, that forbids me to use LINQ in my repositories.

But I'm wondering if this wouldn't be better to "un-hide" my relationships and expose them as public properties, but keep my helper functions. This would allow me to use LINQ in my queries.

What do you do in your apps using NH?

Do you think this would be an acceptable trade-off to maintain easy mappings and queries (with the use of LINQ), but with the cost of some potential misuses of the entities if the user doesn't use the helper functions which keep the relationships?