Home » Ado.Net » Entity Framework » How to use DateTime in Select with Linq to Entity (Entity Framework 4.3)

How to use DateTime in Select with Linq to Entity (Entity Framework 4.3)

If you want to create in your SELECT a new DateTime within your Linq to Entity query, you will reach an exception telling you that you cannot use parameterless class.

To solve this problem, you need to use a special function that only Linq to Entity can use. Entity Framework has some methods that will convert C# code into Sql function. In our case, the method is called “EntityFunctions.CreateDateTime(…)“.

Instead of having something like this:

var data = (from i in Database.Users
       group i by new {y=i.InvoiceDate.Year,m = i.InvoiceDate.Month} into g
       select new UserStatistic{ Date = new DateTime(g.Key.y, g.Key.m, 1), Experience = g.Sum(o => o.Exp));

You need to use the EntityFunctions.CreateDateTime :

var data = (from i in Database.Users
       group i by new {y=i.InvoiceDate.Year,m = i.InvoiceDate.Month} into g
       select new UserStatistic{ Date = EntityFunctions.CreateDateTime(g.Key.y, g.Key.m, 1, 0, 0, 0), Experience = g.Sum(o => o.Exp));

Both code do the same thing, but the second work with Entity Framework. This is because it uses Linq to Entity which convert its statement into SQL and hence, doesn’t know how to create a .Net DateTime object.

You can find the complete exhaustive list of entity functions at Microsoft MSDN web site.

If you like my article, think to buy my annual book, professionally edited by a proofreader. directly from me or on Amazon. I also wrote a TypeScript book called Holistic TypeScript

One Response so far.

  1. ivan says:

    Great, thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.