LINQ Query for an old ADO.NET Query

Question

I am not used to writing JOIN statements using LINQ. I am trying to convert this old query into LINQ Lambda syntax and struggling to understand how to go about it. Any help would be greatly appreciated.

SELECT p.id, p.code, u.email, b.date
FROM 
Patients p
LEFT JOIN Users u
ON u.patientid =p.id and u.id=p.primarycontactid
INNER JOIN BillingSettings b ON b.PracticeId = p.id and b.isDue=1
WHERE p.status=1
AND p.appointmentdate > now() and p.appointmentdate < now() + 90
and b.expirationdate < now()
order by p.id

Answer 1

I wrote this without compiler so it can be wrong but generally your answer is below.

var now = DateTime.Now();

var query = 
    from p in db.Patients 
    join u in db.Users on new { patientid = p.id, p.primarycontactid} equals { u.patientid, primarycontactid = u.id}
    from u in userPatient.DefaultIfEmpty()
    join b in db.BillingSettings on b.PracticeId equals p.id
    where p.status == 1 &&
        p.appointmentdate > now && p.appointmentdate < now.AddDays(90) &&
        (b == null || (b.isDue == 1 && b.expirationdate < now))
    orderby p.id
    select new
    {
        p.id,
        p.code,
        email = u.email
        date = b.date 
    };