HnD | Load and filter related entity generate ORMQueryExecutionException for missing parameter, Page 1

Load and filter related entity generate ORMQueryExecutionException for missing parameter

Posts

Max
User

Posts: 221
Joined: 14-Jul-2006

# Posted on: 18-Nov-2010 17:58:00

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

Otis
LLBLGen Pro Team

Posts: 39614
Joined: 17-Aug-2003

# Posted on: 18-Nov-2010 18:29:03

Use AndAlso, instead of And in VB.NET linq queries, as the vb.net compiler then creates the proper expression tree.

Frans Bouma | Lead developer LLBLGen Pro

Max
User

Posts: 221
Joined: 14-Jul-2006

# Posted on: 19-Nov-2010 10:45:45

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

Otis
LLBLGen Pro Team

Posts: 39614
Joined: 17-Aug-2003

# Posted on: 19-Nov-2010 13:37:00

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#.

Frans Bouma | Lead developer LLBLGen Pro

Max
User

Posts: 221
Joined: 14-Jul-2006

# Posted on: 19-Nov-2010 13:57:20

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.