Jim Blum
Jim Blum

Reputation: 2646

Error Linq to Entities Datetime

I have the following code

var dates = query.Select(
                 x => DateTime.ParseExact(x.Date, "yyyy-MM", CultureInfo.InvariantCulture));

var minDate = dates.Min(x => x);

But When I execute that, I get the exception

System.Data.Entity.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method 'System.DateTime ParseExact(System.String, System.String, System.IFormatProvider)' method, and this method cannot be translated into a store expression.

What am I doing wrong? And how I can fix that?

Upvotes: 3

Views: 7303

Answers (5)

sameer bhokare
sameer bhokare

Reputation: 14

Convert Date Time in your db datetime format:

obj.dt = TimeZoneInfo.ConvertTimeFromUtc((DateTime)obj.dt, TimeZoneInfo.FindSystemTimeZoneById("India Standard Time"));

It`s an easy way to compare date without time. Just use the following code:

_DBcontext.YourModelName.AsQueryable().Where(x=> x.CREATED_DATE.Date == dt.Date).toList();

Upvotes: -1

Timothy Walters
Timothy Walters

Reputation: 16874

If the dates are in the DB as strings in the format "yyyy-MM", then you can do a query based on string sorting and convert the result to a DateTime:

var minDateString = query.Select(x => x.Date).Min();
var minDate = DateTime.ParseExact(
    minDateString, 
    "yyyy-MM", 
    CultureInfo.InvariantCulture
);

Many systems have relied on the natural string ordering of "yyyy-MM-dd hh:mm:ss", you can just as easily rely on a subset of that ordering.

Upvotes: 3

MarcinJuraszek
MarcinJuraszek

Reputation: 125620

I'm afraid you'd have to load all your string representations of datatime values from DB to memory:

var dates = query.Select(x => x.Date).ToList();

and perform parsing and min as LINQ to Objects query:

var min = query.Min(x => DateTime.ParseExact(x, "yyyy-MM", CultureInfo.InvariantCulture));

If your DB was set property and x.Date was DateTime you could do:

var dates = query.Select(x => x.Date);
var min = dates.Min();

which would be translated into proper SQL query and let MIN() be calculated by database, so you wouldn't have to fetch all data into application memory.

Upvotes: 1

Erik Funkenbusch
Erik Funkenbusch

Reputation: 93434

Well, the error is actually quite clear. There is no translation in Linq to Entities of ParseExact to SQL.

Remember, Entity Framework, under the covers, converts the query to a SQL command or set of commands. If EF doesn't know how to translate something, it throws this error.

One possible solution, while not terribly efficient, is to convert the IQueryable to IEnumerable, which will allow you to execute the statement.

var dates = query.ToList().Select(
             x => DateTime.ParseExact(x.Date, "yyyy-MM", CultureInfo.InvariantCulture));

Upvotes: 4

jmcilhinney
jmcilhinney

Reputation: 54417

LINQ to Entities takes your LINQ query and converts it to SQL code that it then executes against the database. That means that the code you write has to be able to be converted to SQL code. There is no way to convert DateTime.ParseExact to SQL code, hence the exception. You will need to evaluate a LINQ to Entities query by calling ToArray and then you can perform a second LINQ to Objects query on that and it will understand DateTime.ParseExact.

Upvotes: 0

Related Questions