How to write Linq query from Sql query-CodePudding

Can anyone please guide me on how I can write the LINQ for below SQL query

DECLARE @now DATETIME = dbo.GetInstanceDate(NULL);          
select * 
FROM [dbo].[creatives] AS [t0]
INNER JOIN [dbo].[contracts] AS [t1] ON [t0].[campaign_id] = [t1].[campaign_id]
LEFT OUTER JOIN [dbo].[vouchers] AS [t2] ON ([t1].[contract_id] = ([t2].[contract_id])) AND t0.creative_id = t2.creative_id
    AND ([t2].[contract_id] = 29980) 
    AND (NOT ([t2].[removed] = 1)) 
    AND ([t2].[active] = 1) 
    AND ((NOT ([t2].[start_date] IS NOT NULL)) OR (([t2].[start_date]) <= @now)) AND ((NOT ([t2].[expiration_date] IS NOT NULL)) OR (([t2].[expiration_date]) > @now)) 
    AND (NOT ([t2].[creative_id] IS NOT NULL))
where [t1].contract_id = 29980

CodePudding user response：

If you are looking for how to convert LEFT JOIN with complex filter there is simple way:

var query = 
   from t in context.Table
   from o in context.OtherTable
      .Where(o => o.id == t.Id && (o.Some == t.Some || o.Another == t.Another))
      .DefaultIfEmpty()
   select new { t, o };