this is my query:
var query = DB.NetaAppeals.Where(A => A.APL_DATETIME > from && A.APL_DATETIME < to && A.APL_STATUS_ID == 2 && A.APL_SERVICE_ID.Value != 319)
.Join(DB.NetaTasks.Where(H => H.TSK_ID > MinTaskID && H.TSK_DATETIME > from && H.TSK_DATETIME < to && H.TSK_STATUS == 2)
, A => A.APL_CORRELATION_ID, B => B.TSK_CORRELATION_ID, (A, B) => new AppealsTasksFlat() { Appeal = A, Task = B, Decision = null })
.Join<AppealsTasksFlat, TBL_DECISIONS, tempDecCompare, AppealsTasksFlat>(DB.NetaDecision, A => new tempDecCompare()
{ A = (A.Task.TSK_DECISION_ID == -888 ? A.Appeal.APL_SERVICE_ID.Value : A.Task.TSK_BASE_SERVICE_ID), B = A.Task.TSK_DECISION_ID },
B => new tempDecCompare() { A = B.DEC_SERVICE_ID, B = B.DEC_ID }, (A, B) => new AppealsTasksFlat() { Appeal = A.Appeal, Task = A.Task, Decision = B })
.GroupJoin(DB.Files, A => (A.Decision.SRV_SAVE_LEVEL == (int)SaveLevel.All || A.Decision.SRV_SAVE_LEVEL == (int)SaveLevel.Original ?
A.Appeal.APL_ORIGINAL_DATA_ID : 0), B => B.TND_ID, (A, B) => new { A, FileData = B });
var query2 = query.GroupJoin(DB.Files, A => (A.A.Decision.SRV_SAVE_LEVEL == (int)SaveLevel.All || A.A.Decision.SRV_SAVE_LEVEL == (int)SaveLevel.Sent ?
(A.A.Task.TSK_SENT_DATA_ID.Value != A.A.Appeal.APL_ORIGINAL_DATA_ID ? A.A.Task.TSK_SENT_DATA_ID.Value : 0) : 0),
B => B.TND_ID, (A, B) => new { A.A, FileDataOrig = A.FileData, FileDataSend = B });
var query3 = query2.GroupJoin(DB.Files, A => (A.A.Decision.SRV_SAVE_LEVEL == (int)SaveLevel.All || A.A.Decision.SRV_SAVE_LEVEL == (int)SaveLevel.Sent || A.A.Decision.SRV_SAVE_LEVEL == (int)SaveLevel.Original ?
(A.A.Decision.SIGNED == 1 ? A.A.Appeal.APL_SIGNED_DATA_ID : 0) : 0),
B => B.TND_ID, (A, B) => new { A.A, FileDataOrig = A.FileDataOrig, FileDataSend = A.FileDataSend, FileSigned = B });
var query4 = query3.GroupJoin(DB.XML, A => (A.A.Decision.SRV_SAVE_LEVEL != (int)SaveLevel.None ? A.A.Task.TSK_BASE_DATA_ID : 0) , B => B.TND_ID, (A, B) => new { A.A, FilesOriginal = A.FileDataOrig, FilesSend = A.FileDataSend, FileSigned = A.FileDataSend, XML = B})
.Where( A=> A.A.Decision.SRV_SAVE_LEVEL != (int)SaveLevel.None);
query , query2 , query3 have good sql translation.
query4 scrambles the SQL by duplicating one of the joins !!! (wrong results)
this is a serious bug.
version 12.2.1100