I have a simple (and probably common) set of tables
User
UserRole
Role
Linked up like so:
User.UserId > UserRole.UserId
UserRole.RoleId > Role.RoleId
This means I get a prefetch from UserEntity called PrefetchPathRoleCollectionViaUserRole. If I use this prefetch I get:
**The multi-part identifier "ToxicBeats.dbo.User.UserId" could not be bound. **
Infact I get a similar error if i user ANY "Via" type prefetch mapped on the many to many pattern.
Here is the offending generated SQL:
exec sp_executesql N'SELECT DISTINCT [ToxicBeats].[dbo].[User].[UserId] AS [UserId0], [ToxicBeats].[dbo].[Role].[RoleId] AS [RoleId1] FROM (( [ToxicBeats].[dbo].[User] [LPA_U1] INNER JOIN [ToxicBeats].[dbo].[UserRole]
[LPA_U2] ON [LPA_U1].[UserId]=[LPA_U2].[UserId]) INNER JOIN [ToxicBeats].[dbo].[Role] ON [ToxicBeats].[dbo].[Role].[RoleId]=[LPA_U2].[RoleId]) WHERE ( ( ( ( [LPA_U2].[UserId] = @UserId1))))',N'@UserId1
int',@UserId1=1
So the User table is assigned an alias and then that alias is not being used in the select list.
Here is what it should look like (well... this runs exception free):
exec sp_executesql N'SELECT DISTINCT [LPA_U1] .[UserId] AS [UserId0], [ToxicBeats].[dbo].[Role].[RoleId] AS [RoleId1] FROM (( [ToxicBeats].[dbo].[User] [LPA_U1] INNER JOIN [ToxicBeats].[dbo].[UserRole]
[LPA_U2] ON [LPA_U1].[UserId]=[LPA_U2].[UserId]) INNER JOIN [ToxicBeats].[dbo].[Role] ON [ToxicBeats].[dbo].[Role].[RoleId]=[LPA_U2].[RoleId]) WHERE ( ( ( ( [LPA_U2].[UserId] = @UserId1))))',N'@UserId1
int',@UserId1=1
Strangely, a member of my team says he is not having the same issue (same db, different instance) in his environment.
Any ideas?
Heres the complete codeblock that is throwing the error:
PrefetchPath2 path = new PrefetchPath2(EntityType.UserEntity);
path.Add(UserEntity.PrefetchPathRoleCollectionViaUserRole);
//To get user's artist information.
path.Add(UserEntity.PrefetchPathArtistUsingUserId);
UserEntity user = new UserEntity();
user.Username = Username.Text.Trim();
using (DataAccessAdapter adapter = new DataAccessAdapter())
{
PredicateExpression predicate = new PredicateExpression();
predicate.Add(user.ConstructFilterForUCUsername());
predicate.AddWithAnd(UserFields.IsActive == true);
adapter.FetchEntityUsingUniqueConstraint(user, predicate, path);//exception thrown here
}
