Reputation: 2646
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
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
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
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
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
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