将 SQL 转换为 Linq 的多个表的组联接

Question

谁能帮我把这个 SQL 语句翻译成 Linq 吗？

SELECT a.KPI, a.ProcessDate, a.Actual, lf.LPLocal, pf.LPProd, d.LocalDelta, d.ProdDelta
FROM
    Actual AS a
    LEFT OUTER JOIN Local l ON l.Date = a.Date
    LEFT OUTER JOIN Prod  p ON p.Date = a.Date
    LEFT OUTER JOIN Delta d ON d.Date = a.Date

我做了一些研究并提出了这个：

Dim Xq1 = (From a In tmpActual _
Group Join l In tmpLocal On l.Item("Dates") Equals a.Item("Dates") Into al = Group _
From x In al.DefaultIfEmpty() _
Group Join p In tmpProd On p.Item("Dates") Equals a.Item("Dates") Into ap = Group _
        From y In ap.DefaultIfEmpty() _
Group Join d In tmpDelta On d.Item("Dates") Equals a.Item("Dates") Into ad = Group _
From z In ad.DefaultIfEmpty() _
Select
    KPI = a.Item("KPI"), ProcessDate = a.Item("Dates"),
    AccountingActual = a.Item("AccountingActual"),
    LocalForecast = x.Item("LocalForecast"),
    ProdForecast = y.Item("ProdForecast"),
    Localdelta = z.Item("LocalDelta"),
    ProdDelta = z.Item("ProdDelta")).DefaultIfEmpty

但是当我运行它时，它得到：

System.InvalidCastException 被捕获 Message="无法转换类型的对象 'd__9e1[VB$AnonymousType_67[System.Object,System.Object,System.Object,System.Object,System.Object,System.Object,System.Object]]' 键入“System.Data.DataTable”。”
来源=“LPBenchmarking”堆栈跟踪： 在 LPBenchmarking.BLL.GetKPIFromPrime(PreKPIData ds、Int32 异常值）位于 C:\Documents 中 和 Settings\RSpalding.TCI\My 文档\库\文档\视觉 Studio 2008\项目\LPB Optimization\LPBenchmarking_Web\BusinessComponents\BLL.vb:line 204 内部异常：

任何帮助将不胜感激。谢谢。

Answer 1

尝试将查询拆分为多个子查询，然后查看哪些子查询有效（如果有）：

Dim alq = From a In tmpActual _
 Group Join l In tmpLocal On l.Item("Dates") Equals a.Item("Dates") Into al = Group

Dim apq = _
 From x In alq.DefaultIfEmpty() _
 Group Join p In tmpProd On p.Item("Dates") Equals a.Item("Dates") Into ap = Group

Dim adq = _
          From y In apq.DefaultIfEmpty() _
 Group Join d In tmpDelta On d.Item("Dates") Equals a.Item("Dates") Into ad = Group

Dim Xq = _
 From z In adq.DefaultIfEmpty() _
 Select
     KPI = a.Item("KPI"),
     ProcessDate = a.Item("Dates"),
     AccountingActual = a.Item("AccountingActual"),
     LocalForecast = x.Item("LocalForecast"),
     ProdForecast = y.Item("ProdForecast"),
     Localdelta = z.Item("LocalDelta"),
     ProdDelta = z.Item("ProdDelta")

Dim Xq1 = Xq.DefaultIfEmpty

注意，我只编辑了您在 SO 中提供的代码 - 我没有在 IDE 中对其进行语法检查！ >