Convert this SQL with left join to LINQ

Question

There are two tables, school and term. The school record must be shown, but the term record may not yet exist, therefore, the term may be null (thus the left join). The left joined table must be filtered by date for the current term if it exists. Can this be done in LINQ?

select school.school_name, term.term_start, term.term_end 
from school
left join term on school.school_id = term.school_id and term.term_start <= '2017-10-21' and term.term_end >= '2017-10-21'
where school.active = 1 
order by school.school_name

UPDATE:

After some input I have a left join but if a school is missing a term I still cannot make the start and end dates show as null - the school doesn't show at all if I am missing a term, and I want the school to show in the first column. What am I missing?? Here is the latest LinqPad code.

var query =  ((from sc in Schools.Where(s => s.Active == 1 )
             join t in Terms on sc.School_id equals t.School_id into ts
             from tsub in ts.DefaultIfEmpty()
             select new {name = sc.School_name, 
                         start = tsub.Term_start,
                         end = tsub.Term_end})
             .Where (o => o.start <= DateTime.Now && o.end >= DateTime.Now))
             .OrderBy( o => o.name);

query.Dump();

UPDATE #2

Here is a screen shot of the SQL result, and I am trying to achieve the same thing in LINQ:

Answer 1

I finally figured it out. If you put the .Where() clause on the joined table you will get null values if there is no matching record. Here is the LinqPad LINQ statement that works and it runs perfectly in .NET MVC.

var query =  ((from sc in Schools.Where(s => s.Active == 1 )
             join t in Terms.Where(x => x.Term_start <= DateTime.Now && x.Term_end >= DateTime.Now) on sc.School_id equals t.School_id into ts
             from tsub in ts.DefaultIfEmpty()
             select new {name = sc.School_name, 
                         start = tsub.Term_start,
                         end = tsub.Term_end})
             .OrderBy( o => o.name));

query.Dump();