- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
strange generated SQL causing runtime error
Joined: 15-Oct-2004
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( 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
Joined: 28-Nov-2005
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.
Joined: 15-Oct-2004
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.
Filename | File size | Added on | Approval |
---|---|---|---|
MUPS.zip | 19,081 | 22-Apr-2011 12:03.32 | Approved |
Joined: 21-Aug-2005
You have just over looked it
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