strange generated SQL causing runtime error

Posts   
 
    
omar avatar
omar
User
Posts: 569
Joined: 15-Oct-2004
# 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(sunglasses 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
daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# 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 Support Team
omar avatar
omar
User
Posts: 569
Joined: 15-Oct-2004
# Posted on: 22-Apr-2011 12:03:14   

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


      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.

Attachments
Filename File size Added on Approval
MUPS.zip 19,081 22-Apr-2011 12:03.32 Approved
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 22-Apr-2011 13:20:54   

You have just over looked it simple_smile

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

omar avatar
omar
User
Posts: 569
Joined: 15-Oct-2004
# Posted on: 22-Apr-2011 15:25:13   

Thanks Walaa. I dont know how I over looked that one flushed