请您能帮我将以下SQL查询转换为LINQ吗？
主要是我遇到问题的连接。
结果将是被执行的LINQ to SQL代码。
非常感谢
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 = ...)
这是我目前使用的LINQ，但是它生成的结果与SQL查询的结果不同。所以我在联接和正确的外部联接上出错了。
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 = [email protected](),
PropertyExpenseScheduleUri = pes.uri,
UnitRef = [email protected](),
UnitName = "(" + [email protected]() + ")" + 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);
您提供的最新代码将生成以下SQL，并返回0行。
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)))
最佳答案
LINQ不支持右外部联接，因此应通过交换左右部分并执行左外部联接（这不是很自然的方法，但至少是已知的join clause (C# Reference)模式）来模拟它。
话虽如此，我认为等效的LINQ查询应该是这样的：
from unitapp in context.unit_apportionment
join unitappdate in context.unit_apportionment_date on unitapp.uri equals unitappdate.unit_apportionment_uri
join pes in context.property_expense_schedule on unitapp.property_expense_schedule_uri equals pes.uri
join unitmestot in context.unit_measurement_total on unitapp.property_ref equals unitmestot.property_ref
into unitapp_unitmesstot from unitmestot in unitapp_unitmesstot.DefaultIfEmpty() // Left Outer Join
join units in context.units on unitmestot.uri equals units.prime_measurement_uri
select new Apportionment()
{
// ...
}
编辑：看起来EF在左外部联接之后立即为内部联接生成其他条件，这导致与原始SQL查询的差异。在这种情况下，您可以尝试将正确的部分分组到一个子查询中，以尝试更改连接顺序：
from unitapp in context.unit_apportionment
join unitappdate in context.unit_apportionment_date on unitapp.uri equals unitappdate.unit_apportionment_uri
join pes in context.property_expense_schedule on unitapp.property_expense_schedule_uri equals pes.uri
join right in (
from unitmestot in context.unit_measurement_total
join units in context.units on unitmestot.uri equals units.prime_measurement_uri
select new { unitmestot, units }
) on unitapp.property_ref equals right.unitmestot.property_ref
into outerJoin from right in outerJoin.DefaultIfEmpty() // Left Outer Join
let unitmestot = right.unitmestot
let units = right.units
select new Apportionment()
{
// ...
}
关于c# - Linq to SQL协助与联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37075466/