Linq-to-Entities:带有WHERE子句的LEFT OUTER JOIN，计算和投影 [英] Linq-to-Entities: LEFT OUTER JOIN with WHERE clause, calculation and projection

问题描述

我很难弄清楚如何将多个列上的简单SQL LEFT OUTER JOIN和where子句转换为可运行的Linq-to-Entities查询.只有两个表.无论Table2中的匹配项如何，我都需要Table1中所有行的值，但是事实证明联接多个列很困难.我还需要在查询中做一个简单的计算，但也找不到. SQL中的查询如下所示:

I'm having a very difficult time figuring out how to translate a simple SQL LEFT OUTER JOIN on multiple columns and a where clause into a working Linq-to-Entities query. There are only two tables. I need values for all rows from Table1, regardless of matches in Table2, but joining multiple columns is proving difficult. I also need to do a simple calculation in the query, but can't find that either. The query in SQL would look like this:

select t1.tableid1,
t1.tableid2,
t1.fieldvalue1,
t2.fieldvalue2,
isnull(t2.fieldvalue2,0) / t1.fieldvalue1 as calcvalue
t1.fieldvalue1 - isnull(t2.fieldvalue2,0) as calcvalue2
from table1 t1
left outer join table2 t2 
    on t1.tableid1 = t2.tableid1
    and t1.tableid2 = t2.tableid2
    and t1.tableid3 = t2.tableid3
where t1.tableid1 = @somevalue

我可以单列左联接，但是我似乎找不到多列的正确语法，更不用说不知道如何在列上增加或减去值了.以下是我的最佳猜测，但我遇到以下情况的类型预期错误("new"之后和第一个左括号之前)

I can do a single column left join, but I can't seem to find the proper syntax for multiple columns, not to mention not knowing how to add or subtract the values on the column. Below is my best guess but I'm getting a Type Expected error (after "new" and before the first opening parenthesis) for:

On New (t2.Field(Of String)("tableid1"), t2.Field(Of String)

最佳猜想:

Dim query = From t1 In dtTable1 _
                         Group Join t2 In dtTable2 _
                         On New (t2.Field(Of String)("tableid1"), t2.Field(Of String)("tableid2")) Equals _
                            (t1.Field(Of String)("tableid1"), t1.Field(Of String)("tableid2")) _
                         Into t2outer = Group _
                         From t2 In t2outer.DefaultIfEmpty() _
                         Select New With _
                        { _
                            .t1_tableid1 = t1.Field(Of String)("tableid1"), _
                            .t1_tableid2 = t1.Field(Of String)("tableid2"), _
                            .t1_fieldvalue1 = t1.Field(Of Integer)("fieldvalue1"))
                            .t2_fieldvalue2 = If(t2 Is Nothing, CType("0", Integer), t2.Field(Of Integer)("fieldvalue2"))
                        }

@Gert那让我足够亲近了.我认为格式略有偏离，因为我必须添加花括号而不是括号，并且在"Key"关键字和id之间放置了一个空格:

@Gert That got me close enough. I think the formatting was slightly off, as I had to add braces rather than parenthesis and it placed a space between the "Key" keyword and the id:

On New With {
                        Key .id1 = t2.Field(Of String)("tableid1"), _
                        Key .id2 = t2.Field(Of String)("tableid2")
                        } _
                    Equals _
                    New With {
                        Key .id1 = t1.Field(Of String)("tableid1"), _
                        Key .id2 = t1.Field(Of String)("tableid2")
                        } _

多亏了Gert，我得以按照SQL查询中的说明进行工作.请参阅以下有关SQL查询的完整工作表示形式的内容:

Thanks to Gert I was able to get this working as described in the SQL query. Please see below for what should be the full working representation of the SQL query:

Dim query = From t1 In dtTable1 _
    Where (T1.Field(Of String)("fieldvalue1") = SOMEVALUE) _
     Group Join t2 In dtTable2 _
     On New (t2.Field(Of String)("tableid1"), t2.Field(Of String)("tableid2")) Equals _
        (t1.Field(Of String)("tableid1"), t1.Field(Of String)("tableid2")) _
     Into t2outer = Group _
     From t2 In t2outer.DefaultIfEmpty() _
     Select New With _
    { _
        .t1_tableid1 = t1.Field(Of String)("tableid1"), _
        .t1_tableid2 = t1.Field(Of String)("tableid2"), _
        .t1_fieldvalue1 = t1.Field(Of Integer)("fieldvalue1"))
        .t2_fieldvalue2 = If(t2 Is Nothing, CType("0", Integer), t2.Field(Of Integer)("fieldvalue2"))
        .calcvalue = If(t2.Field(Of Integer)("fieldvalue2") Is Nothing, CType("0", Integer), t2.Field(Of Integer)("fieldvalue2")) _
            / CType(t1.Field(Of String)("fieldvalue1"), Integer), 
        .calcvalue2 = CType(t1.Field(Of String)("fieldvalue1"), Integer) _
            - If(t2.Field(Of Integer)("fieldvalue2") Is Nothing, CType("0", Integer), t2.Field(Of Integer)("fieldvalue2")) _
    }

推荐答案

首先，如果您对多个字段进行分组，则您正在比较匿名类型，因此该部分

First, if you group on multiple fields, you are comparing anonymous types, so the part

On
   New (t2.Field(Of String)("tableid1"), t2.Field(Of String)("tableid2"))
Equals
       (t1.Field(Of String)("tableid1"), t1.Field(Of String)("tableid2"))

应更改为用于创建匿名类型的语法(与稍后在查询中使用的语法相同):

should be changed into the syntax for creating anonymous types (same as you use later in the query):

On
  New With {.id1 = t2.Field(Of String)("tableid1"),
            .id2 = t2.Field(Of String)("tableid2")}
Equals
  New With {.id1 = t1.Field(Of String)("tableid1"),
            .id2 = t1.Field(Of String)("tableid2")}

但是

在C#中就足够了，因为对于匿名类型，C#编译器使用基于值的相等性.但是由于某些原因，与VB有所不同，它使用 reference 相等性.因此Equals始终为false，因为第一个对象与第二个对象不是同一对象.

In C# this would be enough, because with anonymous types, the C# compiler uses equality based on values. For some reason however, with VB this is different, it uses reference equality. So the Equals is always false, because the first object is not the same object as the second.

幸运的是，VB具有Key关键字，可让您指示在比较匿名对象时要使用的属性.所以最终的语法是:

Fortunately, VB has the Key keyword that lets you indicate properties to be used when comparing anonymous objects. So the final syntax is:

On
  New With {Key .id1 = t2.Field(Of String)("tableid1"),
            Key. id2 = t2.Field(Of String)("tableid2")}
Equals
  New With {Key .id1 = t1.Field(Of String)("tableid1"),
            Key .id2 = t1.Field(Of String)("tableid2")}

这篇关于Linq-to-Entities:带有WHERE子句的LEFT OUTER JOIN，计算和投影的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！