Error: not supported exception in linq query while calling a method to set properties in linq statement

Question

0.00/5 (No votes)

See more:

Hi,

I am getting not supported exception while calling methods to set values to anonymous type. I can understand why is it but is there any other way to achieve what I am trying to do. please look at the code below.

What I have tried:

private static double CalculateAvgDayFromDates(DateTime date, DateTime productionStartDate)
        {
            return (Convert.ToDateTime(date.ToShortDateString()) - Convert.ToDateTime(productionStartDate.ToShortDateString())).TotalDays + GetPercentageOfDay(date.TimeOfDay);
           
        }
        public static void GetDirectorDetails()
        {
            try
            {
                var sdt = new DateTime(2018, 2, 3);
                var edt = new DateTime(2019, 2, 1);

                using (var db = new MyDbContext())
                {
                    var ManagerDetails = from p in db.Projects
                                          let projHOurs = db.ProjectHours.Where(ph => ph.ProjectID == p.ID).Sum(h => h.DayHours)
                                          //let lDate = CalculateAvgDayFromDates(p.ReadyToLaunchDate.Value, p.ProductionStartDate.Value)
                                          //let sLDate = CalculateAvgDayFromDates(p.ProductionStartDate.Value.AddDays(6), p.ProductionStartDate.Value)
                                          where (p.ProductionStartDate > sdt && p.ProductionStartDate <= edt)
                                          where (p.ProductionCompleteDate != null && p.ProductType == 1 && p.Active)
                                         
                                          select new
                                          {
                                              Manager = p.Manager,
                                              SalesEmployee = p.SalesEmployee,
                                              StoreNumber = p.StoreNumber,
                                              LaunchDate = CalculateAvgDayFromDates(p.ReadyToLaunchDate.Value, p.ProductionStartDate.Value),
                                              SoftLaunchDate = CalculateAvgDayFromDates(p.ProductionStartDate.Value.AddDays(6), p.ProductionStartDate.Value),
                                              totalWorkedHours = projHOurs

                                          };

                    var Query = from d in directorDetails
                                  group d by d.director into g
                                  select new
                                  {
                                      manager = g.Key,
                                      storeCounts = g.Count(),
                                      avgWorkedHours = g.Average(x => x.totalWorkedHours),
                                      avgLaunchDays = g.Average(x => x.LaunchDate),
                                      avgSoftLaunchDays = g.Average(x => x.SoftLaunchDate)
                                  };
                   
                    foreach (var d in Query)
                    {
                        System.Diagnostics.Debug.WriteLine($"dir:{d.manager}, storeCounts: {d.storeCounts}, avgLaunchDays: {d.avgLaunchDays}, avgSoftLaunchDays:{d.avgSoftLaunchDays}, avgWorkedHours: {d.avgWorkedHours}");
                      
                    }
                }
            }
            catch (Exception ex)
            {

                string s = ex.Message;
                System.Diagnostics.Debug.WriteLine(s);
            }
        }

Posted 30-Aug-18 6:58am

istudent

Updated 30-Aug-18 7:46am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2018-08-30T07:47:00

Entity Framework doesn't know how to convert your CalculateAvgDayFromDates method to a SQL query.

You'll need to pull the records into memory before performing that calculation.

C#

var managerQuery = from p in db.Projects
                   let projHOurs = db.ProjectHours.Where(ph => ph.ProjectID == p.ID).Sum(h => h.DayHours)
                   where (p.ProductionStartDate > sdt && p.ProductionStartDate <= edt)
                   where (p.ProductionCompleteDate != null && p.ProductType == 1 && p.Active)
                   select new
                   {
                       p.Manager,
                       p.SalesEmployee,
                       p.StoreNumber,
                       p.ReadyToLaunchDate,
                       p.ProductionStartDate,
                       projHOurs,
                   };

var ManagerDetails = from r in managerQuery.AsEnumerable()
                     select new
                     {
                         r.Manager,
                         r.SalesEmployee,
                         r.StoreNumber,
                         LaunchDate = CalculateAvgDayFromDates(r.ReadyToLaunchDate.Value, r.ProductionStartDate.Value),
                         SoftLaunchDate = CalculateAvgDayFromDates(r.ProductionStartDate.Value.AddDays(6), r.ProductionStartDate.Value),
                         totalWorkedHours = r.projHOurs,
                     };

The AsEnumerable() call forces the following LINQ methods to be executed in memory, rather than trying to convert them to SQL.