CODEWITHSUNDEEP
c#linqdatetime
Craig
Craig

Reputation: 18694

Create DateTime within LINQ query

I am getting the following error:

Only parameterless constructors and initializers are supported in LINQ to Entities.

I am trying to query data from a result set, and part of the new result set returns a new date field.

var result = debts.Select(x => new 
             { 
                 x.Snowball.User.Email, 
                 x.SnowballID, 
                 x.Description, 
                 x.ID, 
                 x.DueDayOfMonth, 
                 Due = new DateTime(ExecutionDate.Year, 
                                    ExecutionDate.Month, 
                                    x.DueDayOfMonth) 
             }).ToList();

Is there a way I can create this deduced datetime?

Upvotes: 2

Views: 6435

Answers (4)

sheavens
sheavens

Reputation: 725

I came here looking for the same thing, but on further research found another way using DbFunctions.CreateDateTime in System.Data.Entity. Using this method your query would look like this:

var result = debts.Select(x => new 
             { 
                 x.Snowball.User.Email, 
                 x.SnowballID, 
                 x.Description, 
                 x.ID, 
                 x.DueDayOfMonth, 
                 Due = DbFunctions.CreateDateTime(ExecutionDate.Year, 
                                    ExecutionDate.Month, 
                                    x.DueDayOfMonth, 0,0,0) 
             }).ToList();

EDIT: This is for EF6 and later, for earlier versions of EF use System.Data.Entity.Core.Objects.EntityFunctions instead of DbFunctions

Upvotes: 8

Steve Padmore
Steve Padmore

Reputation: 1740

LINQ to Entities has a boundary for the query.

The IQueryable result allows one or more filters and selections etc. to be added step by step if you wish.

While the query is being added to nothing has hit the database yet; only when you access the result set will the query be constructed by the LINQ Provider (a single Transact SQL Query for example), then run on the database, and its result returned.

In the case you have shown, you are trying to create a new object (Date - but could also be one of your own classes) in the database's result set.

So, for each row returned from the database query you are trying to create a column from an object (from your code base) different for each row (need to pass the parameters to the constructor). Because the underlying provider cannot guarantee to be able to create arbitrary objects from an external source on the fly, this is not allowed (prevented by the LINQ Provider).

The query that will be run against the database is 'debts'. This will return all of the rows from the query unfiltered (as there is no WHERE clause).

The next query will be run in your code. In your code you can use parameterised constructors - and is now LINQ To Objects.

So the way to distinguish the separate stages of the query, and do what you want to do, is to place the ToList() at the end of the query that runs against the database so that the query is generated, run, and the results returned. Then construct and run your next query (in your code) on those results.

var result = debts.ToList().Select(x => new 
{ 
    x.Snowball.User.Email, 
    x.SnowballID, 
    x.Description, 
    x.ID, 
    x.DueDayOfMonth, 
    Due = new DateTime(ExecutionDate.Year, ExecutionDate.Month, x.DueDayOfMonth) 
});

Upvotes: 5

user3188639
user3188639

Reputation:

You may try this:

var result = debts.Select(x =>
    new 
    { 
    x.Snowball.User.Email, x.SnowballID, x.Description, x.ID, 
    x.DueDayOfMonth, 
    exY = ExecutionDate.Year, 
    exM = ExecutionDate.Month, 
    exD = x.DueDayOfMonth) 
    }).ToList()
    .Select(a => new {Email, SnowballID, Description, ID, Due=new DateTime(exY, exM, exD)})
    .ToList()

Upvotes: 0

Connell.O'Donnell
Connell.O'Donnell

Reputation: 3703

You could create your own Date class with a parameter-less constructor and set it using property initializer syntax like

Due = new Date(){Year = ExecutionDate.Year, Month = ExecutionDate.Month, Day = x.DueDayOfMonth}

Then just define a cast from Date to DateTime.

Upvotes: 0

Related Questions