Simple.Data ORM. The multi-part identifier could not be bound

Question

I'm using Simple.Data ORM. I'm trying to make a query from two joined tables. This query works fine:

dynamic alias;
var candidatesRec = db.dbo.Candidates
            .FindAll(db.dbo.Candidates.CommonOfferId == commonOfferId 
                        && db.dbo.CandidateProfiles.CandidateId == null)
            .LeftJoin(db.dbo.CandidateProfiles, out alias)
            .On(db.dbo.Candidates.Id == alias.CandidateId)
            .Select(
                db.dbo.Candidates.Id,
                db.dbo.Candidates.Email
            )
            .OrderByDescending(db.dbo.Candidates.ApplicationDate)

But when this line is added:

.Skip((pageNumber - 1) * pageSize)

I'm getting this exception:

The multi-part identifier \"dbo.CandidateProfiles.CandidateId\" could not be bound.

I was trying explicitly pass 0, 1 and few other numbers to Skip but I always get the same exception.

My test query should return 4 elements and I'm skipping 0 elements (it can be more in normal use).

Additional info: CandidateProfiles has foreign key from Candidates and it's CandidateId can be null.

Edit: We've done a workaround for this problem, but I'm really curious why this one won't work. Simple.Data looked fun at first, but now I'm not sure if I will use it in future

Answer 1

Here is an explanation of the SQL error from http://www.sql-server-helper.com/error-messages/msg-4104.aspx:

Another way of getting the error is when an alias has been assigned to a table referenced in the FROM clause of a statement and the table is used as a prefix of a column instead of using the alias.

While this isn't quite what's happening in your case, it's very close. The problem I see with your code sample is in the FindAll call:

        .FindAll(db.dbo.Candidates.CommonOfferId == commonOfferId 
                    && db.dbo.CandidateProfiles.CandidateId == null)
        .LeftJoin(db.dbo.CandidateProfiles, out alias)

Since the FindAll is using the fully qualified name "db.dbo.CandidateProfiles" and every other reference is using the alias defined in the subsequent LeftJoin, the occurrence in the generated where clause doesn't use the alias, and you end up with a mix and match of alias and explicit table references, that SQL doesn't like.

I think the reason that it only occurred in your case after the "Skip" was added, is that very different SQL ended up being generated (my guess is that only after the Skip was added was an alias actually used).

I don't understand the purpose of the CandidateProfiles.CandidateId == null condition (because combined with the condition in the On call I don't see how this would ever return any results), but I would suggest adding restrictions to the joined table in the On call so that it ends up in the on clause instead of the where clause (and then using the alias instead of the fully qualified name)

.LeftJoin(db.dbo.CandidateProfiles, out alias)  
.On(db.dbo.Candidates.Id == alias.CandidateId && alias.CandidateId == null)