How to write linq query from sql query using LINQ?

Question

I have a table with two columns like BookingArrivedEnquiredTime with varchar datatype and datetime BookingArrivedEnquiredDateTime. When I execute this query in SQL Server the result give perfect with time sorted order the sql query will be like

select BookingArrivedEnquiredTime from BookingArriveds where BookingArrivedEnquiredDateTime='2015-02-17 00:00:00.000' 
order by CAST(('01/01/2000 ' + BookingArrivedEnquiredTime) AS DATETIME)

and it gives out put like this

11:27 AM
11:47 AM
11:53 AM
12:13 PM
12:50 PM
02:02 PM
02:47 PM
03:04 PM
03:16 PM

When i try this query into using linq

public ViewResult Index1(DateTime? Startdate) 
{ 
    Startdate = DateTime.Now.Date; 
    var fm = DateTime.Parse("01/01/2000"); 
    var qr = from item in db.BookingArriveds 
             where item.BookingArrivedEnquiredDateTime == Startdate 
             orderby DateTime.Parse("01/01/2000 " +
             item.BookingArrivedEnquiredTime.ToString()) 
             select item; 
     return View(qr);
}

but it gives error like this

LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String)' method, and this method cannot be translated into a store expression.

where is wrong and I need help for how to rewrite above sql query to linq query also casting from varchar to datetime in linq?

You're trying to write a query which will be executed in the SQL but you're using C# methods like DateTime.Parse(), SQL doesn't recognize them. — Omri Aharon
– Omri Aharon, Commented Mar 20, 2015 at 7:52
Why not parse it later? You can order by item.BookingArrivedEnquiredTime anyway since the prefix date that you're using is the same, so ordering without it using only date should provide the same results. — Omri Aharon
– Omri Aharon, Commented Mar 20, 2015 at 7:55
i tried but it gives result like 11pm,10am,7pm,6am it is sorted on number only not sorted based on am/pm — Kamesh Naidu
– Kamesh Naidu, Commented Mar 20, 2015 at 8:01
So................ do you think maybe instead of creating an entirely new DateTime (e.g. DateTime.Parse), you might use the one you already have... — Sam Axe
– Sam Axe, Commented Mar 20, 2015 at 8:07

Ramesh Rajendran · Accepted Answer · 2015-03-20 08:05:26Z

1

As others have answered, this breaks because .ToString fails to translate to relevant SQL on the way into the database.

However, Microsoft provides the SqlFunctions class that is a collection of methods that can be used in situations like this.

For this case, what you are looking for here is SqlFunctions.StringConvert:

public ViewResult Index1(DateTime? Startdate) 
{ 
    Startdate = DateTime.Now.Date; 
    var fm = DateTime.Parse("01/01/2000"); 
    var qr = from item in db.BookingArriveds 
             where item.BookingArrivedEnquiredDateTime == Startdate 
             orderby SqlFunctions.StringConvert("01/01/2000 " +
             item.BookingArrivedEnquiredTime.ToString()) 
             select item; 
     return View(qr);
}

Good when the solution with temporary variables is not desirable for whatever reasons.

edited Mar 20, 2015 at 8:05

answered Mar 20, 2015 at 7:59

Ramesh Rajendran

38.9k49 gold badges159 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Liero Over a year ago

this does alphabetical sorting on string field, which is wrong. He need to convert BookingArrivedEnquiredTime to datetime

Ramesh Rajendran Over a year ago

My working code is helps to resolve the error only !

Liero · Accepted Answer · 2015-03-20 08:27:41Z

0

You have two options:

you can do the casting and sorting on client:

db.BookingArriveds
  .Where(item => item.BookingArrivedEnquiredDateTime == Startdate)
  .AsEnumerable()
  .OrderBy(item => DateTime.Parse("01/01/2000 " + item.BookingArrivedEnquiredTime);

or you can use SqlFunctions.DatePart to do the cast in Sql server: https://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions(v=vs.110).aspx

EDIT: DatePart function is not suitable, because it gives you only date part. To do the casting in SQL server, you should define your own sql function: https://msdn.microsoft.com/en-us/library/vstudio/dd456847(v=vs.100).aspx

The question is, why are you storing BookingArrivedEnquiredTime in a varchar column. I believe it should be part of BookingArrivedEnquiredDateTime or it should be stored as integer or numeric column

edited Mar 20, 2015 at 8:27

answered Mar 20, 2015 at 8:15

Liero

27.8k41 gold badges184 silver badges350 bronze badges

2 Comments

Kamesh Naidu Over a year ago

--->i tried your query it's working fine it is sorting am to pm but it not sorted the after 12 i will give sample out put here--> 12:48 AM 06:02 AM 07:06 AM 08:41 AM 09:41 AM 12:38 PM 06:02 PM 07:06 PM 08:41 PM

Liero Over a year ago

I don't see problem in the output. It is sorted as it should be. 12:38PM -> 12:38; 6:02PM -> 18:02, etc..

Collectives™ on Stack Overflow

How to write linq query from sql query using LINQ?

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related