LINQ compiled query won't execute - ArgumentNulException was unhandled

Question

I'm testing the Compiled Queries for the first time.

My current query is:

 Shared compiledLatestDate As Func(Of RackJobbingDataDataContext, ArtikelInfoParameters, List(Of VerdelingPlanning)) = _
    CompiledQuery.Compile(Of RackJobbingDataDataContext, ArtikelInfoParameters, List(Of VerdelingPlanning))( _
        Function(db As RackJobbingDataDataContext, param As ArtikelInfoParameters) _
          (From verdplan As VerdelingPlanning In db.VerdelingPlannings _
          Join artPlan As ArtikelPlanning In db.ArtikelPlannings _
          On artPlan.VerdelingPlanningID Equals verdplan.VerdelingPlanningID _
          Join levDet As LeveringDetail In db.LeveringDetails _
          On levDet.ArtikelPlanningID Equals artPlan.ArtikelPlanningID _
          Join lev As Levering In db.Leverings _
          On lev.LeveringID Equals levDet.LeveringID _
          Where artPlan.ArtikelID.Equals(param.artId) _
          And lev.WinkelID.Equals(param.winkId) _
          And Not levDet.PlanningAantal.Equals(Nothing) _
          And levDet.PlanningAantal <> 0 _
          And verdplan.Datum < param.datumVerdeling _
          Order By verdplan.Datum Descending _
        Select levDet))

But i receive an ArgumentNulException was unhandled.

It was advised for LINQ queries to add an object with the required parameters, so this is the class:

Public Class ArtikelInfoParameters
Private _wkId As Guid
Private _artId As Guid
Private _datumVerdeling As DateTime
Public Sub New(ByVal artId As Guid, ByVal winkId As Guid, ByVal datumVerdeling As DateTime)
    _artId = artId
    _wkId = winkId
    _datumVerdeling = datumVerdeling
End Sub
Public Property artId As Guid
    Get
        Return _artId
    End Get
    Set(ByVal value As Guid)
        _artId = value
    End Set
End Property
Public Property winkId As Guid
    Get
        Return _wkId
    End Get
    Set(ByVal value As Guid)
        _wkId = value
    End Set
End Property
Public Property datumVerdeling As DateTime
    Get
        Return _datumVerdeling
    End Get
    Set(ByVal value As DateTime)
        _datumVerdeling = value
    End Set
End Property
End Class

And for the sake of being complete, this is the error i'm receiving.

  bij System.Data.Linq.SqlClient.SqlJoin.set_Left(SqlSource value)
  bij System.Data.Linq.SqlClient.SqlMultiplexer.Visitor.VisitMultiset(SqlSubSelect sms)
  bij System.Data.Linq.SqlClient.SqlVisitor.VisitSubSelect(SqlSubSelect ss)
  bij System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
  bij System.Data.Linq.SqlClient.SqlVisitor.VisitExpression(SqlExpression exp)
  bij System.Data.Linq.SqlClient.SqlMultiplexer.Visitor.VisitSelect(SqlSelect select)
  bij System.Data.Linq.SqlClient.SqlVisitor.Visit(SqlNode node)
  bij System.Data.Linq.SqlClient.SqlProvider.BuildQuery(ResultShape resultShape, Type resultType, SqlNode node, ReadOnlyCollection`1 parentParameters, SqlNodeAnnotations annotations)
  bij System.Data.Linq.SqlClient.SqlProvider.BuildQuery(Expression query, SqlNodeAnnotations annotations)
  bij System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Compile(Expression query)
  bij System.Data.Linq.CompiledQuery.ExecuteQuery(DataContext context, Object[] args)
  bij System.Data.Linq.CompiledQuery.Invoke[TArg0,TArg1,TResult](TArg0 arg0, TArg1 arg1)
  bij RackJobbing.UI.ucArtikelInfo.getLatestDate(Guid artikelId) in D:\EasySolutions\TFS\Provoost\Development\RackJobbing\RackJobbing.UI\Snelinvoer\Verdeling\ucArtikelInfo.vb:regel 69
  bij RackJobbing.UI.ucArtikelInfo.UpdateInfo() in D:\EasySolutions\TFS\Provoost\Development\RackJobbing\RackJobbing.UI\Snelinvoer\Verdeling\ucArtikelInfo.vb:regel 111
  bij RackJobbing.UI.frmPrePlanning.UcVerdeling1_ArtikelWinkelChanged() in D:\EasySolutions\TFS\Provoost\Development\RackJobbing\RackJobbing.UI\Snelinvoer\Verdeling\frmPrePlanning.vb:regel 280
  bij RackJobbing.UI.ucVerdeling.grdVerdeling_EnterCell(Object sender, EventArgs e) in D:\EasySolutions\TFS\Provoost\Development\RackJobbing\RackJobbing.UI\Snelinvoer\Verdeling\ucVerdeling.vb:regel 339
  bij C1.Win.C1FlexGrid.C1FlexGridBase.OnEnterCell(EventArgs e)
  bij C1.Win.C1FlexGrid.C1FlexGridBase.a9()
  bij C1.Win.C1FlexGrid.C1FlexGridBase.OnGridChanged(Object sender, GridChangedEventArgs e)
  bij C1.Win.C1FlexGrid.C1FlexGrid.OnGridChanged(Object sender, GridChangedEventArgs e)
  bij C1.Win.C1FlexGrid.t.bc(GridChangedTypeEnum A_0, Int32 A_1, Int32 A_2, Int32 A_3, Int32 A_4)
  bij C1.Win.C1FlexGrid.t.bn(GridChangedTypeEnum A_0)
  bij C1.Win.C1FlexGrid.p.n(C1FlexGridBase A_0, CellRange A_1, Boolean A_2)
  bij C1.Win.C1FlexGrid.C1FlexGridBase.Select(CellRange rg, Boolean show)
  bij C1.Win.C1FlexGrid.ab.o(MouseEventArgs A_0)
  bij C1.Win.C1FlexGrid.ab.l(MouseEventArgs A_0)
  bij C1.Win.C1FlexGrid.C1FlexGridBase.OnMouseDown(MouseEventArgs e)
  bij System.Windows.Forms.Control.WmMouseDown(Message& m, MouseButtons button, Int32 clicks)
  bij System.Windows.Forms.Control.WndProc(Message& m)
  bij C1.Win.C1FlexGrid.Util.BaseControls.ScrollableControl.WndProc(Message& m)
  bij System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
  bij System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
  bij System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
  bij System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
  bij System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
  bij System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
  bij System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
  bij System.Windows.Forms.Application.Run(ApplicationContext context)
  bij Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
  bij Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
  bij Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
  bij RackJobbing.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:regel 81
  bij System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
  bij System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
  bij Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
  bij System.Threading.ThreadHelper.ThreadStart_Context(Object state)
  bij System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
  bij System.Threading.ThreadHelper.ThreadStart()

I'd appreciate any help in solving my problem , because i really can't seem to find the solution :(

Answer 1

Solution:

Public Shared CompiledLatestDate As Func(Of RackJobbingDataDataContext, ArtikelInfoParameters, IQueryable(Of VerdelingPlanning)) = _
   CompiledQuery.Compile(Function(db As RackJobbingDataDataContext, artInfo As ArtikelInfoParameters) _
        From verdplan As VerdelingPlanning In db.VerdelingPlannings _
              Join artPlan As ArtikelPlanning In db.ArtikelPlannings _
              On verdplan.VerdelingPlanningID Equals artPlan.VerdelingPlanningID _
              Join levDet As LeveringDetail In db.LeveringDetails _
              On artPlan.ArtikelPlanningID Equals levDet.ArtikelPlanningID _
              Join lev As Levering In db.Leverings _
              On levDet.LeveringID Equals lev.LeveringID _
              Where artPlan.ArtikelID.Equals(artInfo.artId) _
              And lev.WinkelID.Equals(artInfo.winkId) _
              And Not levDet.PlanningAantal.Equals(Nothing) _
              And levDet.PlanningAantal <> 0 _
              And verdplan.Datum < artInfo.datumVerdeling _
              Order By verdplan.Datum Descending _
            Select verdplan)

Solved almost all of my problems, even though i sometimes get a NullReferenceException when there is no value in the collection (it doesn't always give an error, so i'm still looking into that).

Answer 2

Unlike SQL, the ON clause's left and right sides have different scope visibility.

You need to put variables that were in scope before the JOIN on the left side of the ON clause, and variables that are newly in scope from the JOIN on the right side.

Here I have switched the variables in ON clauses.

From verdplan As VerdelingPlanning In db.VerdelingPlannings _
  Join artPlan As ArtikelPlanning In db.ArtikelPlannings _
  On verdplan.VerdelingPlanningID Equals artPlan.VerdelingPlanningID _
  Join levDet As LeveringDetail In db.LeveringDetails _
  On artPlan.ArtikelPlanningID Equals levDet.ArtikelPlanningID _
  Join lev As Levering In db.Leverings _
  On levDet.LeveringID Equals lev.LeveringID _