locked
Left join should be inner join RRS feed

  • Question

  • I have the following linq-query built dynamically:

    (from x in ((ObjectQuery<PersonView>)PersonView).MergeAs(0)
    where x.Info && (((False || (True && (x.Person.FirstName == compareValue))) || (True && (x.Person.FirstName == compareValue))) || (True &&     ((x.Person.FirstName == "") || (x.Person.FirstName == null))))
    where (x.DateTime >= startDate.Date) && (x.DateTime <= endDate.Date)
    select x.PersonID).Distinct()

    which results in the following sql query:

    SELECT 
    [Distinct1].[PersonID] AS [PersonID]
    FROM ( SELECT DISTINCT 
        [Extent1].[PersonID] AS [PersonID]
        FROM  (SELECT 
               [PersonView].[PersonID] AS [PersonID], 
               [PersonView].[DateTime] AS [DateTime], 
               [PersonView].[Info]     AS     [Info]
               FROM [core].[PersonView] AS     [PersonView]) AS [Extent1]
               LEFT OUTER JOIN [core].[Persons] AS [Extent2] ON [Extent1].[PersonID] = [Extent2].[PersonID]
               WHERE ([Extent1].[Info] = 1) AND ([Extent2].[FirstName] IN (@p__linq__0,@p__linq__1,N'') OR [Extent2].[FirstName] IS NULL) AND ([Extent1].[DateTime] >= @p__linq__2) AND ([Extent1].[DateTime] <= @p__linq__3)
    )  AS [Distinct1]

    The left outer join is the one that causes the problem and makes for really bad performance. I went searching and to my surprise read that navigation properties always translate to an inner join. And that is not the case here. Only requirement is that the propery is not nullable. Here the foreign key is indeed not nullable. Can anyone help me to solve this?

    I work database first and the following entity is generated:

    public partial class PersonView: DbEntity   

    {        ...       

    private int _personID;       

    public int PersonID { get{ return _personID;} set{ _personID = value; OnPropertyChanged("PersonID");} }               

    public virtual Person Person { get; set; }

    ...   

    }

    The partial is because I tried to add metadata with the required attribute. Didn't work


    Monday, June 22, 2015 1:12 PM

All replies

  • >The left outer join is the one that causes the problem and makes for really bad performance

    Why do you believe that.

    >navigation properties always translate to an inner join

    Nope.  Here you have a bunch of ORs so you don't want the PersonView row to be eliminated if the join predicate does not obtain.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, June 22, 2015 1:42 PM
  • I pasted the sql-query in microsoft management studio and by replacing the left join by an inner one it went from 270 seconds to 11 seconds. 270 seconds is really not acceptable and I have no idea how to adjust the linq query. The where clause is created dynamically according to parameters the user inputs. Here is piece of the code:

    case FilterComparer.IsEqualTo:
                   if (compareValue == "")
                      return x => x == "" || x == null;
                   return x => x == compareValue;

    This was one of the links that made me believe it should translate to an inner join : http://stackoverflow.com/questions/23284454/conventions-for-joins-in-entity-framework-6-1-0-code-first (Apparently I cannot add a link to my post, sorry for that)

    Tuesday, June 23, 2015 5:54 AM
  • Hello LittleWhiteFairy,

    >>This was one of the links that made me believe it should translate to an inner join :

    It actually would depend on your foreign key rather than you written linq query, please check if you have set our foreign key column to be nullable, if so, for what you want to generate an inner join, to set to not nullable so that it would generate the 1 to * relationship and Entity Framework would consider the association is required. I made a small test with a sql server database and it worked.

    If this does not work for you, please share information as your used database and your tables with us.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 23, 2015 8:06 AM
  • The 'foreign key' is already not nullable. I checked in the database PersonId on PersonView is not null and also in the model nullable is set to false so I have a 1 to * relationship. Is the problem that I start from a view and it is not connected in the database? In the edmx an association is present.

    I was able to get better performance by adding .Where(x => x.Person != null) to the linq-query which resulted in a check on ResourceID != null. I have no idea why it does improve performance because it can't be null. I use SQL Server 2008R2. I don't know how much more I can tell about my tables. PersonView is a view that has a foreign key PersonID that is an int, not null.

    Anyway, the problem is solved except I have no idea why.


    Tuesday, June 23, 2015 9:19 AM
  • "I pasted the sql-query in microsoft management studio and by replacing the left join by an inner one it went from 270 seconds to 11 seconds."

    Those queries are different.  SQL Server doesn't know if every row in PersonView has a corresponding row in Person.  If it did, then a LEFT JOIN and an INNER JOIN would be equivalent.

    "was able to get better performance by adding .Where(x => x.Person != null)"

    Putting a query predicate on the outer table of a LEFT OUTER JOIN will eliminate any rows in the result where there is no matching row in the outer table of the JOIN, effectively turning the LEFT OUTER JOIN into an INNER JOIN.

    EG


    select *
    from A
    left join B
      on a.id = b.aid
    where b.col is not null

    is equivalent to

    select *
    from A
    inner join B
      on a.id = b.aid

    Your LINQ predicate ".Where(x => x.Person != null)" will eliminate x's who have no related Person, so will either generate a INNER JOIN query or a LEFT JOIN with a "is not null" check on the outer table, which amounts to the same thing.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Tuesday, June 23, 2015 1:33 PM