Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> strange generated SQL causing runtime error
 

Pages: 1
LLBLGen Pro Runtime Framework
strange generated SQL causing runtime error
Page:1/1 

  Print all messages in this thread  
Poster Message
omar
User



Location:
Salmiya, Kuwait
Joined on:
15-Oct-2004 15:44:57
Posted:
569 posts
# Posted on: 22-Apr-2011 01:04:06.  
First, the usual background
VS2010, .NET4.0, LLBLGen 3.1

Suddenly I am encountering the following runt-time error when trying to fill an entity collection from the DB:
"The multi-part identifier "dbo.Distributor.ID" could not be bound"

I turned on the verbose LLBL tracing to check the resulting query. The entity in question is a view (VnDistributorInfo) that I mapped to an entity VnDistributorInfoEntity.

The strange thing about LLBL's generated SQL is the field it is referencing in the WHERE clause which is causing the run time exception (dbo.Distributor.ID).
In my original view I do have a column ID from the table Distrubutor but I dont know why LLBL's generated SQL is referencing the column name this way ?

(VnDistributorInfo) view in the DB
=========================
SELECT     dbo.Distributor.ID, dbo.Distributor.DistributorName, dbo.CODEDistributorType.Reference AS DistributorType, dbo.Distributor.Salary, dbo.Distributor.Phone1,
                     dbo.Distributor.Phone2, dbo.Distributor.Address, Nationality.Nationality, dbo.Distributor.IsSpecial, dbo.Distributor.BirthDate, dbo.Distributor.BirthPlace,
                     dbo.CODEMaritalStatus.Description AS MaritalStatus, Nationality2.Nationality AS PassportNationality, dbo.Distributor.PassportNr, dbo.Distributor.PassportExpiry,
                     dbo.Distributor.ResidencyNr, dbo.Distributor.ResidencyExpiry, dbo.Distributor.CivilIdNr, dbo.Distributor.CivilIdExpiry, dbo.Distributor.DrivingLicenseNr,
                     dbo.CODEDrivingLicenseType.Description AS DrivingLicenseType, dbo.Distributor.DrivingLicenseExpiry, dbo.Distributor.Education, dbo.Distributor.CurrentJob,
                     dbo.Distributor.CurrentEmployer, dbo.CODEWorkShiftType.Description AS WorkShiftType, dbo.Distributor.EditVer, dbo.Distributor.EditUserName,
                     dbo.Distributor.EditDate, dbo.Distributor.EditWorkstation, dbo.Distributor.StatusID, dbo.CODEDistributorStatus.Description AS StatusDescription,
                     dbo.Distributor.StatusDate, dbo.Distributor.AppointedDistributionLineID, dbo.DistributionLine.DistributionLineName AS AppointedDistributionLineName,
                     dbo.CODEArea.Area AS AppointedArea, dbo.Distributor.ReplacedDistributorId, Distributor_2.DistributorName AS ReplacedDistributorName
FROM         dbo.CODEWorkShiftType RIGHT OUTER JOIN
                     dbo.Distributor LEFT OUTER JOIN
                     dbo.Distributor AS Distributor_2 ON Distributor_2.ID = dbo.Distributor.ReplacedDistributorId LEFT OUTER JOIN
                     dbo.CODEArea INNER JOIN
                     dbo.DistributionLine ON dbo.CODEArea.ID = dbo.DistributionLine.AreaID ON dbo.Distributor.AppointedDistributionLineID = dbo.DistributionLine.ID LEFT OUTER JOIN
                     dbo.CODEDistributorStatus ON dbo.Distributor.StatusID = dbo.CODEDistributorStatus.ID ON
                     dbo.CODEWorkShiftType.ID = dbo.Distributor.WorkShiftType LEFT OUTER JOIN
                     dbo.CODEDrivingLicenseType ON dbo.Distributor.DrivingLicenseType = dbo.CODEDrivingLicenseType.ID LEFT OUTER JOIN
                     dbo.CODEMaritalStatus ON dbo.Distributor.MaritalStatus = dbo.CODEMaritalStatus.ID LEFT OUTER JOIN
                     dbo.CODENationality AS Nationality2 ON dbo.Distributor.PassportNationalityId = Nationality2.ID LEFT OUTER JOIN
                     dbo.CODENationality AS Nationality ON dbo.Distributor.NationalityId = Nationality.ID LEFT OUTER JOIN
                     dbo.CODEDistributorType ON dbo.Distributor.DistributorType = dbo.CODEDistributorType.ID



LLBL's debugging output
==================

Method Enter: DataAccessAdapterBase.FetchEntityCollection(Cool
Method Enter: DataAccessAdapterBase.FetchEntityCollectionInternal(7)
Active Entity Collection Description:
    EntityCollection: MUPS.DAL.HelperClasses.EntityCollection`1[[MUPS.DAL.EntityClasses.VnDistributorInfoEntity, MUPS.DAL, Version=1.0.4129.2506, Culture=neutral, PublicKeyToken=null]].    Will contain entities of type: VnDistributorInfoEntity

Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query:
    Query: SELECT [dbo].[VnDistributorInfo].[Address], [dbo].[VnDistributorInfo].[AppointedArea], [dbo].[VnDistributorInfo].[AppointedDistributionLineID], [dbo].[VnDistributorInfo].[AppointedDistributionLineName], [dbo].[VnDistributorInfo].[BirthDate], [dbo].[VnDistributorInfo].[BirthPlace], [dbo].[VnDistributorInfo].[CivilIdExpiry], [dbo].[VnDistributorInfo].[CivilIdNr], [dbo].[VnDistributorInfo].[CurrentEmployer], [dbo].[VnDistributorInfo].[CurrentJob], [dbo].[VnDistributorInfo].[DistributorName], [dbo].[VnDistributorInfo].[DistributorType], [dbo].[VnDistributorInfo].[DrivingLicenseExpiry], [dbo].[VnDistributorInfo].[DrivingLicenseNr], [dbo].[VnDistributorInfo].[DrivingLicenseType], [dbo].[VnDistributorInfo].[EditDate], [dbo].[VnDistributorInfo].[EditUserName], [dbo].[VnDistributorInfo].[EditVer], [dbo].[VnDistributorInfo].[EditWorkstation], [dbo].[VnDistributorInfo].[Education], [dbo].[VnDistributorInfo].[ID], [dbo].[VnDistributorInfo].[IsSpecial], [dbo].[VnDistributorInfo].[MaritalStatus], [dbo].[VnDistributorInfo].[Nationality], [dbo].[VnDistributorInfo].[PassportExpiry], [dbo].[VnDistributorInfo].[PassportNationality], [dbo].[VnDistributorInfo].[PassportNr], [dbo].[VnDistributorInfo].[Phone1], [dbo].[VnDistributorInfo].[Phone2], [dbo].[VnDistributorInfo].[ReplacedDistributorId], [dbo].[VnDistributorInfo].[ReplacedDistributorName], [dbo].[VnDistributorInfo].[ResidencyExpiry], [dbo].[VnDistributorInfo].[ResidencyNr], [dbo].[VnDistributorInfo].[Salary], [dbo].[VnDistributorInfo].[StatusDate], [dbo].[VnDistributorInfo].[StatusDescription], [dbo].[VnDistributorInfo].[StatusID], [dbo].[VnDistributorInfo].[WorkShiftType] FROM [dbo].[VnDistributorInfo] WHERE ( ( [dbo].[Distributor].[ID] = @p1))
    Parameter: @p1 : Guid. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: ffbebf2c-bc9b-46c4-8fcc-0e394155d362.
Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
Method Enter: DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery
Method Enter: DataAccessAdapterBase.OpenConnection
: Connection physically opened.
Method Exit: DataAccessAdapterBase.OpenConnection
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
A first chance exception of type 'SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException' occurred in SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll
We don't know who we are until we see what we can do  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8098 posts
# Posted on: 22-Apr-2011 06:30:54.  
Hi Omar,

Please post this:
- LLBLGen Pro runtime library version
- The code snipped that generates this exception (e.g. the code you are using to fill the collection). I know this is trivial, but maybe you are using some special filter or relations.
- Is there any special in that view (mapped onto entity)? For example: Does it belongs to an inheritance hierarchy?
- If possible, attach your .llblgenproj file so we can take a look at your view. Definitely I can't reproduce that with my views, so we have to look at your side. If even possible, attach a repro case solution we can take a look at.


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
omar
User



Location:
Salmiya, Kuwait
Joined on:
15-Oct-2004 15:44:57
Posted:
569 posts
# Posted on: 22-Apr-2011 12:03:14. Goto attachments  
Thank you daelmo. Your requested information is as follows:

- LLBLGen Pro runtime library version
properties for SD.LLBLGen.Pro.ORMSupportClasses.NET20 dll shows version 3.1.0.0


- The code snipped that generates this exception (e.g. the code you are using to fill the collection). I know this is trivial, but maybe you are using some special filter or relations.


Following are the sequance of functions that are called. The exception is thrown by the last function that calls the adapter.FetchEntityCollection

Code:

     public static VnDistributorInfoEntity GetDistributorInfo(Guid distributorId)
     {
         EntityCollection<VnDistributorInfoEntity> col = new EntityCollection<VnDistributorInfoEntity>();
         IRelationPredicateBucket filter = new RelationPredicateBucket();
         filter.PredicateExpression.Add(DistributorFields.ID == distributorId);

         FillDistributorInfo(col, filter);
         if (col.Count > 0)
            return col[0];
         else
            return null;
     }

     public static void FillDistributorInfo(EntityCollection<VnDistributorInfoEntity> colToFill, IRelationPredicateBucket filter)
     {
         DataAccess.DataBase.Fill(colToFill, filter);
     }

     internal static void Fill(IEntityCollection2 toFill, IRelationPredicateBucket filter)
     {
         Fill(toFill, filter, 0, null, null, null, 0, 0);
     }

     internal static void Fill(
         IEntityCollection2 toFill,
         IRelationPredicateBucket filter,
         int maxNumOfItemsToReturn,
         ISortExpression sorter,
         IPrefetchPath2 prefetch,
         ExcludeIncludeFieldsList execludedFields,
         int pageNumber,
         int pageSize)
     {
         if (toFill != null)
            using (var adapter = DataAdapterFactory.Create())
            {
             adapter.FetchEntityCollection(toFill, filter, maxNumOfItemsToReturn, sorter, prefetch, execludedFields, pageNumber, pageSize);
            }
     }



- Is there any special in that view (mapped onto entity)? For example: Does it belongs to an inheritance hierarchy?

The view is plain vanilla SQL view and I have posted its SQL in my first message in this thread. The view is mapped onto an Entity. My project is not using any inheritance hierarchy.


- If possible, attach your .llblgenproj file so we can take a look at your view. Definitely I can't reproduce that with my views, so we have to look at your side. If even possible, attach a repro case solution we can take a look at.

I have attached the llblproj file. The code I am using is simply directly calling the function (GetDistributorInfo) shown in the code above and passing the guid ID of the Distributor entity I wish to retrieve from the DB.
We don't know who we are until we see what we can do  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14569 posts
# Posted on: 22-Apr-2011 13:20:54.  
You have just over looked it Regular Smiley

Quote:
EntityCollection<VnDistributorInfoEntity> col = new EntityCollection<VnDistributorInfoEntity>();
         IRelationPredicateBucket filter = new RelationPredicateBucket();
         filter.PredicateExpression.Add(DistributorFields.ID == distributorId);

You are using the field from the table-Entity in your filtering, not the field of the View-Entity ?

I guess it should have been: VnDistributorInfoFields.Id


  Top
omar
User



Location:
Salmiya, Kuwait
Joined on:
15-Oct-2004 15:44:57
Posted:
569 posts
# Posted on: 22-Apr-2011 15:25:13.  
Thanks Walaa. I dont know how I over looked that one Embarrassed
We don't know who we are until we see what we can do  Top
Pages: 1  


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

Version: 2.1.12172008 Final.