Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Bad Query being generated by LLBLGen (missing Alias)
 

Pages: 1
Bugs & Issues
Bad Query being generated by LLBLGen (missing Alias)
Page:1/1 

  Print all messages in this thread  
Poster Message
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# Posted on: 28-Mar-2008 22:01:21.  
LLBLGen v2.5 Final (February 4th, 2008 )
Runtime Version: 2.5.08.0228
Compiling for C# Adapter Pattern targeting .NET 3
Running off SQL Server 2005


Getting the below exception in production but not when executing locally when making adapter calls from a WCF Service. In development, the WCF Service runs under ASP.NET Development Server, in Production, on a remote machine under IIS. The web.configs for the dev and production WCF Services have the same settings as far as connection strings and LLBLGen settings go.

For testing purposes both services are calling the same Database (Production) with the same connection string. The schema of the Production Database is identical to that of the Development Database.

Under development, when fetching data for a m:n prefetch path this query is generated:

Code:
exec sp_executesql N'SELECT DISTINCT [LPA_P1].[Id] AS [Id0], [dbo].[Price].[Id] AS [Id1] FROM (( [dbo].[PriceGroup] [LPA_P1] INNER JOIN [dbo].[PriceGroupPrice] [LPA_P2] ON [LPA_P1].[Id]=[LPA_P2].[PriceGroupId]) INNER JOIN [dbo].[Price] ON [dbo].[Price].[Id]=[LPA_P2].[PriceId]) WHERE ( ( [LPA_P2].[PriceGroupId] IN (SELECT [dbo].[PriceGroup].[Id] FROM ( [dbo].[ItemGroup] INNER JOIN [dbo].[PriceGroup] ON [dbo].[ItemGroup].[Id]=[dbo].[PriceGroup].[ItemGroupId]) WHERE ( ( ( [dbo].[PriceGroup].[IsActive] = @IsActive1 AND [dbo].[PriceGroup].[BaanCompanyId] = @BaanCompanyId2 AND [dbo].[ItemGroup].[ManagerId] = @ManagerId3))))))',N'@IsActive1
bit,@BaanCompanyId2 int,@ManagerId3 int',@IsActive1=1,@BaanCompanyId2=1,@ManagerId3=21



Which is all as it should be.

However, in production a very slightly different query is generated for the same m:n fetch:

Code:
exec sp_executesql N'SELECT DISTINCT [dbo].[PriceGroup].[Id] AS [Id0], [dbo].[Price].[Id] AS [Id1] FROM (( [dbo].[PriceGroup] [LPA_P1]
INNER JOIN [dbo].[PriceGroupPrice] [LPA_P2] ON [LPA_P1].[Id]=[LPA_P2].[PriceGroupId]) INNER JOIN [dbo].[Price] ON
[dbo].[Price].[Id]=[LPA_P2].[PriceId]) WHERE ( ( [LPA_P2].[PriceGroupId] IN (SELECT [dbo].[PriceGroup].[Id] FROM ( [dbo].[ItemGroup]
INNER JOIN [dbo].[PriceGroup] ON [dbo].[ItemGroup].[Id]=[dbo].[PriceGroup].[ItemGroupId]) WHERE ( ( ( [dbo].[PriceGroup].[IsActive] =
@IsActive1 AND [dbo].[PriceGroup].[BaanCompanyId] = @BaanCompanyId2 AND [dbo].[ItemGroup].[ManagerId] = @ManagerId3))))))',N'@IsActive1
bit,@BaanCompanyId2 int,@ManagerId3 int',@IsActive1=1,@BaanCompanyId2=1,@ManagerId3=2


Notice that in the query generated on production the [LPA_P1] alias for the Price Group table is not used, as it should be, hence we get the following exception:

Event Type:    Error
Event Source:    Univar Q Error Logging
Event Category:    None
Event ID:    100
Date:        28/03/2008
Time:        16:23:30
User:        N/A
Computer:    UKI-PRICING01
Description:
Timestamp: 28/03/2008 16:23:30
Message: HandlingInstanceID: d4903255-d8e6-42d4-8066-dc0b7c5da3f6
An exception of type 'System.Data.SqlClient.SqlException' occurred and was caught.
----------------------------------------------------------------------------------
03/28/2008 16:23:30
Type : System.Data.SqlClient.SqlException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message : The multi-part identifier "UnivarQ.dbo.PriceGroup.Id" could not be bound.
Source : .Net SqlClient Data Provider
Help link :
Errors : System.Data.SqlClient.SqlErrorCollection
Class : 16
LineNumber : 1
Number : 4104
Procedure :
Server : 10.2.50.101\UKI_SQL2_DEV2
State : 1
ErrorCode : -2146232060
Data : System.Collections.ListDictionaryInternal
TargetSite : Void OnError(System.Data.SqlClient.SqlException, Boolean)
Stack Trace :    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchTypedList(IEntityFields2 fieldCollectionToFetch, DataTable dataTableToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, Boolean allowDuplicates, IGroupByCollection groupByClause, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.MergeManyToMany(IPrefetchPathElement2 currentElement, IRelationPredicateBucket elementFilter, Int64 maxNumberOfItemsToReturn, IEntityCollection2 rootEntities)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchPrefetchPath(IEntityCollection2 rootEntities, IRelationPredicateBucket filterBucket, Int64 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, Boolean forceParameterizedPPath)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields)
at UnivarQ.DataGateway.PriceGroupWorker.SearchForPriceGroups(String itemGroupName, String priceGroupName, String skuDesc, String skuCode, Nullable`1 managerId, Nullable`1 baanCompanyId, Nullable`1 priceUnitId, Boolean includePrices, Boolean useExactPhrase) in C:\Projects\UnivarQ\Current\Product\Production\Facade\DataGateway\PriceGroupWorker.cs:line 220
at UnivarQ.QService.UnivarQService.SearchForPriceGroups(String itemGroupName, String priceGroupName, String skuDesc, String skuCode, Nullable`1 managerId, Nullable`1 baanCompanyId, Nullable`1 priceUnitId, Boolean includePrices, Boolean useExactPhrase) in C:\Projects\UnivarQ\Current\Product\Production\Services\QService\UnivarQService.cs:line 808
at SyncInvokeSearchForPriceGroups(Object , Object[] , Object[] )
at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage4(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage3(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage1(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)

Additional Info:

MachineName : UKI-PRICING01
TimeStamp : 28/03/2008 16:23:30
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
AppDomainName : /LM/W3SVC/1/Root/UnivarQServices-1-128511939806115858
ThreadIdentity :
WindowsIdentity : NT AUTHORITY\NETWORK SERVICE

Category: General
Priority: 0
EventId: 100
Severity: Error
Title:Exception Management Application Exception
Machine: UKI-PRICING01
Application Domain: /LM/W3SVC/1/Root/UnivarQServices-1-128511939806115858
Process Id: 16372
Process Name: c:\windows\system32\inetsrv\w3wp.exe
Win32 Thread Id: 15388
Thread Name:
Extended Properties: HelpLink.ProdName - Microsoft SQL Server
HelpLink.ProdVer - 09.00.2047
HelpLink.EvtSrc - MSSQLServer
HelpLink.EvtID - 4104
HelpLink.LinkId - 20476


The same problem occurs on other m:n fetches carried out when m:n prefetch paths feature in the code.

We're really pulling our hair out over this one, please help. We've tried reverting to 2.5.07.1129 runtime library versions, as the problem started after we moved to using the above version of LLBLGen, but no luck, same exception.

Also, we've tried using it with and without catalog names specified, makes no difference.

  Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
38092 posts
# Posted on: 28-Mar-2008 22:41:05.  
If you get a different query on another machine, you have a different runtime version there or something else is different.

Anyway, we applied some fixes to the runtime library for m:n relation fixes for inheritance entities on february 28th. This requires new code being generated with the latest templates.

Please do the following:
- be sure you use the latest templates. If you're not using templates released on february 28th, you have wrong aliases setup in the prefetch path code
- be sure you use a runtime library released on or after february 28th.

So please regenerate teh code using the latest runtime lib and templates and deploy BOTH on the production server. If you just deploy the runtime lib update on the production server, you'll likely run into the bug we fixed on february 28th, as it still has the wrong aliases.

So it might be you did all this except deploying an updated compiled version of the generated code as it works in the development environment.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
saggett
User



Location:
Manchester, UK
Joined on:
12-Nov-2007 15:44:46
Posted:
50 posts
# Posted on: 29-Mar-2008 01:44:13.  
Well, I did I diskwide search for llblgen assemblies and found some old 2.5 runtime libraries in the GAC. It looks like the web service was using those in preference to the ones in its /bin directory, producing a mismatch between runtime libraries and generated code that resulted in the bad query.

Took a while to figure out though. All the while I was assuming that if the correct version was in the /bin directory of the service, that was the version that would be used at execution.
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.