Execution of 'System.Linq.Enumerable:GroupBy(IEnumerable`1,Func`2)' on the database server side currently not implemented.

[Muhammad]

I am getting the error 'Execution of 'System.Linq.Enumerable:GroupBy(IEnumerable`1,Func`2)' on the database server side currently not implemented.'
when I execute the following query

from t in dbContext.TrackerRecords
                       where t.DeviceSerial.Value.Equals(deviceSerial) &&
                       t.Date.Value >= fromDate && t.Date.Value <= toDate
                       orderby t.Date.Value descending
                       group t by t.Date.Value.Date into g
                       select new TripDataModel
                       {
                           Day = g.Key,
                           Trips = (from x in g
                                   group x by x.Date.Value.Hour into gj
                                    where gj.Max(m => m.Speed.Value) > 0
                                    let AvgSpd = gj.Average(m => m.Speed.Value)
                                   select new TripModel
                                   {
                                       MinSpeed = gj.Min(m => m.Speed.Value),
                                       MaxSpeed = gj.Max(m => m.Speed.Value),
                                       AvgSpeed = AvgSpd > 0 ? Math.Round(AvgSpd, 2, MidpointRounding.AwayFromZero) : 0,
                                       FromHour = new DateTime(g.Key.Year, g.Key.Month, g.Key.Day, gj.Key, 0, 0)
                                   })
                       }

I tried the query in linqtosql and working fine but I need to use OpenAccess
Is there any solution or workaround this?

[Kristian Nikolov]

Hi Muhammad,

Thank you for contacting us.

Generally Telerik Data Access supports the execution of the GroupBy LINQ extension method on the database engine side. We attempted to reproduce the error you have experienced by creating a LINQ query with similar structure to the one you provided. The query however executed successfully.

Therefore we kindly ask you to provide us with the following details:

1. Which of the group statements in your query causes the exception?
2. Are TripDataModel and TripModel persistent types?
3. Are there null values in the database for any of the nullable properties seen in the query?

Please note that as we can conduct our support communication in this thread, I will close the other one.

We are looking forward to your feedback.

Regards,
Kristian Nikolov
Telerik

[Muhammad]

Execution of 'System.Linq.Enumerable:GroupBy(IEnumerable`1,Func`2)' on the database server side currently not implemented. This occured from g.GroupBy(x => x.Date.Hour).

public class TripDataModel
    {
        public DateTime Day { get; set; }

        public IEnumerable<TripModel> Trips { get; set; }

        public string Serial { get; set; }
    }

public class TripModel
    {
        public DateTime FromHour { get; set; }

        public int MinSpeed { get; set; }

        public int MaxSpeed { get; set; }

        public double AvgSpeed { get; set; }
    }

and there is no null values in the database
Thanks for your reply

[Kristian Nikolov]

Hello Muhammad,

Thank you for the provided details.

We attempted to reproduce the error again, but the LINQ query executed successfully on our side. We would like to investigate the situation further. Therefore we kindly ask you to send us a project which reproduces the error.

I have changed the type of this thread to Products Feedback in order to allow you to attach files.

We are looking forward to your feedback.

Regards,
Kristian Nikolov
Telerik

 

 

[Muhammad]

Thanks for replying,
it is sample project on same database with the same error 
https://drive.google.com/file/d/0B7XOgInzMeERY25kMUdSWDZKNjA/edit?usp=sharing

[Kristian Nikolov]

Hi Muhammad,

Thank you for the provided project.

The reason for the error you are experiencing most probably lies within the combination of the complexity of the query and the projection in non persistent type.

As a workaround you could consider splitting the query into two parts.

Part 1:

var trackedRecords = (from t in context.TrackerRecords
                            where t.DeviceSerial.Equals("1337") &&
                                t.Date >= new DateTime(2014, 2, 1) && t.Date <= new DateTime(2014, 3, 21)
                            orderby t.Date descending
                        select t).ToList();

Part 2:

var tripDataModels = (from r in trackedRecords
            group r by r.Date.Date
            into g
            select new TripDataModel()
            {
                Day = g.Key,
                Trips = (from x in g
                        group x by x.Date.Hour
                            into gj
                            where gj.Max(m => m.Speed) > 0
                            let AvgSpd = gj.Average(m => m.Speed)
                            select new TripModel
                            {
                                MinSpeed = gj.Min(m => m.Speed),
                                MaxSpeed = gj.Max(m => m.Speed),
                                AvgSpeed = AvgSpd > 0 ? Math.Round(AvgSpd, 2, MidpointRounding.AwayFromZero) : 0,
                                FromHour = new DateTime(g.Key.Year, g.Key.Month, g.Key.Day, gj.Key, 0, 0)
                            })
            }).ToList();

The first part retrieves the required TrackRecord objects and then the second part is executed in memory. As in your original query most of the filtering was done prior to the projection, this approach should not introduce any performance issue compared to your original query. Another thing you may notice is that the initial grouping from your original query is now moved to the second part. This is done in order to avoid the N+1 problem.

I hope this helps. Should you have additional questions, feel free to post in our forums again.

Regards,
Kristian Nikolov
Telerik