How to cast datetimeoffset to datetime in entity framework?

datetimeoffset entity-framework-6

Question

It seems like a simple thing to do, but EF doesn't support properties like DateTimeOffset.DateTime or DateTimeOffset.LocalDateTime or even DateTime.Date. I'm trying to filter a DateTimeOffset type field by the DateTime component only (i.e. the 'local' DateTime), which is a simple thing to do in plain sql: where cast(x as datetime) = '2016-12-14'.

There is a DbFunctions.TruncateTime, but no corresponding TruncateOffset.

There just doesn't seem to be any way to cast or convert a DateTimeOffset object to a normal DateTime object that works in linq-to-entities. The only conversion is an implicit one from DateTime to DateTimeOffset, but not the other way around.

1
1
12/14/2016 6:03:17 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

Popular Answer

You can use the DbFunctions.CreateDateTime to strip off the offset, for example:

...
.Select(e => new MyClass {                                      
  MyDate = DbFunctions.CreateDateTime(e.MyDateTimeOffset.Year,
                                      e.MyDateTimeOffset.Month,
                                      e.MyDateTimeOffset.Day,
                                      e.MyDateTimeOffset.Hour,
                                      e.MyDateTimeOffset.Minute,
                                      e.MyDateTimeOffset.Second)
...

As you might suspect, this creates some truly awful SQL, ie:

convert (datetime2,right('000' + convert(varchar(255), DATEPART (year, [Extent1].[MyDateTimeOffset])), 4) + '-' + convert(varchar(255), DATEPART (month, [Extent1].[MyDateTimeOffset])) + '-' + convert(varchar(255), DATEPART (day, [Extent1].[MyDateTimeOffset])) + ' ' + convert(varchar(255), DATEPART (hour, [Extent1].[MyDateTimeOffset])) + ':' + convert(varchar(255), DATEPART (minute, [Extent1].[MyDateTimeOffset])) + ':' + str( CAST( DATEPART (second, [Extent1].[MyDateTimeOffset]) AS float), 10, 7), 121) AS [C2]

However it will give you what you want.

One caveat is that this will completely strip-off / ignore the offset (as the OP requested).

1
9/23/2017 3:58:53 PM

Related Questions

LINQ to Entities only supports casting in the Entity Framework. basic kinds in the Entity Data Model

The Entity Framework makes this difficult and I'm not sure there's a way to do what you want to do with a single return value type (IComparable, object, etc). You might consider reworking your design into a dictionary of name-to-...Func<IQueryable<K>, IOrderedQueryable<K>>... values:..._possibleSortForForumItem.Add("CreateDate", query => query...
entity-framework primitive-types
asked by Ben M

Select specified columns and return strongly typed without loosing cast using Entity Framework.

A bit kludgy, but will work:...var result = from a in new DataContext().Actions where a.UserId == someGuidValue let IsEvent = a as Event != null select new { a.CreatedOn, IsEvent, a.Summary }; List<Action> list = result.AsEnumerable() .Select(o => o.IsEvent ? ...
asp.net-mvc entity-framework inheritance linq linq-to-sql
asked by Craig Stuntz

DateTimeOffset to SQL Server DateTime Mapping in Entity Framework

Is there a way to map a ...DateTimeOffset... property to a SQL Server ...datetime... column, with the assumption that you can't change either side, meaning that the property and column have to stay those date types? ...I know the easiest is to make them match but want to know if there's a way to work around this. I was looking into custom mappings ...
ef-code-first entity-framework mapping
asked by Carlos

DateTime in entity Framework && DateTime in sql format returning different results

Guys i have a big problem in entity framework and DateTime. Here the problem:...First with entity:...When the user submit the form them those values are being create for a condicional query depending on which parameter the user type. Example:...var query = from user in UserTable select user; if(Request.Form["Category"]) { ...
asp.net-mvc-4 c# entity-framework sql sql-server
asked by user2195741

How to implement a DateTime entity property to set always DateTime.Now

You could add a constructor to your class and then initialize there your property....public class News { // properties goes here public News() { Date=DateTime.Now; } } ...A far better constructor would be the following...public News(int newsId, string title, string content) { NewsId=newsId; Title=title; Content=con...
c# datetime entity-framework properties
asked by Christos

Cast() exception: LINQ to Entities only supports casting EDM primitive or enumeration types

Since you're getting all of the entities, you could materialize the query first, then perform the cast....This would perform the Cast in memory instead of trying to formulate it as part of the query translation. The error comes because LINQ to Entities doesn't support transforming the SQL to a non-entity class, even when it inherits from an entity...
asp.net-mvc c# casting entity-framework linq-to-entities
asked by tvanfosson

Entity Framework - ridiculous Query, casting smallint to int for comparison

You need to create the ...Where... expression yourself using the static functions in the ...Expression... class....Like this:...Int16 bucketId = 3; var parameter = Expression.Parameter(typeof(SimStgTrade)); var property = Expression.PropertyOrField(parameter, "BucketRef"); var constant = Expression.Constant(bucketId); var comparison = Expression.E...
entity-framework entity-framework-6
asked by Aducci

Is there a way to cast in the `Where` part of an Entity Framework query?

You can use ...SqlFunctions.StringConvert...:...var q = db.Employees .Where(emp => SqlFunctions.StringConvert((decimal) emp.Id).Contains("95"));
c# casting entity-framework entity-framework-6 linq
asked by Tim Schmelter

Entity Framework 6 related entity where datetime clause doesn't translate to SQL

"EF makes some very expensive queries" mmm you have control of how you would like to use the framework with its limitation*, if you know something is bad but still do it that way its not EF fault....You could do it exactly the way you have....i.e. if you did this with raw sql how would you do it....Then simply do the same in EF....var channel = co...
c# entity-framework-6
asked by Seabizkit

Cast a nullable DateTime in a lambda expression

You have to access the ...Value... field of the nullable type:...DateTime? nullableDt = DateTime.Now; int year = nullableDt.Value.Year;
asp.net-mvc c# entity-framework-6
asked by pappbence96

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

More Projects...

Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow