CODEWITHSUNDEEP
c#entity-frameworklinq
JH. Lee
JH. Lee

Reputation: 27

How to use Convert.ToDateTime in LINQ C#

I have a question about EntityFrameWork/LINQ. First, I want to set Year from b.Datetime(data type is string), so I use Convert.ToDateTime method, but an error occurs.

Here is my code.

IQueryable<AlarmCount> alarmCount = from b in fatalalarminfo
                                    group b by new
                                    {
                                        GroupKey = b.Eqpid,
                                        Year = Convert.ToDateTime(b.DateTime).Year
                                     } into gbs
                                     orderby gbs.Key.Year, gbs.Key.GroupKey
                                     select new AlarmCount()
                                     {
                                         GroupKey = gbs.Key.GroupKey,
                                         Period = new Period(EnumPeriodType.YEAR, gbs.Key.Year),
                                         Amount = gbs.Count()
                                     };

See the error message below.

enter image description here

Upvotes: 1

Views: 841

Answers (2)

BrandonStudio
BrandonStudio

Reputation: 79

The IQueryable<T> does queries in database, you get this exception because there is no function mapped to Convert.ToDateTime in your database. Try IEnumerable<T> instead.

Upvotes: 0

Joost00719
Joost00719

Reputation: 754

The issue is that it is an IQueryable. This means that your linq query is being translated to SQL, and it cannot translate Convert.ToDateTime to sql. You can use DateTime.ParseExact, which does translate to SQL properly.

IQueryable<AlarmCount> alarmCount = from b in fatalalarminfo
                                            group b by new
                                            {
                                                GroupKey = b.Eqpid,
                                                Year = DateTime.ParseExact(b.DateTime, "<format>", CultureInfo.InvariantCulture).Year
                                            } into gbs
                                            orderby gbs.Key.Year, gbs.Key.GroupKey
                                            select new AlarmCount()
                                            {
                                                GroupKey = gbs.Key.GroupKey,
                                                Period = new Period(EnumPeriodType.YEAR, gbs.Key.Year),
                                                Amount = gbs.Count()
                                            };

Make sure to replace <format> with the correct format.

Upvotes: 1

Related Questions