Filter by column from joined table

Question

I've recently started using Simple.Data as my ORM. i've approached a problem with filtering by column from joined table.

My query looks like this:

var candidates = db.dbo.Candidates
            .FindAllBy(CommonOfferId: commonOfferId, CandidateId: null)
            .Select(
                db.dbo.Candidates.Id,
                db.dbo.Candidates.Email,
                db.dbo.CandidateProfiles.CandidateId
            ).LeftJoin(db.dbo.CandidateProfiles).On(db.dbo.Candidates.Id == db.dbo.CandidateProfiles.CandidateId);

I want to select entities which don't have ids in another table. This code is not working. I'm getting an exception, that Candidates doesn't have column CandidateId.

Is there any way to filter this query by value from joined table?

Answer 1

It is almost correct:

dbo.Candidates
        .FindAllBy(CommonOfferId: commonOfferId, CandidateProfiles.CandidateId: null)
        .Select(
            db.dbo.Candidates.Id,
            db.dbo.Candidates.Email,
            db.dbo.CandidateProfiles.CandidateId
        ).LeftJoin(db.dbo.CandidateProfiles).On(db.dbo.Candidates.Id == db.dbo.CandidateProfiles.CandidateId);

Answer 2

If you have foreign keys defined in the database between Candidates and CandidateProfiles tables and it's a left join, I guess you could just write (not tested):

db.dbo.Candidates
   .FindAllBy(CommonOfferId: commonOfferId)
   .Where(db.dbo.Candidates.CandidateProfiles.CandidateId==null)
    .Select(
        db.dbo.Candidates.Id,
        db.dbo.Candidates.Email);

The main missing part in your code was the .Candidates in front of .CandidateProfiles. That's how you express left joins in Simple.Data and you can join multiple levels that way. It's very expressive and IMHO it's one of Simple.Data's best features.