0

i'm a junior developer and trying to convert the following linq statement to T-Sql:

    var results = context
      .Employees
      .Where(item => (dateFilter.ToUpper() == "P") ? item.PublishedDate >= From : item.CapturedDate >= dateFromDT)
      .Where(item => (dateFilter.ToUpper() == "P") ? item.PublishedDate <= dateTo : item.CapturedDate <= dateTo)
      .FirstOrDefault()

Can one please help me?

Select * from Employees where PublishedDate = From and .......
Clifford
  • 105
  • 1
  • 1
  • 9
  • 1
    If that's working code already, why not just log what SQL it used? – Jon Skeet May 04 '17 at 08:59
  • 1
    i'm actually converting c# api to sql store procedure so i want to convert i'm not quite sure of my where statement – Clifford May 04 '17 at 09:01
  • Possible duplicate of [how to convert linq query to sql](http://stackoverflow.com/questions/6190620/how-to-convert-linq-query-to-sql) – Fabiano May 04 '17 at 09:10
  • Another possible duplicate: [How to view LINQ generated SQL statements](http://stackoverflow.com/questions/4899974/how-to-view-linq-generated-sql-statements). I like the LinqPad suggestion, great tool. – David Ching May 04 '17 at 09:18

2 Answers2

0

I think it should be like this.

 Select Top 1 * from Employees 
     where 
(dateFilter = "P" COLLATE Latin1_General_CS_AS  AND PublishedDate >= From AND PublishedDate >=dateTo) 
OR 
(dateFilter != "P" COLLATE Latin1_General_CS_AS AND CapturedDate  >= dateFromDT AND CapturedDate  >= dateTo)
Amit Kumar
  • 5,888
  • 11
  • 47
  • 85
0

You could use something similar to this:

select top 1 * 
  from employees 
 where (upper(DateFilter) = 'P' and PublishedDate >= From and PublishedDate <= dateTo)
    or (upper(DateFilter) <> 'P' and CapturedDate >= dateFromDT and CapturedDate <= dateTo)

if DateFilter is a column in the table then it is better to skip the upper as it can degrade performance (given that you have a index on that column).

Draupnir Guðmundsson
  • 16
  • 1