Skip to Main Content

ODP.NET

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

problem with new Oracle.ManagedDataAccess.EntityFramework query

user13470967Aug 16 2018 — edited Aug 16 2018

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

This post has been answered by user13470967 on Aug 16 2018
Jump to Answer

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 13 2018
Added on Aug 16 2018
1 comment
290 views