- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Load and filter related entity generate ORMQueryExecutionException for missing parameter
Joined: 14-Jul-2006
I'm new to LINQ, but I'm a LLBLGen long time user I'm trying to use LINQ to fetch and filter data from 2 joined tables. I'm trying 2 similar LINQ query, but both generate a ORMQueryExecutionException for missing parameters.
query 1 (I believe this one use an implicit join...)
Dim q = From ev In metaData.PersonaEvento
Where ev.PersonaProfessionista.FlgInterno = True And ev.DataEvento.Value < #11/6/2010#
Select New With {._IdPersona = ev.PersonaProfessionista.Idpersona, ._DataEvento = ev.DataEvento}
query 2 (like query 1, but with explicit join)
Dim q = From ev In metaData.PersonaEvento
Join iter In metaData.PersonaProfessionista On ev.IdpersonaProfessionista Equals iter.IdpersonaProfessionista
Where (iter.FlgInterno = True And ev.DataEvento.Value < #11/6/2010#)
Select New With {._IdPersona = iter.Idpersona, ._DataEvento = ev.DataEvento}
Query 1 generate the following SQL
Generated Sql query:
Query: SELECT [LPA_L1].[IDPersona] AS [_IdPersona], [LPA_L2].[DataEvento] AS [_DataEvento] FROM ( [PersonaProfessionista] [LPA_L1] RIGHT JOIN [PersonaEvento] [LPA_L2] ON [LPA_L1].[IDPersonaProfessionista]=[LPA_L2].[IDPersonaProfessionista]) WHERE ( ( ( ( ( [].[LPFA_1] = @LPFA_11)))))
Parameter: @LPFA_11 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.
Query 2 generate the following SQL
Generated Sql query:
Query: SELECT [LPA_L2].[IDPersona] AS [_IdPersona], [LPA_L1].[DataEvento] AS [_DataEvento] FROM ( [PersonaEvento] [LPA_L1] INNER JOIN [PersonaProfessionista] [LPA_L2] ON [LPA_L1].[IDPersonaProfessionista] = [LPA_L2].[IDPersonaProfessionista]) WHERE ( ( ( ( ( [].[LPFA_3] = @LPFA_31)))))
Parameter: @LPFA_31 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.
In both query something is missing in the where clause "[].[LPFA_1] = @LPFA_11" / " [].[LPFA_3] = @LPFA_31" Both query are missing the filter on the data field (LINQ: DataEvento.Value < #11/6/2010#)
Query 1 generate the following exception:
Exception Analysis:
Object = SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException
_exceptionInfo (F) = System.Collections.Generic.Dictionary`2[SD.LLBLGen.Pro.ORMSupportClasses.ExceptionInfoElement,System.Object] (object System.Collections.Generic.Dictionary`2[[SD.LLBLGen.Pro.ORMSupportClasses.ExceptionInfoElement, SD.LLBLGen.Pro.ORMSupportClasses.NET20, Version=2.6.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27],[System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]) *) Total item count =6
_HResult (F) = -2146232832
_message (F) = An exception was caught during the execution of a retrieval query: Nessun valore specificato per alcuni parametri necessari.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
_parameters (F) = System.Data.OleDb.OleDbParameterCollection (object System.Data.OleDb.OleDbParameterCollection) *) Total item count =1
_queryExecuted (F) =
Query: SELECT [LPA_L1].[IDPersona] AS [_IdPersona], [LPA_L2].[DataEvento] AS [_DataEvento] FROM ( [PersonaProfessionista] [LPA_L1] RIGHT JOIN [PersonaEvento] [LPA_L2] ON [LPA_L1].[IDPersonaProfessionista]=[LPA_L2].[IDPersonaProfessionista]) WHERE ( ( ( ( ( [].[LPFA_1] = @LPFA_11)))))
Parameter: @LPFA_11 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.
Data = System.Collections.ListDictionaryInternal (object System.Collections.ListDictionaryInternal) *) Total item count =0
DbSpecificExceptionInfo = System.Collections.Generic.Dictionary`2[SD.LLBLGen.Pro.ORMSupportClasses.ExceptionInfoElement,System.Object] (object System.Collections.Generic.Dictionary`2[[SD.LLBLGen.Pro.ORMSupportClasses.ExceptionInfoElement, SD.LLBLGen.Pro.ORMSupportClasses.NET20, Version=2.6.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27],[System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]) *) Total item count =6
HResult = -2146232832
InnerException = System.Data.OleDb.OleDbException: Nessun valore specificato per alcuni parametri necessari.
in System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
in System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
in System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
in System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
in System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
in System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
in System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
in SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) (object System.Data.OleDb.OleDbException)
IsTransient = False
Message = An exception was caught during the execution of a retrieval query: Nessun valore specificato per alcuni parametri necessari.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
Parameters = System.Data.OleDb.OleDbParameterCollection (object System.Data.OleDb.OleDbParameterCollection) *) Total item count =1
QueryExecuted =
Query: SELECT [LPA_L1].[IDPersona] AS [_IdPersona], [LPA_L2].[DataEvento] AS [_DataEvento] FROM ( [PersonaProfessionista] [LPA_L1] RIGHT JOIN [PersonaEvento] [LPA_L2] ON [LPA_L1].[IDPersonaProfessionista]=[LPA_L2].[IDPersonaProfessionista]) WHERE ( ( ( ( ( [].[LPFA_1] = @LPFA_11)))))
Parameter: @LPFA_11 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.
RuntimeBuild = 09302010
RuntimeVersion = 2.6.0.0
Source = SD.LLBLGen.Pro.ORMSupportClasses.NET20
StackTrace = in SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
in SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
in SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun)
in SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator()
in System.Windows.Forms.BindingSource.GetListFromEnumerable(IEnumerable enumerable)
in System.Windows.Forms.BindingSource.ResetList()
in System.Windows.Forms.BindingSource.set_DataSource(Object value)
in ISI.AlboTop.frmLLBLFilterTest.BindData(Object pDataSource) in C:\_Data\_MaxData\Sviluppo.Net2\AlboTop\AlboTop SOL\ISI.AlboTop PRJ\Max\frmLLBLFilterTest.vb:riga 23
in ISI.AlboTop.frmLLBLFilterTest.TrappedButton4_Click(Object sender, EventArgs e) in C:\_Data\_MaxData\Sviluppo.Net2\AlboTop\AlboTop SOL\ISI.AlboTop PRJ\Max\frmLLBLFilterTest.vb:riga 1455
in System.Windows.Forms.Control.OnClick(EventArgs e)
in System.Windows.Forms.Button.OnClick(EventArgs e)
in ISI.AlboTop.Gui.TrappedButton.OnClick(EventArgs e) in C:\_Data\_MaxData\Sviluppo.Net2\AlboTop\AlboTop SOL\ISI.AlboTop.Net2\_Controls\TrappedButton.vb:riga 72
TargetSite = System.Data.IDataReader Execute(System.Data.CommandBehavior) (object System.Reflection.RuntimeMethodInfo)
query 2 generate the following exception
Exception Analysis:
Object = SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException
_exceptionInfo (F) = System.Collections.Generic.Dictionary`2[SD.LLBLGen.Pro.ORMSupportClasses.ExceptionInfoElement,System.Object] (object System.Collections.Generic.Dictionary`2[[SD.LLBLGen.Pro.ORMSupportClasses.ExceptionInfoElement, SD.LLBLGen.Pro.ORMSupportClasses.NET20, Version=2.6.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27],[System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]) *) Total item count =6
_HResult (F) = -2146232832
_message (F) = An exception was caught during the execution of a retrieval query: Nessun valore specificato per alcuni parametri necessari.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
_parameters (F) = System.Data.OleDb.OleDbParameterCollection (object System.Data.OleDb.OleDbParameterCollection) *) Total item count =1
_queryExecuted (F) =
Query: SELECT [LPA_L2].[IDPersona] AS [_IdPersona], [LPA_L1].[DataEvento] AS [_DataEvento] FROM ( [PersonaEvento] [LPA_L1] INNER JOIN [PersonaProfessionista] [LPA_L2] ON [LPA_L1].[IDPersonaProfessionista] = [LPA_L2].[IDPersonaProfessionista]) WHERE ( ( ( ( ( [].[LPFA_3] = @LPFA_31)))))
Parameter: @LPFA_31 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.
Data = System.Collections.ListDictionaryInternal (object System.Collections.ListDictionaryInternal) *) Total item count =0
DbSpecificExceptionInfo = System.Collections.Generic.Dictionary`2[SD.LLBLGen.Pro.ORMSupportClasses.ExceptionInfoElement,System.Object] (object System.Collections.Generic.Dictionary`2[[SD.LLBLGen.Pro.ORMSupportClasses.ExceptionInfoElement, SD.LLBLGen.Pro.ORMSupportClasses.NET20, Version=2.6.0.0, Culture=neutral, PublicKeyToken=ca73b74ba4e3ff27],[System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]) *) Total item count =6
HResult = -2146232832
InnerException = System.Data.OleDb.OleDbException: Nessun valore specificato per alcuni parametri necessari.
in System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
in System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
in System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
in System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
in System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
in System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
in System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
in SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) (object System.Data.OleDb.OleDbException)
IsTransient = False
Message = An exception was caught during the execution of a retrieval query: Nessun valore specificato per alcuni parametri necessari.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
Parameters = System.Data.OleDb.OleDbParameterCollection (object System.Data.OleDb.OleDbParameterCollection) *) Total item count =1
QueryExecuted =
Query: SELECT [LPA_L2].[IDPersona] AS [_IdPersona], [LPA_L1].[DataEvento] AS [_DataEvento] FROM ( [PersonaEvento] [LPA_L1] INNER JOIN [PersonaProfessionista] [LPA_L2] ON [LPA_L1].[IDPersonaProfessionista] = [LPA_L2].[IDPersonaProfessionista]) WHERE ( ( ( ( ( [].[LPFA_3] = @LPFA_31)))))
Parameter: @LPFA_31 : Boolean. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: True.
RuntimeBuild = 09302010
RuntimeVersion = 2.6.0.0
Source = SD.LLBLGen.Pro.ORMSupportClasses.NET20
StackTrace = in SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
in SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior)
in SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun)
in SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize)
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.Execute()
in SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.IEnumerable.GetEnumerator()
in System.Windows.Forms.BindingSource.GetListFromEnumerable(IEnumerable enumerable)
in System.Windows.Forms.BindingSource.ResetList()
in System.Windows.Forms.BindingSource.set_DataSource(Object value)
in ISI.AlboTop.frmLLBLFilterTest.BindData(Object pDataSource) in C:\_Data\_MaxData\Sviluppo.Net2\AlboTop\AlboTop SOL\ISI.AlboTop PRJ\Max\frmLLBLFilterTest.vb:riga 23
in ISI.AlboTop.frmLLBLFilterTest.TrappedButton4_Click(Object sender, EventArgs e) in C:\_Data\_MaxData\Sviluppo.Net2\AlboTop\AlboTop SOL\ISI.AlboTop PRJ\Max\frmLLBLFilterTest.vb:riga 1460
in System.Windows.Forms.Control.OnClick(EventArgs e)
in System.Windows.Forms.Button.OnClick(EventArgs e)
in ISI.AlboTop.Gui.TrappedButton.OnClick(EventArgs e) in C:\_Data\_MaxData\Sviluppo.Net2\AlboTop\AlboTop SOL\ISI.AlboTop.Net2\_Controls\TrappedButton.vb:riga 72
TargetSite = System.Data.IDataReader Execute(System.Data.CommandBehavior) (object System.Reflection.RuntimeMethodInfo)
Maybe I'm doing something wrong?
I'm using the following development setting: *) LLBLGenPro 2.6 Final (09-October-2009) (Library Lib 2.6.10.0930) *) Code generation: Adapter/VB.Net 3.5/Standard Templates *) .Net 3.5, VisualStudio 2010 *) Database: Access2k
Thanks, Massimiliano
Joined: 14-Jul-2006
Otis wrote:
Use AndAlso, instead of And in VB.NET linq queries, as the vb.net compiler then creates the proper expression tree.
Thanks, now it works as expected.
Forgive my ignorance , but is "And" supposed to work, and it doesn't? Or is this a documented "feature"? And if it's a feature, it's a feature of what? LINQ? Vb.Net? LLBLGen?
I've not been able to fine anything useful about VB.Net, LINQ, and "And vs AndAlso" usage.
Thank you very much, Massimiliano
Joined: 17-Aug-2003
The expression tree looks the same with AndAlso in VB.NET and '&&' in C#. If you use And, it will cause a bitwise and operation, not an 'And' between two predicates. Hence the requirement to use AndAlso, because the linq provider doesnt know if the language used was VB.NET or C#.
Joined: 14-Jul-2006
Otis wrote:
The expression tree looks the same with AndAlso in VB.NET and '&&' in C#. If you use And, it will cause a bitwise and operation, not an 'And' between two predicates. Hence the requirement to use AndAlso, because the linq provider doesnt know if the language used was VB.NET or C#.
Thanks for the explanation.