Data Directive BPM C# Linq Help

markdamen · May 9, 2018, 10:19pm

Hi

Please can somebody help with this statement:

var shipLines =
from sd in
Db.ShipDtl

                         join cp **in**

Db.CustXPrt

on

new { sd.Company, sd.CustNum, sd.PartNum }

                         equals

new { cp.Company, cp.CustNum, cp.PartNum }

            where sd.PackNum == iPackNum && cp.CustNum == iCustNum

            orderby sd.PartNum

            select new {sd.PackNum, sd.PackLine, sd.OrderNum, sd.PartNum, cp.XPartNum, sd.LineDesc} ;

I want to make it a left join, such that ShipDtl records are always returned, whether or not there is a CustXPrt record.

Many Thanks

Mark

danbedwards · May 9, 2018, 10:55pm

Something like this should work for the join

var shiplines = 
(from sd in Db.ShipDtl
join cp in Db.CustXPrt 
on new {sd.Company, sd.CustNum, sd.PartNum } equals new {cp.Company, cp.CustNum, cp.PartNum }
into cpleft
from cp in cpleft.DefaultIfEmpty()

asmar · May 10, 2018, 12:29am

According to Epicor support, LINQ with left outer join in BPM C# code is not supported in E10.1. As a workaround, I use an equal join and check the status of the returned row.

hkeric.wci · May 10, 2018, 12:38am

That’s odd - I’ve been doing Left JOINs. Unless you are thinking of something else.

timshuwy · May 10, 2018, 12:39am

I believe I have done that as well… i believe it is the “DefaultIfEmpty()” tag that allows for the left join.

josecgomez · May 10, 2018, 12:47am

Left joins work just fine in any version of 10

asmar · May 10, 2018, 1:14am

I could never get this to work in my environment.

CS0000637810 - TO TECH Does Epicor E10 support left outer join using LINQ in BPM ??

NA
Nathan Anderson
Customer Visible Notes(comments)
2017-10-13 14:18:43

From one of our BPM framework developers:

If we are speaking about BPM Queries (those designed graphically in BPM Query designer which looks similar to BAQ designer, but is completely different), then no, we do not support left outer joins.

If the question is about LINQ queries in BPM Custom code action (not available on MT-SaaS), then it is simply generic C# code written against the Epicor data model where regular approaches, given the understanding of the difference between database and in-memory tables, are applicable.

Google, MSDN, and StackOverflow provide this answer.
msdn c# linq left outer join - Google Search

The takeaway: There isn’t anything within the framework that would prevent this from working, but, we do not have an example within Support that we can provide. If e10help cannot provide an example that you can work with, our custom programming / education team can assist you on a billable basis.

Please let me know if you have any additional questions regarding this.

asmar · June 14, 2018, 9:38pm

I used you examples in my code, and it did not work. Notice, I have a table criteria that I need to use:

  	 var OrderHedVar = (from o in Db.OrderHed.With(LockHint.NoLock)
				// *** Add left outer join to OrderDtl
			     join od in Db.OrderDtl.With(LockHint.NoLock) 
                 on new { o.Company, o.OrderNum } equals new { od.Company, od.OrderNum }
         into oodj 
         from od in oodj.DefaultIfEmpty()

				// ***
        
     where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber   

           select new 
           {
              o.OrderNum, o.RequestDate

           }).ToList();

Chris_Conn · June 15, 2018, 1:03am

You are using a few variables we have no clue what they are:
TaskListVar
CaseNum

Also you reference o.HDCaseNum is that a member of the OrderHed table?

asmar · June 15, 2018, 10:35am

The TaskListVar.Comany and CaseNumber are coming from the BPM method temporary table.

Mazin

asmar · June 15, 2018, 12:44pm

The variables are used in the Where clause as a table criteria to filter out the rows from the first (primary) table.

Chris_Conn · June 15, 2018, 5:52pm

When you say it doesnt work - what do you mean? Syntax error? No results? Wrong Results?

I start debugging by verifying I have the data I expect in those vars.

Next I’d some criteria on the records I was trying to join like:

from o in Db.OrderHed.Where(o => o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber)
				// *** Add left outer join to OrderDtl
			     join od in Db.OrderDtl.Where(d => d.Company == TaskListVar.Company)

asmar · June 15, 2018, 6:13pm

The BPM will not compile and I get a long convoluted error message that makes not sense. Prior to the left out join syntax, the BPM worked directly with one table join with the same where critera listed for the Company and CaseNum. The only think changed in the BPM is the syntax for the left outer join to the QuoteDtl table.

So my original question still stand, which is; does Epicor support a left outer join from BPM using LINQ syntax? The example provided in this post does not show a table criteria for the first table. The table criteria is need to filter the source rows prior to joining to the second table.

josecgomez · June 15, 2018, 6:20pm

What’s the error…

Ernie · June 15, 2018, 6:40pm

(because as we ALL know, Jose can read those otherwise inscrutable error messages)

knash · June 15, 2018, 6:45pm

I agree Chris. I will add that there seems to be some code outside of the code snippet. Hard to tell where the error is coming from.

Epicor does support Left Joins.

Are you able to get the join to work with out all of the extra stuff Just a plain left join? Then add to the where clause. Reduce the number of moving parts in the code.

Mark_Wonsil · June 15, 2018, 6:53pm

Dang it. I swear Jose told me he could read auror messages…

Now i feel stupified…

asmar · June 15, 2018, 7:56pm

Server Side Exception

There is at least one compilation error.

Exception caught in: Epicor.ServiceModel

Error Detail

Description: There is at least one compilation error.
Details:
Error CS1061: ‘System.Collections.Generic.List<AnonymousType#1>’ does not contain a definition for ‘OrderNum’ and no extension method ‘OrderNum’ accepting a first argument of type ‘System.Collections.Generic.List<AnonymousType#1>’ could be found (are you missing a using directive or an assembly reference?) [GetTaskList.Post.Set_Quote_Number.cs(277,50)]
Error CS1061: ‘System.Collections.Generic.List<AnonymousType#1>’ does not contain a definition for ‘RequestDate’ and no extension method ‘RequestDate’ accepting a first argument of type ‘System.Collections.Generic.List<AnonymousType#1>’ could be found (are you missing a using directive or an assembly reference?) [GetTaskList.Post.Set_Quote_Number.cs(279,58)]
Error CS1061: ‘System.Collections.Generic.List<AnonymousType#1>’ does not contain a definition for ‘DocTotalCharges’ and no extension method ‘DocTotalCharges’ accepting a first argument of type ‘System.Collections.Generic.List<AnonymousType#1>’ could be found (are you missing a using directive or an assembly reference?) [GetTaskList.Post.Set_Quote_Number.cs(280,70)]
Program: Epicor.Customization.dll
Method: PrepareException
Line Number: 99
Column Number: 13
Server Trace Stack: at Epicor.Customization.Standard.CustomizationCompiler.PrepareException(CompilerErrorCollection errors) in c:_Releases\ICE\3.1.400.38\Source\Framework\Epicor.Customization\Standard\CustomizationCompiler.cs:line 99
at Epicor.Customization.Standard.CustomizationCompiler.Compile(BuildEnvironment input, String outputAssembly) in c:_Releases\ICE\3.1.400.38\Source\Framework\Epicor.Customization\Standard\CustomizationCompiler.cs:line 62
at Epicor.Customization.Standard.CustomizationBuilder.Process(CustomizationProject project) in c:_Releases\ICE\3.1.400.38\Source\Framework\Epicor.Customization\Standard\CustomizationBuilder.cs:line 78
at Ice.Services.BO.BpMethodSvc.AfterUpdate() in c:_Releases\ICE\3.1.400.38\Source\Server\Services\BO\BpMethod\BpMethod.Events.cs:line 89
at Ice.Services.Trace.TablesetProfilingCollector.DoTablesetEventTrace(String tablesetName, String methodName, Action action) in c:_Releases\ICE\3.1.400.38\Source\Framework\Epicor.Ice\Services\TablesetProfilingCollector.cs:line 198
at Ice.TablesetBound3.InnerUpdate(IceDataContext dataContext, TFullTableset tableset) in c:\_Releases\ICE\3.1.400.38\Source\Framework\Epicor.Ice\Services\TablesetBound.cs:line 813 at Ice.Services.BO.BpMethodSvc.Update(BpMethodTableset& ds) in c:\_Releases\ICE\3.1.400.38\Source\Server\Services\BO\BpMethod\BpMethod.Designer.cs:line 837 at Ice.Services.BO.BpMethodSvcFacade.Update(BpMethodTableset& ds) in c:\_Releases\ICE\3.1.400.38\Source\Server\Services\BO\BpMethod\BpMethodSvcFacade.cs:line 224 at SyncInvokeUpdate(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at Epicor.Hosting.OperationBoundInvoker.InnerInvoke(Object instance, Func2 func) in c:_Releases\ICE\3.1.400.38\Source\Framework\Epicor.System\Hosting\OperationBoundInvoker.cs:line 59
at Epicor.Hosting.OperationBoundInvoker.Invoke(Object instance, Func2 func) in c:\_Releases\ICE\3.1.400.38\Source\Framework\Epicor.System\Hosting\OperationBoundInvoker.cs:line 28 at Epicor.Hosting.Wcf.EpiOperationInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) in c:\_Releases\ICE\3.1.400.38\Source\Framework\Epicor.System\Hosting\Wcf\EpiOperationInvoker.cs:line 23 at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc) at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage31(MessageRpc& rpc) at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet) at System.ServiceModel.Dispatcher.ChannelHandler.DispatchAndReleasePump(RequestContext request, Boolean cleanThread, OperationContext currentOperationContext) at System.ServiceModel.Dispatcher.ChannelHandler.HandleRequest(RequestContext request, OperationContext currentOperationContext) at System.ServiceModel.Dispatcher.ChannelHandler.AsyncMessagePump(IAsyncResult result) at System.Runtime.Fx.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result) at System.Runtime.AsyncResult.Complete(Boolean completedSynchronously) at System.ServiceModel.Channels.SecurityChannelListener1.ReceiveItemAndVerifySecurityAsyncResult`2.InnerTryReceiveCompletedCallback(IAsyncResult result)
at System.Runtime.Fx.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result)
at System.Runtime.AsyncResult.Complete(Boolean completedSynchronously)
at System.ServiceModel.Channels.TransportDuplexSessionChannel.TryReceiveAsyncResult.OnReceive(IAsyncResult result)
at System.Runtime.Fx.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result)
at System.Runtime.AsyncResult.Complete(Boolean completedSynchronously)
at System.ServiceModel.Channels.SynchronizedMessageSource.ReceiveAsyncResult.OnReceiveComplete(Object state)
at System.ServiceModel.Channels.SessionConnectionReader.OnAsyncReadComplete(Object state)
at System.Runtime.Fx.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result)
at System.Net.LazyAsyncResult.Complete(IntPtr userToken)
at System.Net.Security.NegotiateStream.ProcessFrameBody(Int32 readBytes, Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.NegotiateStream.ReadCallback(AsyncProtocolRequest asyncRequest)
at System.Net.FixedSizeReader.CheckCompletionBeforeNextRead(Int32 bytes)
at System.Net.FixedSizeReader.ReadCallback(IAsyncResult transportResult)
at System.Runtime.AsyncResult.Complete(Boolean completedSynchronously)
at System.ServiceModel.Channels.ConnectionStream.IOAsyncResult.OnAsyncIOComplete(Object state)
at System.ServiceModel.Channels.SocketConnection.OnReceiveAsync(Object sender, SocketAsyncEventArgs eventArgs)
at System.Net.Sockets.SocketAsyncEventArgs.FinishOperationSuccess(SocketError socketError, Int32 bytesTransferred, SocketFlags flags)
at System.Net.Sockets.SocketAsyncEventArgs.CompletionPortCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* nativeOverlapped)
at System.Threading._IOCompletionCallback.PerformIOCompletionCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* pOVERLAP)

Client Stack Trace

at Epicor.ServiceModel.Channels.ImplBase`1.ShouldRethrowNonRetryableException(Exception ex, DataSet[] dataSets)
at Ice.Proxy.BO.BpMethodImpl.Update(BpMethodDataSet ds)
at Ice.Adapters.BpMethodAdapter.OnUpdate()
at Ice.Lib.Framework.EpiBaseAdapter.Update()
at Ice.UI.App.BpMethodEntry.Transactions.MainTransactionBase.adapterUpdate()

asmar · June 15, 2018, 7:57pm

 // Start new code
 var OrderHedVar = (from o in Db.OrderHed.With(LockHint.NoLock)
			// *** Add left outer join to OrderDtl
		     join od in Db.OrderDtl.With(LockHint.NoLock) 
             on new { o.Company, o.OrderNum } equals new { od.Company, od.OrderNum }
     into oodj 
     from od in oodj.DefaultIfEmpty()

			// ***
    
 where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber   

       select new 
       {
          o.OrderNum, o.RequestDate

       }).ToList();

  // end new code
  /* old code <-- 	 var OrderHedVar = (from o in Db.OrderHed.With(LockHint.NoLock)
     where o.Company == TaskListVar.Company && o.HDCaseNum == CaseNumber   
     //orderby l.TaskSeqNum ascending
			 //orderby l.TaskSeqNum descending
           select new 
           {
              o.OrderNum, o.RequestDate, o.DocTotalCharges

           }).FirstOrDefault();
			
		/* old code */

josecgomez · June 15, 2018, 8:06pm

so you are not using od at all… why even do the join?

Data Directive BPM C# Linq Help

Google, MSDN, and StackOverflow provide this answer. msdn c# linq left outer join - Google Search

Error Detail

Client Stack Trace

Google, MSDN, and StackOverflow provide this answer.
msdn c# linq left outer join - Google Search