How to use Convert.ToDateTime in LINQ C#-CodePudding

I wanna ask question about EntityFrameWork/LINQ Here is my code. first, I wanna set Year from b.Datetime(data type is string) so, I use Convert.ToDateTime method, but the error is occurred.

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()
                                            };

About error message, please refer to under image link. please help..!! thanks..!

enter image description here

CodePudding user response：

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.

CodePudding user response：

The IQureable<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.