How to use linq to query field type is time and add 20 mins to it

Question

1.00/5 (1 vote)

See more:

I have 2 tables, one is the booking table for use to field. and will insert the user flight time to this table booking,

another table is named bus timetable, once the client insert the flight time to the booking table, then I need to write a query to find if there are time suit for this client, if has then get the list of value.

C#

airportTimetable = from r in db.ShuttleTimeTables 
                    where r.TimeTypeId == 1 && r.Time >= flightTime + (30min)
                    select new SelectListItem 
                  { 
                      Value = r.Time.ToString(), 
                      Text = r.Time.ToString() };

// and the time need format,I don't know how to format it.
// output look is like this 08:00:00.0000000 at the moment,
// it is ugly.

Question is how to format timespan in linq and how to add 30 mins to my timespan

Posted 29-Oct-14 16:50pm

SandyZhang2014

Updated 29-Oct-14 16:54pm

George Jonsson

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

DamithSL · Answer 1 · 2014-10-29T16:58:00

Solution 1

refer DateTime.AddMinutes Method[^] and DateTime.ToString Method (String)[^] those methods will help you to do the task.

C#

DateTime time = flightTime.AddMinute(30);

airportTimetable = from r in db.ShuttleTimeTables 
                    where r.TimeTypeId == 1 && r.Time >= time
                    select new SelectListItem 
                  { 
                      Value = r.Time.ToString(), 
                      Text = r.Time.ToString("HH:mm:ss") };

if you have timespan then,

C#

TimeSpan time = flightTime.Add(TimeSpan.FromMinutes(30));

airportTimetable = from r in db.ShuttleTimeTables 
                    where r.TimeTypeId == 1 && r.Time >= time
                    select new SelectListItem 
                  { 
                      Value = r.Time.ToString(), 
                      Text = r.Time.ToString(@"hh\:mm\:ss") };

refer TimeSpan.Add Method[^] and Custom TimeSpan Format Strings[^]

Posted 29-Oct-14 16:58pm

DamithSL

Updated 29-Oct-14 17:13pm

v6

Comments

SandyZhang2014 29-Oct-14 23:01pm

How to use it?? do you have an example?

SandyZhang2014 29-Oct-14 23:03pm

The field just have time, don't have date, I don't know how to use it.

SandyZhang2014 29-Oct-14 23:06pm

DateTime time = flightTime.AddMinute(30);this doen't work,
the flightTime return timespan

SandyZhang2014 29-Oct-14 23:24pm

The query is work, but the format don't work

DamithSL 29-Oct-14 23:27pm

what is the format you want to show? check the documentation and change the format as you wish.

SandyZhang2014 30-Oct-14 0:31am

Any format put into it, then show error

SandyZhang2014 30-Oct-14 0:32am

It don't accept this ToString(@"hh\:mm\:ss") };

DamithSL 30-Oct-14 0:38am

what is r.Time? is it timespan?

SandyZhang2014 30-Oct-14 18:52pm

In my database the field type is time(7), and when I put ToString(@"hh\:mm\:ss") }; this, then show error, but if I put it like this
select new SelectListItem { Value = r.Time.ToString(), Text = r.Time.Value.ToString(@"hh\:mm\:ss") };
at the runtime show error:LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression.

SandyZhang2014 · Answer 2 · 2014-10-30T13:14:00

It solved, I put it here, hope that will help someone else:

<pre lang="c#">
TimeSpan time = flightTime.Add(TimeSpan.FromMinutes(30));
 
airportTimetable = (from r in db.ShuttleTimeTables
                    where r.TimeTypeId == 3 && r.Time >= time2
                    select r.Time)
                    .ToList()
                    .Select(x => new SelectListItem() 
                     {
                        Text = x.Value.ToString(@"hh\:mm"),
                        Value = x.Value.ToString(@"hh\:mm")
                      })
                   .ToList();