Uploaded image for project: 'NHibernate [Moved to GitHub]'
  1. NH-2451

LINQ Issue - joining twice with same table when used in both select and where. Second join is a full select and filtered in where instead of a join .. on

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 3.0.0.GA
    • Fix Version/s: 3.3.0.CR1
    • Component/s: Linq Provider
    • Labels:
      None
    • Sprint:

      Description

      Hi

      I have an issue with the new Linq Provider, it seems to be joining incorrectly as soon as you make a Reference comparison in a where clause.

      I have the following query i would like to execute.

      var list = session.Query<Reservation>()
      .Where(x => x.SelectedRoom.MaxTakeaway == 0)
      .Select(x => ID = x.SelectedRoom.ID).ToList();

      This however results in this SQL being executed:

      select Rooms1_.ID as col_0_0_
      from reservation_db reservatio0_
      left outer join Rooms table1_
      on reservatio0_.SelectedRoom=Rooms1_.ID,
      Rooms Rooms2_
      where reservatio0_.SelectedRoom= Rooms2_.ID
      and Rooms2_.ShowInList = '1'

      As you can see NHibernate choses to join on Rooms to get the correct Room for the SELECT, however it then chooses to add the entire table again (,Rooms Rooms2_) and then in the where clause it filters the results, effectively changing this to a INNER JOIN AND joining on the same table twice also seems like a waste.

      The objects are very simply
      public class Reservation {
      public int ID

      { get;set; }
      public Room SelectedRoom { get;set; }

      }
      public class Room {
      public int ID

      { get;set; }
      public bool ShowInList { get;set; }

      }

        Attachments

          Issue links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                mschmidt Morten Schmidt
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Who's Looking?