LINQ Join multiple conditions with OR operator

I have something like

SELECT [t0].[Id], [t1].[Id], [t1].[DepartmentId]
FROM [ScannedDocuments] AS [t0]
INNER JOIN [Documents_RelatedDepartments] AS [t1]
    ON  (([t0].[Id] = [t1].[Id]) AND (1 = [t1].[DepartmentId]))
     OR 开发者_StackOverflow社区(([t0].[Id] = [t1].[Id]) AND (56 = [t0].InsertById))

results as expected on sql server but I couldn't translate it to Linq. Any thoughts?

Thanks in advance.

Try this:

from t0 in ctx.ScannedDocuments
from t1 in ctx.Documents_RelatedDepartments
where 
(
    (t0.Id == t1.Id) && (t1.Id == 1) ||
    (t0.Id == t1.Id) && (t0.InsertById == 56) 
)
select new {t0.Id, Id2=t1.Id, t1.DepartmentId}

Looks like that would be something like:

var query = from doc in context.ScannedDocuments
            join department in context.RelatedDocuments 
              on doc.Id equals department.Id
            where doc.InsertById == 56 || department.DepartmentId == 1
            select new { DocId = doc.Id, DepartmentId = department.Id };

There's a simpler query. Also note that the Id's are constrained to being the same by the join so you don't need both in the output.

db.ScannedDocuments
  .Where( s => s.InsertById == 56)
  .Join( db.Documents_RelatedDepartments
           .Where( d => d.DepartmentId == 1 ),
         o => o.Id,
         i => i.Id,
         (o,i) => new { o.Id, i => i.DepartmentId } )