vb.net - Linq-to-Entities: LEFT OUTER JOIN with WHERE clause, calculation and projection -

I have a hard time figuring out how to insert a simple SQL left over call on multiple columns and where There are only two tables in the working-to-unit query, regardless of the matches in Table 2, I need prices for all the rows from Table 1, but being included in many columns proves difficult for me to query A simple calculation , But this can not be found in the query in SQL as:

  select t1.tableid1, t1.tableid2, t1.fieldvalue1, t2.fieldvalue2, isnull (t2) .fieldvalue2.0) / calcvalue as t1.fieldvalue1 t1.fieldvalue1 - isnull (t2.fieldvalue2,0) table1 t1 to the left as externally t1.tableid1 = t2.tableid1 and t1.tableid2 = t2.tableid2 and t1 .tableid3 = t2.tableid3 Where to join table2 t2 on t1.tableid1 = @somevalue   
 I can add except one column, but I can not find the right syntax for many columns Could not Knowing how to add and subtract values ​​is my best estimate on the column but I'm getting an estimated error ("new" and before opening opening brackets):  
  New At (t2.Field (string of) ("Looks best:  
  Slow query = t1 to dtTable1 _ Join in group t2 dtTable2 _ On in t2.Field (string of)   
 (T2.Field ("table") ("tableid2")) _ t2outer = group _t2 to t2outer.DefaultIfEmpty with () _ new selection with _ { _. T1_tableid1 = t1.Field (of string) ("tableid1"), _ .t1_tableid2 = t1. Field (string of) ("tableid2"), _. T1_fieldvalue1 = t1.Field (integer) ("fieldvalue1")) .t2_fieldvalue2 =, then t2.Field ((T2 is nothing, CTYPE ("0", Is integer) of integer ("field value 2"}}   
 @Gert found me close enough, I think the formatting was a bit far away, as I had to add braces instead of brackets And placed a space between the "key" keyword and the ID:  
  with the new {key .id1 = T2.Field (of string) ("Table 1"), _key.id2 = T2.field (string of) ("ta Bleid2 ")} _ Parallels {key with .id1 = t1.fild (string of) (" Thanks to the gate I was able to get this function as described in SQL query: (for keyboard), _key , .id2 = t1.field (string of) ("tableid2")} _   
. For the full functionality of the SQL query, please see below:  < Pre>  slow query = dtTable1 _ where in T1 (T1.Field (string of) ("field value 1") = SOMEVALUE) _ in group DTT2 On the new (T2) field (string of) ("tableide 1"), T. 2 in the field (in the string) ("table 2") is equal. (T1) Field (string of) ("Table 1"), t1.Field (string) ("tableid2") _ _ in t2outer = group _t2outer.DefaultIfEmpty (in t2) _ Select new with _ {_ .t1_tableid1 = t1.Field (from "string of"), _ .t1_tableid2 = t1.Field (of string) ("tableid2"), _. T1_fieldvalue1 = (integer) (t1.Field "fieldvalue1 0 ")) .t2_fieldvalue2 = If (T2 is nothing, CTYPE (is" ", integer), T. field (" fieldwall 2 ")) .calcvalue = if (t2.Field (of integer) (" field value 2 ") is nothing, CType (" 0 ", integer), T2 field (" value value 1 "), (of integer) / ( Value value 2)) / sitip (string of) ("value value 1"), integer), .calcvalue2 = CType (t1.Field (string of) ("field value 1"), integer) _ - if (t2. Field (integer) ("Field Value 2") is nothing, CType ("0", integer), T. field (of integer) ("field value 2")}}}}   First, if you group on many areas, you are comparing anonymous types, so  
  On the new (t2.Field (of string) ("tableid1"), T2Field (str ("Table 2") of the string ("Table 2") equal to (T1fild (string of) ("Table 1"), T1Fild (string of "" 2 ")   < / P>  
  with {{.id1 = t2.Field (string of) ( "Tableid1"), .id2 = t2.field (string of) ("tableid2 "}} {.id1 = t1.Field (string of) (" tableid1 "), .id2 = t1.Field (string of" (Tableid2 ")}   
  but   
 In C # will be enough, because with the anonymous type, the C # compiler uses similarity, for some reasons based on  values ​​ however, with VB This is a C, it  context  uses equality so  equal  always  false , because the object is not the same thing to another.  
 Fortunately, there is  key  in VB, which lets you point to the properties used when comparing unknown objects, then the last syntax is:  < With the new {key .id1 = t2.field (string of) ("Table 1"), key Id2 = t2.Field (of string) ("Table 2"}} {new equation with key . Key 1 = t1. Field (string of) ("Table 1"), key .id2 = t1. Field (of string) ("tableid2")}