NotSupportedException When Joining Projections

[claudiobernasconi]

Trying to join two entities and select a projection each will in some cases results in a NotSupportedException while being supported in other cases.

If the projection is defined in the from clause of the query, the query can be compiled. Example:

var query =
    from team in
        session.Query<TeamEntity>()
            .Select(team => new TeamModel
            {
                TeamId = team.Id,
                Shortname = team.Shortname
            })
    join player in session.Query<PlayerEntity>()
        on team.TeamId equals player.TeamId
    select new PlayerModel
    {
        PlayerId = player.Id,
        TeamId = team.TeamId,
        Shortname = team.Shortname,
        Name = player.Name
    };

query.ToList();

The statement above compiles into the following SQL statement (generated names replaced):

select player.Player_Id, team.Team_Id, team.Shortname, player.Name
    from DalTest_Team team, DalTest_Player player 
    where player.Team_Id=team.Team_Id

If the projection is defined in the join clause of the query, the query cannot be compiled. Example:

var query =
    from player in session.Query<PlayerEntity>()
    join team in 
        session.Query<TeamEntity>()
            .Select(team => new TeamModel
            {
                TeamId = team.Id,
                Shortname = team.Shortname
            })
        on player.TeamId equals team.TeamId
    select new PlayerModel
    {
        PlayerId = player.Id,
        TeamId = team.TeamId,
        Shortname = team.Shortname,
        Name = player.Name
    };

query.ToList();

We do not understand why there would be a difference whether the projection is part of the from or part of the join statement. Is there any technical reason for this behavior?

Is this a bug?

There is a fully functional unit test project in this GitHub repository. All required dependencies including SQLite can be restored using NuGet.

Original description

This issue was originally posted on StackOverflow with more details including a runnable test project. I did not get any community feedback within two weeks and therefore thinkt that this actually is a bug and try to report it here for clarification.

We experience an unexpected behavior of the NHibernate LINQ provider. We try to join two entities and select a projection each which sometimes results in a NotSupportedException.

Detailed description of the issue and runnable code example.

Please let me know if I should include the entire text in this GitHub issue as well or if you prefer the link the original post on StackOverflow.

Thank you for any feedback.

[fredericDelaporte]

I have not looked into the details on StackOverflow, but if you try joining two entities without using any mapped association between them, then the error seems quite self explanatory.

Your report here currently looks more as a support request than as a bug/feature request: its place then belongs to NHUser groups (or StackOverflow, but you have already tried that).

[claudiobernasconi]

@fredericDelaporte Thank you very much for your feedback. I really appreciate your help. I am aware of the fact that this use case might not be a general use case.

In my opinion I cannot decide if this is a support request or a bug. If you read through the question posted on StackOverflow you can see that it works in one way but does not the other way. This seems strange to me and looks like a bug. Maybe there is a logical explanation why this behavior is as it is?

Please take a look at the question in more detail if you want to solve our problem. I can assure you that we already put in a lot of effort and we are pretty familiar with nHiberate. We use it for more than 5 years in production.

[fredericDelaporte]

So some forms of join without using an association may work with Linq-2-NH, I was not knowing it. I have always considered this was to be done with QueryOver instead.

Determining if your case is a bug or a feature request would require to check what is going on in the provider code in both cases. (I personally do not intend to do it any time soon. Other contributors may do it.)

[hazzik]

Linq expressions are translated to HQL. HQL supports only joins by navigation properties and does not support SQL joins. HQL also supports select from multiple tables (cross join). So, inner join on unrelated tables can be emulated by cross join + where clause.

Linq translator does some simple basic analysis to determine if the expression can be substituted by cross join + where clause, navigation join, etc.

We would not be able to support all join types without extending the HQL grammar.

So, each expression class should be considered in isolation. It's always not a bug, as NotSupportedException says what it is - this query is not supported. So, for every case it's either "could be done" - it's an improvement. Or "can not be done" - won't fix.

[claudiobernasconi]

@hazzik First of all thank you for giving me the detailed internals on how this works under the hood. The first query posted in the original question results in a cross join. According to your explanation, this makes sense to me.

The question remains why the second query does not translate into a cross join as well? If you take a look at the queries, you will recognize that there is only a difference in the left and the right side of the cross join.

In my opinion, the second query should translate in a cross join as well if the first query successfully did. It would look something like this (order of the statements are reversed):

select player.Player_Id, team.Team_Id, team.Shortname, player.Name
    from DalTest_Player player, DalTest_Team team 
    where team.Team_Id = player.Team_Id

In other words: If it is not an intended behavior of the Linq provider I do not understand how it works in the first query but does not in the second?

However, I accept your answer that our intention is not supported and maybe never will be supported in the future.

[hazzik]

The question remains why the second query does not translate into a cross join as well?

Analyzer is not intelligent enough to recognize this. You are more than welcome to improve it.

[hazzik]

@bahusoid does HQL support this now?

[bahusoid]

What hql do you have in mind? It looks like join on subquery to me:

 join (select  te.Id, te.ShortName TeamEntity te) team on team.Id = player.TeamId      

So I would say no. But maca88 is already working on it