title: Linq to SQL assistance with joins
body: <p>Please can you help me translate the following SQL query into LINQ
  Its mainly the joins that i am having issues with.
  The result will be LINQ to SQL code which gets executed.</p>
  
  <p>many thanks</p>
  
  <pre><code>SELECT * FROM   unit INNER JOIN   
  
   unit_measurement_total ON unit.prime_measurement_uri = unit_measurement_total.uri RIGHT OUTER JOIN
     property_expense_schedule 
          INNER JOIN
       unit_apportionment ON property_expense_schedule.uri = unit_apportionment.property_expense_schedule_uri 
          INNER JOIN
        unit_apportionment_date ON unit_apportionment.uri = unit_apportionment_date.unit_apportionment_uri ON 
       unit_measurement_total.property_ref = unit_apportionment.property_ref
          WHERE (property_expense_schedule.property_ref = ...) 
  </code></pre>
  
  <p>This is the LINQ I have at the moment, but its not generating the same results as the SQL query. So i am going wrong with the joins and the right outer joins.</p>
  
  <pre><code> var query = (from units in context.units
                   join unitmestot in context.unit_measurement_total on units.prime_measurement_uri equals unitmestot.uri
                   from pes in context.property_expense_schedule
                   join unitapp in context.unit_apportionment on new { A = pes.uri, B = unitmestot.property_ref, C = unitmestot.unit_ref } equals new { A = unitapp.property_expense_schedule_uri, B = unitapp.property_ref, C = unitapp.unit_ref}
                   join unitappdate in context.unit_apportionment_date on unitapp.uri equals unitappdate.unit_apportionment_uri
                   select new Apportionment()
                   {
                       PropertyRef = units.property_ref.ToString(),
                       ScheduleName = pes.name,
                       ScheduleRef = pes.@ref.ToString(),
                       PropertyExpenseScheduleUri = pes.uri,
                       UnitRef = units.@ref.ToString(),
                       UnitName = "(" + units.@ref.ToString() + ")" + units.name,
                       ObseleteUnit = units.obsolete_unit,
                       ApportionmentPercentage = unitappdate.apportionment_percentage,
                       ToDate = unitappdate.to_date,
                       MeasurementBasis = unitmestot.measurement_basis,
                       MeasuredIn = unitmestot.measured_in,
                       MeasurementImperialTotal = unitmestot.measurement_imperial_total,
                       MeasurementMetricTotal = unitmestot.measurement_metric_total
  
                   }).Where(filter);
  </code></pre>
  
  <p>the latest code you supplied generates the following SQL and returns 0 rows.</p>
  
  <pre><code>  SELECT     [Extent1].[uri] AS [uri],     CASE WHEN ([Join3].[property_ref1] IS NULL) THEN N'' ELSE  CAST( [Join3].[property_ref1] AS nvarchar(max)) END AS [C1],     [Extent3].[name] AS [name],      CAST( [Extent3].[ref] AS nvarchar(max)) AS [C2],     [Extent3].[uri] AS [uri1],     CASE WHEN ([Join3].[ref] IS NULL) THEN N'' ELSE  CAST( [Join3].[ref] AS nvarchar(max)) END AS [C3],     N'(' + CASE WHEN (CASE WHEN ([Join3].[ref] IS NULL) THEN N'' ELSE  CAST( [Join3].[ref] AS nvarchar(max)) END IS NULL) THEN N'' WHEN ([Join3].[ref] IS NULL) THEN N'' ELSE  CAST( [Join3].[ref] AS nvarchar(max)) END + N')' + CASE WHEN ([Join3].[name] IS NULL) THEN N'' ELSE [Join3].[name] END AS [C4],     [Join3].[obsolete_unit] AS [obsolete_unit],     [Extent2].[apportionment_percentage] AS [apportionment_percentage],     [Extent2].[to_date] AS [to_date],     CASE WHEN ([Join3].[measurement_basis] IS NULL) THEN N'' ELSE [Join3].[measurement_basis] END AS [C5],     CASE WHEN ([Join3].[measured_in] IS NULL) THEN N'' ELSE [Join3].[measured_in] END AS [C6],     [Join3].[measurement_imperial_total] AS [measurement_imperial_total],     [Join3].[measurement_metric_total] AS [measurement_metric_total]    FROM    [tramps].[unit_apportionment] AS [Extent1]    INNER JOIN [tramps].[unit_apportionment_date] AS [Extent2] ON [Extent1].[uri] = [Extent2].[unit_apportionment_uri]    INNER JOIN [tramps].[property_expense_schedule] AS [Extent3] ON [Extent1].[property_expense_schedule_uri] = [Extent3].[uri]    LEFT OUTER JOIN  (SELECT [Extent4].[property_ref] AS [property_ref2], [Extent4].[measurement_basis] AS [measurement_basis], [Extent4].[measured_in] AS [measured_in], [Extent4].[measurement_imperial_total] AS [measurement_imperial_total], [Extent4].[measurement_metric_total] AS [measurement_metric_total], [Extent5].[property_ref] AS [property_ref1], [Extent5].[ref] AS [ref], [Extent5].[name] AS [name], [Extent5].[obsolete_unit] AS [obsolete_unit]        FROM  [tramps].[unit_measurement_total] AS [Extent4]        INNER JOIN [tramps].[unit] AS [Extent5] ON [Extent4].[uri] = [Extent5].[prime_measurement_uri] ) AS [Join3] ON [Extent1].[property_ref] = [Join3].[property_ref2]    WHERE (N'101329' = (CASE WHEN ([Join3].[property_ref1] IS NULL) THEN N'' ELSE  CAST( [Join3].[property_ref1] AS nvarchar(max)) END)) AND ( NOT (('Y' = [Join3].[obsolete_unit]) AND ([Join3].[obsolete_unit] IS NOT NULL)))
  </code></pre>