Help to convert some Linq to SQL syntax

Question

I have a website which i am attempting to convert some of the logic from Linq to SQL to SQL.

The code i am trying to convert to SQL is

var data = (from od in OrderDetails
               join os in OrderStrTypes on od.osTypeId.Value equals os.osTypeId
               where od.ostrId == 1
               && od.osTypeId != null
               && od.Active != null && od.Active.Value
               && (od.StartDate == null || DateTime.Now.Date >= od.StartDate.Value)
               && (od.EndDate == null || DateTime.Now.Date <= od.EndDate.Value)
               select os).First();                                                                                                                                                                                                                                        

The lines I am finding difficult to convert are

&& (od.StartDate == null || DateTime.Now.Date >= od.StartDate.Value)               
&& (od.EndDate == null || DateTime.Now.Date <= od.EndDate.Value)

I first tried with LinqPad but keep getting an error about operands cannot be applied or DateOnly could not be found.

When i tried to do it manually using the Query editor in SQL i cant get the syntax correct.

Could anyone help convert those two lines to the SQL equivalent syntax.

If there are other tools to help me do this more efficiently or how i could setup linqPad to accept datetimes please do advise.

Answer 1

Try something like this:

select top(1) os.*
from OrderDetails od
inner join OrderStrTypes os on os.osTypeId = od.osTypeId
where od.ostrId = 1
and od.Active = 1
and ( od.StartDate is null or getdate() >= od.StartDate )
and ( od.EndDate is null or cast(getdate() as date) <= od.EndDate )

If there are several rows, then add an ORDER BY.