Entity Framework, DateAdd - manipulate dates upon select

Question

Is it possible to manipulate dates in an EF query while maintaining the flexibility of wildcard select?

In old school SQL you can do this

SELECT ID, Name, 
DATEADD (minute, 30, [StartTime]) AS [StartTime]
FROM Titles

I know you can to the same in EF with

var items = context.Titles.Select(n => new { 
                ID = n.ID,
                Name= n.Name,
                StartTime = System.Data.Objects.EntityFunctions.AddMinutes(n.StartTime, 30)
            });

The challenge for me is that I'd like to not have to specify all properties.

Is it possible to do something like

var items = context.Titles.SomethingSomething(n => 
LinqToUpdateOnly 'StartTime' property);

?

Aducci · Accepted Answer · 2014-03-26 17:23:28Z

0

You could create a class that holds the original entity plus any additional properties you want and then create a translation method that projects from your entity to the new class. It is not as flexible as you are looking for, but you can do this:

Custom class

public class TitlePoco
{
  public Title Title { get; set; }
  public DateTime StartTime { get; set; }
}

A single point that handles all the translations you want

public IQueryable<TitlePoco> TranslateTitles(IQueryable<Title> query)
{
  return query.Select(n => new TitlePoco { 
                Title = n,
                StartTime = System.Data.Objects.EntityFunctions.AddMinutes(n.StartTime, 30)
            });
}

Then you call your method like this

var items = TranslateTitles(context.Titles.Where(x => ...));

edited Mar 26, 2014 at 17:23

answered Mar 25, 2014 at 20:19

Aducci

26.8k8 gold badges67 silver badges69 bronze badges

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

5 Comments

Michael H. Pedersen Over a year ago

Actually, this is what I'm doing :) But it still doesn't answer the question (hopefully I'm asking it right). I specifically do not want to have to '.Select(n => new....)'. The point is that I want the query itself to be flexible. In the event that I add a column (and poco class property) I don't want to have to go through the entire DAL to change the '.Select(n => new' statements.

Aducci Over a year ago

@Michael'mital'Pedersen - Can you give an example of why you want to modify the entity property directly, instead of adding a property via a partial class?

Michael H. Pedersen Over a year ago

it's all a matter of performance. Let's say "StartTime" is UTC and I want to display 100,000 records with the datetime converted to a different timezone. In this case I want the SQL Server to add 120 minutes (example) to StartTime before returning the results. This should perform much better than calling a property in a partial class 100,000 times.

Aducci Over a year ago

@Michael'mital'Pedersen, What about creating a class that contains your entity plus any additional properties, this way you dont have to worry about any properties changing on your entity, but you can still do the calculations on sql server for any additional properties, take a look at the updated answer

Michael H. Pedersen Over a year ago

I thought about this too. Only reason I didn't do it already is that it would introduce a second "layer" in the entity as far as DAL goes, which is what I was trying to avoid. However, for this application performance is more important that maintainability (:-/), so I'll have to set aside my aversion for DAL code logic exceptions and do as you suggest. I'm accepting this answer. Thanks :-)

Allister · Accepted Answer · 2014-03-25 20:46:46Z

0

How about this for old school?

var items = context.Titles.ToList();
foreach(var title in items) StartTime = StartTime.AddMinutes(30);

answered Mar 25, 2014 at 20:46

Allister

611 bronze badge

1 Comment

Michael H. Pedersen Over a year ago

Absolutely not. The 'ToList()' will cause the query to execute and property update to occur afterwards. This type of simple mass-manipulation is best placed on the SQL Server.

Collectives™ on Stack Overflow

Entity Framework, DateAdd - manipulate dates upon select

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related