[c#]左外部联接条件复杂

发布时间: 2017/2/28 21:16:54
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我怎么能这样与 Linq SQL 查询?

select DISTINC
....
   from Table1
     LEFT OUTER JOIN Table2
     ON Table2.Field1 = Table1.Field1 AND
        Table2.Field2 = Table1.Field2 AND
        Table2.Field3 = Table1.Field3 AND
        (
        ( Table1.Field4 = 'Something' AND ( Table2.Field5 = 'Something'
                                 OR Table2.Field5 = 'Something' ) )
        OR
        ( Table1.Field4 = 'Something' AND ( Table2.Field5 = 'Something'
                                 OR Table2.Field5 = 'Something' ) )
        OR
        ( Table1.Field4 = 'Something' AND ( Table2.Field5 = 'Something'
                                 OR Table2.Field5 = 'Something'
                                 OR Table2.Field5 = 'Something' ) )
        )

   where 
   ....
   order by ...

我一直在做左外部联接 LinQ,但只有这样等于

from Table1 in ....
  join Table2 in ....
    on new { Table1.Field1, Table1.Field2 }
    equals new { Table2.UNField1V, Table2.Field2 }
    into Join1
  from Name in Join1.DefaultIfEmpty()
  where
  ....
  select new { ... }

却不知道要做这种事像我写的 SQL 查询条件复杂。

解决方法 1:

from t1 in Table1
from t2 in Table2.Where(t2 => t2.Field1 == t1.Field1 && /* ... complex join condition */)
                 .DefaultIfEmpty()
select new
{
    t1.AnyReferenceField,
    (int?)t2.AnotherInt32Field // !
    /* ... */
}

只是不要忘记转换 t2 的值字段是可以为 null。否则你会得到异常类似于 The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

如果你想要查询的语法︰

from t1 in Table1
from t2 in (from t2 in Table2
            where t2.Field1 == t1.Field1 && /* ... complex join condition */
            select t2).DefaultIfEmpty()
select new { /* ... */ }
官方微信
官方QQ群
31647020