problem with new Oracle.ManagedDataAccess.EntityFramework query — oracle-tech

    Forum Stats

  • 3,716,003 Users
  • 2,242,928 Discussions
  • 7,845,734 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

problem with new Oracle.ManagedDataAccess.EntityFramework query

user13470967
user13470967 Member Posts: 2
edited August 2018 in ODP.NET

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

Best Answer

Answers

This discussion has been closed.