EF Core join table with multiple condition with nullable property

Question

I have the following table structures:

public class Delegate {
    public int DelegateId {get;set;}
    public int? NotificationTypeId { get; set; }
    public NotificationType NotificationType { get; set; }
}

    public class TrainingNotification {
        public int TrainingNotificationId {get;set;}
public int NotificationTypeId {get;set;}
        public int DelegateId {get;set;}
        public virtual Delegate Delegate {get;set;}
    }

One to Many between Delegate and TrainingNotification

public class NotificationType {
    public int NotificationTypeId {get;set;}
    public virtual ICollection<Delegate> Delegates {get;set;}
}

Want to retrieve the TrainingNotification for the NotificationTypeId in Delegate.

    var delegates21 = await from tn in _context.TrainingNotification
                            join cd in _context.Delegate on
                            new { tn.DelegateId, tn.NotificationTypeId } equals
                            new { cd.DelegateId, cd.NotificationTypeId } 

but getting error type of one of the expression in the join clause in incorrect

Can anyone help how to fix this?

Here is the test data and expected result:

Delegate:
DelegateId  NotificationTypeId
100             1
8201            2
101             null


TrainginNotification:
TrainignNotificationId  DelegateId  NotificationTypeId
1                           8201        1
2                           8201        2
3                           100         1


NotificationType:
NotificationTypeId      Name
1                       InviteEmail
2                       ReminderEmail

Retrieve users who hasnt got reminder emails:

Result:

DelegateId      
100

Thanks

Answer 1

This may be because you haven't identified the parts of your anonymous type explicitly. Try:

 var delegates21 = await from tn in _context.TrainingNotification
                        join cd in _context.Delegate on
                        new { did = tn.DelegateId, nid = tn.NotificationTypeId } equals
                        new { did = cd.DelegateId, nid = cd.NotificationTypeId } 

Answer 2

Since NotificationTypeId is nullable on the Delegate type but not on the TrainingNotifcation type, you can cast the TrainingNotification.NotificationTypeId as int?:

var delegates21 = await from tn in _context.TrainingNotification
                            join cd in _context.Delegate on
                            new { tn.DelegateId, (int?)tn.NotificationTypeId } equals
                            new { cd.DelegateId, cd.NotificationTypeId }

That should work just fine.