3

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?

MarcinJuraszek
  • 124,003
  • 15
  • 196
  • 263
Jim Blum
  • 2,656
  • 5
  • 28
  • 37

5 Answers5

5

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));
Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
  • 1
    `query.ToList()` will fetch all data from DB, not only `x.Date` value, which is important. – MarcinJuraszek Feb 06 '14 at 04:45
  • 1
    @MarcinJuraszek - Without knowing the schema, you can't assume that there are any more than one column in the table. I give the asker credit for knowing his own schema and making appropriate changes. – Erik Funkenbusch Feb 06 '14 at 04:50
  • Yes, I know that's a possibility. And that's why I just commented instead of downvoting :) – MarcinJuraszek Feb 06 '14 at 04:51
  • Thanks a lot Erik. I thank you so much! You made it work! thanks! Just upvoted. I am very happy with the solution, but just because I want to do everything really efficient, can you also come up with something that could be more effective? Once again, I thank you so much – Jim Blum Feb 06 '14 at 05:06
  • @JimBlum - Well, in your case, if all you want is to find the earliest date, you can probably just order the column and then just take the first entry. If you don't actually need it in a DateTime object, then you don't even need to parse it. – Erik Funkenbusch Feb 06 '14 at 05:13
  • Thanks. Actually, I am doing a lot more with the `dates`, so I guess, I can't use that. Thanks a lot – Jim Blum Feb 06 '14 at 05:15
  • Chosen as best answer :) – Jim Blum Feb 06 '14 at 05:20
3

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.

Timothy Walters
  • 16,866
  • 2
  • 41
  • 49
  • @JimBlum from comments you've made on other answers, it seems you want to do more than just get the minimum date. Could you please update your question with more details of what you need so I can provide a better answer? I often resort to a 2-step process, reduce my rows and get as much raw data as I need, then to `.ToList()` then I safely use all the non-SQL compatible calls I like on the in-memory set. – Timothy Walters Feb 06 '14 at 05:29
1

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.

MarcinJuraszek
  • 124,003
  • 15
  • 196
  • 263
  • Thanks a lot Marcin. I will try that now – Jim Blum Feb 06 '14 at 04:42
  • ToList will work and there's nothing actually wrong with it but if you're not actually going to use it as a List then why create a List? Arrays are more efficient than Lists so you should be calling ToArray by default and only calling ToList when you need to. – jmcilhinney Feb 06 '14 at 04:43
  • @jmcilhinney I don't see any reason why `ToArray` would be more efficient here. Maybe only a fact, that `ToArray()` would shrink allocated array when all records are loaded when `ToList()` will keep these not used space within underlying array to make additional inserts faster in future. – MarcinJuraszek Feb 06 '14 at 04:44
  • This gives me a syntax error `"Cannot convert lambda expression to type ulong because it is not a delegate type"` – Jim Blum Feb 06 '14 at 04:48
  • Check now. I noticed `x => x =>` was there by accident. – MarcinJuraszek Feb 06 '14 at 04:49
  • Thanks. Now I get a syntax error `The best overloaded method for System.DateTime.ParseExact(string,string,IFormatProvider ) has some invalid arguments` – Jim Blum Feb 06 '14 at 04:52
  • @MarcinJuraszek - ToArray requires that there be a large enough block of contiguous memory. That could cause out of memory exceptions if the result set is large and the heap is fragmented, even with plenty of free memory. – Erik Funkenbusch Feb 06 '14 at 04:53
  • List does the same, because it uses array internally to store elements. – MarcinJuraszek Feb 06 '14 at 05:09
  • Thanks a lot Marcin. Upvoted, because your answer was very much helpful – Jim Blum Feb 06 '14 at 05:21
  • @MarcinJuraszek, as you say, a List uses an array internally so how can an array not be more efficient? The List is just an unnecessary layer in between adding overhead. Obviously it's a very small overhead and the difference will be imperceptible to the user but an imperceptible gain is no reason not to do something so simple. It takes absolutely no extra effort to use ToArray instead of ToList and it is more efficient, however marginally, so ToArray should be used. Do you usually create a List where only an array is needed? If not then why here? – jmcilhinney Feb 06 '14 at 06:21
  • OK, If you care so much about that overhead, why don't you use `AsEnumerable()`? It will make even and array unnecessary. – MarcinJuraszek Feb 06 '14 at 06:26
0

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.

jmcilhinney
  • 50,448
  • 5
  • 26
  • 46
  • 1
    Thanks a lot jmcilhinney for you answer. However, I think your answer could be better supported if it included a piece of code :) – Jim Blum Feb 06 '14 at 04:58
-1

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();
RiveN
  • 2,595
  • 11
  • 13
  • 26
sameer bhokare
  • 14
  • 1
  • That's what the first comment, 8 years ago, already suggested. It doesn't answer the immediate question and in this case it wasn't even possible. – Gert Arnold May 30 '22 at 07:26