none
Linq to SQL Joins Change Unexepectdly

    Question

  • I am writing a WCF application with LINQ to SQL Code First. For one of my entities I have the following joins defined in the entity type configuration: 

                this.HasRequired(t => t.Station)
                    .WithMany()
                    .HasForeignKey(t => t.StationNumber);

    I have to do a query involving an outer join for this entity shown below:

     var result = from lease in query
                               join building in ctx.Buildings        
                                on new{lease.StationNumber,lease.BuildingNumber} equals new { building.StationNumber, building.BuildingNumber}
                                into gj
                                from subset in gj.DefaultIfEmpty()
                               select new {Lease=lease                          
                                   , Building=subset                            //to include all of the relevant pieces.
                                   , Station = lease.Station
                                   , GSAUsage = lease.LookupGSAUsage
                                   , Program = lease.Station.Program };

    When I unit test that component of the application and inspect the SQL generated by the above statement I get the correct inner join between lease and station. However, when I debug the application, I get a different sql statement generated:

    {SELECT 
    .......
    FROM     [dbo].[Station_Lease_vw] AS [Extent1]
    LEFT OUTER JOIN [dbo].[Building] AS [Extent2] 
    ON ([Extent1].[BuildingNumber] = [Extent2].[BuildingNumber]) AND ([Extent1].[StationNumber] = [Extent2].[StationNumber])
    LEFT OUTER JOIN [dbo].[Station] AS [Extent3] 
    ON [Extent1].[StationNumber] = [Extent3].[StationNumber]
    LEFT OUTER JOIN [dbo].[Lookup_GSAUsage] AS [Extent4] 
    ON ([Extent1].[GSAUsageCode] = [Extent4].[UsageCode]) 
    AND ([Extent1].[UsageType] = [Extent4].[UsageType])
    LEFT OUTER JOIN [dbo].[Program] AS [Extent5] 
    ON [Extent3].[ProgramID] = [Extent5].[ProgramID]
    WHERE [Extent1].[ACC] = @p__linq__0}

    All of the inner joins are set to Outer joins. Does anyone know what could cause this different SQL statement to be generated?

    Thursday, July 18, 2013 7:08 PM

Answers

  • According to your issue,I found the problem on DefaultIfEmpty method.here are some examples about it.

    For our test we will use the following demo database/conceptual model:

    Table :

    Master : Id,MasterField1,MasterField2

    Detail:Id,DetailField1,DetaField2,MasterID

    DetailOfDeatil:Id,DetailOfDetailField1,DetailOfDetailField2,DetailID

    Master records may have 1 or more Detail records and Detail records may have one or more DetailOfDetail records. Left outer joins are translated as follows:

     

    “Bring me all records/entities from the Master table along with their corresponding Detail records. The Master records should always exist in the result set even if there are no related records/entities in the Detail table”.

     

    The first approach is using the navigation property Details in the query as follows:

     

    from p in ctx.Masters.Include("Details") select p

     

    This approach is the simplest possible. It only applies when you have the “Details” Navigation Property on your model (enabling the property just to be able to perform left outer join queries is not an option) and it also suffers from the fact that the data are not flattened  (which you may need since your data are feeding a report for example). You get a list of Master objects each one with a list of 0 or more Detail objects.

    But you have a way of flattening your results. Using the following LINQ will flatten your results but unfortunately will perform an inner join:

     

    var Result2 = from p in ctx.Masters
                  from q in p.Details
                  select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
                      d1=q.DetailField1,d2=q.DetailField2};

     

    To do a left outer join you just need to do the following:

     

    var Result2 = from p in ctx.Masters
                  from q in p.Details.DefaultIfEmpty()
                  select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
                      d1=q.DetailField1,d2=q.DetailField2};

     

    That is, the only difference is the addition of the .DefaultIfEmpty() method (this one was missing in previous versions of EF). Now you have a flattened result set for your needs.

     

    The second approach does not require the navigation property:

    If you do not have the “Details” navigation property then you can still get a left outer join. Using the following will give you an inner join:

     

    var Result3 = from p in ctx.Masters join q in ctx.Details on p.Id equals q.MasterID
                  select new {Id = p.Id,m1 = p.MasterField1,m2 = p.MasterField2,
                      d1 = q.DetailField1,d2 = q.DetailField2};

     

    Changing this to the followinq:

     

    var Result3 = from p in ctx.Masters 
                  join q in ctx.Details on p.Id equals q.MasterID into Details
                  select new {Id = p.Id,d=Details};

     

    Will give you the same result as if you had the navigation property. All you have to do for the left outer join is to flatten it as before:

     

    var Result3 = from p in ctx.Masters 
                  join q in ctx.Details on p.Id equals q.MasterID into Details
                  from m in Details.DefaultIfEmpty()
                  select new {Id = p.Id,m1 = p.MasterField1,m2 = p.MasterField2,
                      d1 = m.DetailField1,d2 = m.DetailField2};

     

    Now let’s extend it a little further and require to get results from the third table as well. We will distinguish again between the two previous approaches (obviously we would like to avoid a nasty foreach loop at the end where the data are collected with SELECT statements creating a wonderful SELECT N+1 issue).

     

    Using the Navigation Property Approach

    To include the data from the third table in your result you just follow the same principle by adding the third table:

     

    var Result2 = from p in ctx.Masters
                  from q in p.Details.DefaultIfEmpty()
                  from l in q.Details.DefaultIfEmpty()
                  select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
                      d1=q.DetailField1,d2=q.DetailField2,dd1=l.DetailOfDetailField1,dd2=l.DeailOfDetailField2};

     

    Without the Navigation Property

    The same applies here by following the same approach as before:

     

    var Result3 = from p in ctx.Masters 
                                  join m in ctx.Details on p.Id equals m.MasterID into Details
                                  from q in Details.DefaultIfEmpty() join o in ctx.DetailOfDetails 
                                       on q.Id equals o.DetailID into DetailsOfDetail
                                  from l in DetailsOfDetail.DefaultIfEmpty()
                                  select new
                                  {
                                      Id = p.Id,
                                      m1 = p.MasterField1,
                                      m2 = p.MasterField2,
                                      d1 = q.DetailField1,
                                      d2 = q.DetailField2,
                                      dd1 = l.DetailOfDetailField1,
                                      dd2 = l.DeailOfDetailField2
                                  };

     

    I am glad to help you.

    Thursday, July 25, 2013 3:14 AM

All replies

  • Hi,

    I have the same question,can anyone help us!

    buernuo


    ALL FOR ONE AND ONE FOR ALL.


    • Edited by Ada Semon Wednesday, July 24, 2013 9:13 AM Edit content
    Wednesday, July 24, 2013 9:11 AM
  • According to your issue,I found the problem on DefaultIfEmpty method.here are some examples about it.

    For our test we will use the following demo database/conceptual model:

    Table :

    Master : Id,MasterField1,MasterField2

    Detail:Id,DetailField1,DetaField2,MasterID

    DetailOfDeatil:Id,DetailOfDetailField1,DetailOfDetailField2,DetailID

    Master records may have 1 or more Detail records and Detail records may have one or more DetailOfDetail records. Left outer joins are translated as follows:

     

    “Bring me all records/entities from the Master table along with their corresponding Detail records. The Master records should always exist in the result set even if there are no related records/entities in the Detail table”.

     

    The first approach is using the navigation property Details in the query as follows:

     

    from p in ctx.Masters.Include("Details") select p

     

    This approach is the simplest possible. It only applies when you have the “Details” Navigation Property on your model (enabling the property just to be able to perform left outer join queries is not an option) and it also suffers from the fact that the data are not flattened  (which you may need since your data are feeding a report for example). You get a list of Master objects each one with a list of 0 or more Detail objects.

    But you have a way of flattening your results. Using the following LINQ will flatten your results but unfortunately will perform an inner join:

     

    var Result2 = from p in ctx.Masters
                  from q in p.Details
                  select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
                      d1=q.DetailField1,d2=q.DetailField2};

     

    To do a left outer join you just need to do the following:

     

    var Result2 = from p in ctx.Masters
                  from q in p.Details.DefaultIfEmpty()
                  select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
                      d1=q.DetailField1,d2=q.DetailField2};

     

    That is, the only difference is the addition of the .DefaultIfEmpty() method (this one was missing in previous versions of EF). Now you have a flattened result set for your needs.

     

    The second approach does not require the navigation property:

    If you do not have the “Details” navigation property then you can still get a left outer join. Using the following will give you an inner join:

     

    var Result3 = from p in ctx.Masters join q in ctx.Details on p.Id equals q.MasterID
                  select new {Id = p.Id,m1 = p.MasterField1,m2 = p.MasterField2,
                      d1 = q.DetailField1,d2 = q.DetailField2};

     

    Changing this to the followinq:

     

    var Result3 = from p in ctx.Masters 
                  join q in ctx.Details on p.Id equals q.MasterID into Details
                  select new {Id = p.Id,d=Details};

     

    Will give you the same result as if you had the navigation property. All you have to do for the left outer join is to flatten it as before:

     

    var Result3 = from p in ctx.Masters 
                  join q in ctx.Details on p.Id equals q.MasterID into Details
                  from m in Details.DefaultIfEmpty()
                  select new {Id = p.Id,m1 = p.MasterField1,m2 = p.MasterField2,
                      d1 = m.DetailField1,d2 = m.DetailField2};

     

    Now let’s extend it a little further and require to get results from the third table as well. We will distinguish again between the two previous approaches (obviously we would like to avoid a nasty foreach loop at the end where the data are collected with SELECT statements creating a wonderful SELECT N+1 issue).

     

    Using the Navigation Property Approach

    To include the data from the third table in your result you just follow the same principle by adding the third table:

     

    var Result2 = from p in ctx.Masters
                  from q in p.Details.DefaultIfEmpty()
                  from l in q.Details.DefaultIfEmpty()
                  select new {Id = p.Id, m1 = p.MasterField1,m2 = p.MasterField2,
                      d1=q.DetailField1,d2=q.DetailField2,dd1=l.DetailOfDetailField1,dd2=l.DeailOfDetailField2};

     

    Without the Navigation Property

    The same applies here by following the same approach as before:

     

    var Result3 = from p in ctx.Masters 
                                  join m in ctx.Details on p.Id equals m.MasterID into Details
                                  from q in Details.DefaultIfEmpty() join o in ctx.DetailOfDetails 
                                       on q.Id equals o.DetailID into DetailsOfDetail
                                  from l in DetailsOfDetail.DefaultIfEmpty()
                                  select new
                                  {
                                      Id = p.Id,
                                      m1 = p.MasterField1,
                                      m2 = p.MasterField2,
                                      d1 = q.DetailField1,
                                      d2 = q.DetailField2,
                                      dd1 = l.DetailOfDetailField1,
                                      dd2 = l.DeailOfDetailField2
                                  };

     

    I am glad to help you.

    Thursday, July 25, 2013 3:14 AM