Left external link with difficult conditions
How can I execute a SQL query from Linq?
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 ...
I did LEFT OUTER JOINS in LinQ but only with the same values
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 { ... }
But I'm not going to do something like this with complex conditions like the SQL Query I wrote.
+3
source to share
1 answer
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 // !
/* ... */
}
Just remember to convert the t2 values to NULL. Otherwise, you will get an exception like The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type generic parameter or the query must use a nullable type.
.
If you want query syntax:
from t1 in Table1
from t2 in (from t2 in Table2
where t2.Field1 == t1.Field1 && /* ... complex join condition */
select t2).DefaultIfEmpty()
select new { /* ... */ }
+1
source to share