LINQ Lambda query with JOIN and WHERE 'not in'

Question

I am trying to create a Lambda query of my database searching for students that have taken classes but have not paid for their classes. I am passing in a StudentID (datatype string) and searching the CLASS and PAYMENT tables looking for COUNT of the records in the CLASS table that do NOT have a matching record in the PAYMENT table.

CLASS table has a StudentID field and a ClassID field (datatype GUID) PAYMENT table has a ClassID field (datatype GUID).

My new to LINQ and Lambda and haven't gotten very far as I keep getting syntax errors. Help would be sincerely appreciated.

int unPaidClasses =
                db.CLASS
                .Join(db.PAYMENT,
                class => class.ClassID,
                pay => pay.ClassId,
                (class, pay) => new { CLASS = class, PAYMENT = payment })
                .Where(x =>

You might be getting syntax errors because `class` is a keyword. — Jacob, Apr 11 '18 at 23:48
`as I keep getting syntax errors.` What are the syntax errors? — mjwills, Apr 12 '18 at 00:29
Using keywords is bad practice but if you really must do it then precede it with the @ symbol ```@class```. However; I suggest using a different name in your code that makes sense; i.e. ```payClass``` possibly. — Michael Puckett II, Apr 12 '18 at 02:14

score 0 · Answer 1 · answered Apr 11 '18 at 23:51

Something like this might work (making assumptions on which properties are available):

var unpaidClasses = db.CLASS.Where(cls => 
    cls.StudentID == studentID 
    && !db.PAYMENT.Any(pmt => 
        pmt.StudentID == studenID 
        && pmt.ClassID = cls.ClassID));

That should give you an IQueryable<CLASS> for anything without any matching payment records, assuming that payments are also associated with students.

score 0 · Answer 2 · answered Apr 11 '18 at 23:52

Here are a few pointers to help you resolve the issue independently:

class is a reserved word. Do not use it to name variables. Use cls or some other valid identifier instead.
You need left Join, because you are looking for classes where the payment record is missing. Here is how it is done using fluent syntax; note DefaultIfEmpty() call.
Where condition should check that PAYMENT is null.
Do not use all-caps for property names (Class instead of CLASS, Payment instead of PAYMENT). See Microsoft Naming Guidelines.

LINQ Lambda query with JOIN and WHERE 'not in'

2 Answers2