Add Datetime and time in Entity Framework from separate column

Question

I want to get all the events which are not ended yet

select * FROM [Events] where EndDate+cast(EndTo as datetime)>GETDATE()

which is working perfectly.

This is my value in (EndTo: 11:00:00.0000000, END DATE: 2016-05-26)

I tried using

var list = dbContext.Events
                    .Where(e => e.EndDate + e.EndTo > DateTime.Now)
                    .ToList();

and I'm getting an error

DbArithmeticExpression arguments must have a numeric common type.

How can I write this query in Entity Framework?

Ian · Accepted Answer · 2016-05-26 06:04:26Z

3

This is because you try to add DateTime with TimeSpan, which is not allowed. To do that, try to use something like DateTime.AddSeconds(TimeSpan.TotalSeconds) instead:

var list= dbContext.Events
           .Where(e=>e.EndDate.AddSeconds(e.EndTo.TotalSeconds) > DateTime.Now)
           .ToList();

Or if this is LINQ to Entities:

var list= dbContext.Events
           .Where(e=>EntityFunctions.AddSeconds(e.EndDate, e.EndTo.TotalSeconds) > DateTime.Now)
           .ToList();

Edit:

Since your e.EndTo is a Nullable<TimeSpan>, then you should use its Value:

var list= dbContext.Events
           .Where(e=>e.EndDate.AddSeconds(e.EndTo.Value.TotalSeconds) > DateTime.Now)
           .ToList();

Or

var list= dbContext.Events
           .Where(e=>EntityFunctions.AddSeconds(e.EndDate, e.EndTo.Value.TotalSeconds) > DateTime.Now)
           .ToList();

edited May 26, 2016 at 6:04

answered May 26, 2016 at 5:22

Ian

30.9k20 gold badges76 silver badges112 bronze badges

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

4 Comments

M.Nabeel Over a year ago

Error : 'System.Nullable<System.TimeSpan>' does not contain a definition for 'TotalSeconds' and no extension method 'TotalSeconds' accepting a first argument of type 'System.Nullable<System.TimeSpan>' could be found (are you missing a using directive or an assembly reference?)

Ian Over a year ago

@M.Nabeel ah, so your e.EndTo is a Nullable TimeSpan, in that case, please try to use e.EndTo.Value.TotalSeconds instead

Ian Over a year ago

@M.Nabeel no problem! ;)

xr280xr Over a year ago

This doesn't work for Linq to Entities. "'TotalSeconds' is not supported in LINQ to Entities". Additionally TotalSeconds is a double but AddSeconds takes an int so it won't even compile as written.

Collectives™ on Stack Overflow

Add Datetime and time in Entity Framework from separate column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related