How to write Linq query from Sql query

Question

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

What have you tried already? Is there any specific part that you are struggling with? — Fermin, Feb 17 '22 at 15:38
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Feb 17 '22 at 16:08
A good start would be to find an O/R mapper that supports LINQ and create a class model that maps to the database. — Gert Arnold, Feb 18 '22 at 10:48

score 0 · Accepted Answer · answered Feb 17 '22 at 18:21

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 };

How to write Linq query from Sql query

1 Answers1