0

How would I convert this SQL query to Linq?

Declare @Date Date 
select @Date = Max (InsertedDate) from Rates

SELECT * FROM Rates where CAST(InsertedDate AS Date) =CAST(@Date AS Date)  ORDER BY InsertedDate DESC
chills42
  • 14,201
  • 3
  • 42
  • 77
Krishna shidnekoppa
  • 1,011
  • 13
  • 20
  • 6
    Can you clarify your question? – mjuarez Apr 17 '15 at 06:47
  • What is it that you are trying to achieve? What have you tried, or what approach do you think you could follow? Where did you get stuck with trying to solve this yourself? – Alex Apr 17 '15 at 06:51
  • The Above SQl query first fetch the Latest Inserted Date first and than it will fetches all the records on that date select @Date = Max (InsertedDate) from Rates ....Here it gets the latest inserted date .....SELECT * FROM Rates where CAST(InsertedDate AS Date) =CAST(@Date AS Date) ORDER BY InsertedDate DESC .......Here it fetches all the records from the latest date ..... I need to get this in LINQ – Krishna shidnekoppa Apr 17 '15 at 07:12

1 Answers1

1

Your Sql Query appears to find all records on the last inserted date. Your Sql is approximately equivalent to:

var lastDate = db.Rates.Max(r => r.InsertedDate).Date;
var allRatesOnTheLastDate = db.Rates.Where(r => r.InsertedDate >= lastDate)
                                    .OrderByDescending(r => r.InsertedDate);

This should be more performant than your Sql, given that you are casting the InsertedDate column which will prevent SARGability of any index on the column.

(Assuming an EF or Linq2Sql context called db with a Rates entity bound to it)

Community
  • 1
  • 1
StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • The Above SQl query first fetch the Latest Inserted Date first and than it will fetches all the records on that date – Krishna shidnekoppa Apr 17 '15 at 07:11
  • 1
    Yes. By definition, (concurrency aside!) there will be no other records on a day after the last inserted date, hence we don't need an upper bound on the query. Try it. Also, please note that StackOverflow protocol requires that the body of a question explain the problem (i.e. don't just paste code), and also that you show your attempt to date (SO is not a code request service). [Recommended Reading](http://stackoverflow.com/help/how-to-ask). If you edit your question with this in mind, the community may reopen your question. – StuartLC Apr 17 '15 at 08:23