CODEWITHSUNDEEP
c#linqentity-frameworkt-sqlleft-join
JuanDYB
JuanDYB

Reputation: 754

Left Outer Join with Complex conditions

How can I do an SQL Query like this with 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 have been doing LEFT OUTER JOINS in LinQ but only with equals like this

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 have no idea to do something like that with complex conditions like the SQL Query that I have written.

Upvotes: 3

Views: 453

Answers (1)

lorond
lorond

Reputation: 3896

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 do not forget to convert t2's value fields to be nullable. Otherwise you will get exception similar to 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..

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 { /* ... */ }

Upvotes: 1

Related Questions