none
How to assign stored procedure result to a entity inside select statement in entity framework. RRS feed

  • Question

  • I want to assign GetMajorMileStone(projecttask.ProjectTaskId) result to MajorMilestone. I tried but getting following error:LINQ to Entities does not recognize the method 'System.Data.Objects.ObjectResult1[.Poco.GetMajorMileStone_Result] GetMajorMileStone(System.Nullable1[System.Guid])' method, and this method cannot be translated into a store expression.'

    Here is my code:

      
    public ProjectsPayload GetProjectSchedule(int projectid, bool includeArchived, DateTime startDate, DateTime endDate, int userId)
            {
                using (var db = new Entities())
                {
                    try
                    {
                        db.CommandTimeout = 1200;
    
                        var query = from project in db.Project.Where(t => (t.Active || t.TempActive) && t.ProjectId == projectid)
                                    join UP in db.User_X_Project on project.ProjectId equals UP.ProjectId
                                    where (UP.UserId == userId && UP.Active)
                                    orderby (project.Priority ?? int.MaxValue)
                                    orderby (project.ProjectTitle)
                                    select new
                                    {
                                        Project = project,
    
                                        ProjectTask = from projecttask in project.ProjectTask.Where(t => t.Active && (
                                                                                                                        (includeArchived == true && t.TaskStatusId == (int?)TaskStatus.Archived) ||
                                                                                                                        (includeArchived == false && t.TaskStatusId != (int?)TaskStatus.Archived))
                                                      || t.TaskStatusId != (int?)TaskStatus.Planned)
                                                      join schedule in project.ProjectTask.SelectMany(p => p.ProjectTaskSchedule) on projecttask.ProjectTaskId equals schedule.ProjectTaskId
                                                      join daily in db.ProjectTaskSchedule.SelectMany(p => p.DailyStatus) on schedule.ProjectTaskScheduleId equals daily.ProjectTaskScheduleId
                                                      where schedule.Active && daily.Active && projecttask.Active && schedule.ResourceId == userId && (
                                                                    (EntityFunctions.TruncateTime(daily.Date) >= EntityFunctions.TruncateTime(startDate.Date) &&
                                                                    EntityFunctions.TruncateTime(daily.Date) <= EntityFunctions.TruncateTime(endDate.Date))
                                                                    )
                                                      orderby schedule.StartDate
                                                      select new
                                                      {
    
                                                          ProjectTask = projecttask,
                                                          ProjectTaskSchedule = from projecttaskschedule in projecttask.ProjectTaskSchedule.Where(t => t.Active && t.ResourceId == userId)
                                                                                select new
                                                                                {
                                                                                    ProjectTaskSchedule = projecttaskschedule,
                                                                                    DailyStatus = projecttaskschedule.DailyStatus.Where(t => t.Active),
    
                                                                                },
                                                          CritiCality = from cr in db.CritiCality.Where(ts => ts.ProjectTaskId == projecttask.ProjectTaskId) select cr,
                                                          MMDetails = from mm in db.MMDetails.Where(ts => ts.ProjectTaskId == projecttask.ProjectTaskId) select mm,
    
                                                          MajorMilestone = db.GetMajorMileStone(projecttask.ProjectTaskId).AsEnumerable().FirstOrDefault(),
                                                      }
    
    
                                    };
    
                        var materialized = query.AsEnumerable();
                        var result1 = materialized.Select(t => t.Project).FirstOrDefault();
                        var result = materialized.Select(t => new ProjectsPayload
                        {
                            ProjectId = t.Project.ProjectId,
                            ProjectTitle = t.Project.ProjectTitle,
                            Priority = t.Project.Priority,
                            ProjectDescription = t.Project.ProjectDescription,
                            ProjectTask = t.Project.ProjectTask.Select(x => new ProjectTaskPayload
                            {
                                Duration = x.Duration,
                                Hours = x.Hours,
                                IsOngoing = x.IsOngoing,
                                IsSummaryTask = x.IsSummaryTask,
                                Priority = x.Priority,
                                ParentTaskId = x.ParentTaskId,
                                ProjectId = x.ProjectId,
                                ProjectTaskId = x.ProjectTaskId,
                                TaskAcceptanceId = x.TaskAcceptanceId,
                                TaskStatusId = x.TaskStatusId,
                                TaskTitle = x.TaskTitle,
                                TaskTypeId = x.TaskTypeId,
                                IsMileStone = x.IsMileStone,
                                IsTimeAwayTask = x.IsTimeAwayTask,
                                AutoSize = x.AutoSize,
                                IsArchivedTasksInSummary = x.IsArchivedTasksInSummary,
                                IsASAP = x.IsASAP,
                                IsAutoCompleteEnable = x.IsAutoCompleteEnable,
                                IsSharedDiffSchedules = x.IsSharedDiffSchedules,
                                LongDescription = x.LongDescription,
                                OwnerId = x.OwnerId,
                                ShowInSummaryTask = x.ShowInSummaryTask,
                                SubTypeID = x.SubTypeID,
                                MMDetails1 = x.MMDetails1.Select(MD => new MMDetailsPayload { MajorMilestoneId = MD.MajorMilestoneId, ProjectTaskId = MD.ProjectTaskId, MMDetailsId = MD.MMDetailsId, Slack = MD.Slack }),
                                ProjectTaskSchedule = x.ProjectTaskSchedule.Select(a => new ProjectsTaskSchedulePayload
                                {
                                    ProjectTaskScheduleId = a.ProjectTaskScheduleId,
                                    StartDate = a.StartDate,
                                    EndDate = a.EndDate,
                                    ProjectTaskId = a.ProjectTaskId,
                                    ResourceId = a.ResourceId,
                                    ArchiveEndDate = a.ArchiveEndDate,
                                    ArchiveStartDate = a.ArchiveStartDate,
                                    IsSharedTask = a.IsSharedTask,
                                    TimeUnitId = a.TimeUnitId,
                                    DailyStatus = a.DailyStatus.Select(Ds => new DailyStatusPayload
                                    {
                                        Active = Ds.Active,
                                        ActualHours = Ds.ActualHours,
                                        DailyStatusId = Ds.DailyStatusId,
                                        Date = Ds.Date,
                                        ProjectTaskScheduleId = Ds.ProjectTaskScheduleId,
                                        IsCloseOutDay = Ds.IsCloseOutDay,
                                        Priority = Ds.Priority
                                    })
                                }).ToList(),
                                CritiCality = x.CritiCality.Select(c => new CriticalityPayload { CriticalityId = c.CriticalityId, CriticalityTypeId = c.CriticalityTypeId, ProjectTaskId = c.ProjectTaskId }).ToList(),
    
                            }).ToList()
                        }).FirstOrDefault();
                        return result;
                    }
                    catch (EntityException ex)
                    {
                        if (ex.Message == connectionException)
                            throw new FaultException(dbException);
                        else
                            throw new FaultException(ex.Message);
                    }
                }
            }
    



    • Edited by Radhakishan Friday, December 6, 2019 8:45 AM
    Friday, December 6, 2019 6:00 AM

All replies

  • You can't use a stored procedure as part of a Linq query. The EF engine cannot make T-SQL involving the sproc to be submitted to the DB engine for execution. That is what the exception message means.

    Also when posting code, you should use the <> icon next to the HTML icon on the tollbar and format the code for posting.

    Friday, December 6, 2019 7:42 AM
  • Thank U so much..
    Friday, December 6, 2019 8:56 AM
  • I have Used LINQ instead of sproc for MajorMileStone. But I am getting count=0 for MajorMileStone.

    If I write outside select statement like... var v= //something// working fine. 

    I have to show the result for MajorMileStone inside select statement. Remaining Entities(CritiCality,MMDetails) showing results fine.. not working for MajorMileStone . I have records in DB for MajorMileStone.

    My code is...

    var query = from project in db.Project.Where(t => (t.Active || t.TempActive) && t.ProjectId == projectid)
                                    join UP in db.User_X_Project on project.ProjectId equals UP.ProjectId
                                    where (UP.UserId == userId && UP.Active)
                                    orderby (project.Priority ?? int.MaxValue)
                                    orderby (project.ProjectTitle)
                                    select new
                                    {
                                        Project = project,
                                        ProjectTask = from projecttask in project.ProjectTask.Where(t => t.Active && (
                                                                                                                        (includeArchived == true && t.TaskStatusId == (int?)TaskStatus.Archived) ||
                                                                                                                        (includeArchived == false && t.TaskStatusId != (int?)TaskStatus.Archived))
                                                      || t.TaskStatusId != (int?)TaskStatus.Planned)
                                                      join schedule in project.ProjectTask.SelectMany(p => p.ProjectTaskSchedule) on projecttask.ProjectTaskId equals schedule.ProjectTaskId
                                                      join daily in db.ProjectTaskSchedule.SelectMany(p => p.DailyStatus) on schedule.ProjectTaskScheduleId equals daily.ProjectTaskScheduleId
                                                      where schedule.Active && daily.Active && projecttask.Active && schedule.ResourceId == userId && (
                                                                    (EntityFunctions.TruncateTime(daily.Date) >= EntityFunctions.TruncateTime(startDate.Date) &&
                                                                    EntityFunctions.TruncateTime(daily.Date) <= EntityFunctions.TruncateTime(endDate.Date))
                                                                    )
                                                      orderby schedule.StartDate
                                                      select new
                                                      {
    
                                                          ProjectTask = projecttask,
                                                          ProjectTaskSchedule = from projecttaskschedule in projecttask.ProjectTaskSchedule.Where(t => t.Active && t.ResourceId == userId)
                                                                                select new
                                                                                {
                                                                                    ProjectTaskSchedule = projecttaskschedule,
                                                                                    DailyStatus = projecttaskschedule.DailyStatus.Where(t => t.Active),
    
                                                                                },
                                                          CritiCality = from cr in db.CritiCality.Where(ts => ts.ProjectTaskId == projecttask.ProjectTaskId) select cr,
                                                          MMDetails = from mm in db.MMDetails.Where(ts => ts.ProjectTaskId == projecttask.ProjectTaskId) select mm,
                                                          MajorMilestone = from pt in db.ProjectTask.Where(ts => ts.ProjectTaskId == projecttask.ProjectTaskId)
                                                                          join mm in db.MMDetails on pt.ProjectTaskId equals mm.ProjectTaskId
                                                                          join mmd in db.MajorMilestone on mm.MajorMilestoneId equals mmd.ProjectTaskId
                                                                           select mmd,
    
                                                                                                            }
    
                                    };

    Friday, December 6, 2019 9:08 AM
  • IMO, you'll be better off just using T-SQL in a sproc for the entire thing and execute the sproc using the EF backdoor and a datareader,  populating a custom object like a DTO or a POCO and loading the objects into a List<T>.

    Sometimes, Linq is not the right path to take. 

    Saturday, December 7, 2019 1:23 PM
  • I can't change my method structure. It was previously implemented method now we are adding extra entity i.e, MajorMileStone... to my existing method(New User Story).
    Tuesday, December 10, 2019 5:39 AM
  • Hi Radhakishan,
    LINQ to Entities requires the entire LINQ query expression to be converted to a server query, and doesn't support arbitrary method calls without known conversions.
    So you need to merge the subquery into the main query instead of calling the method directly from the LINQ query.
    More details you can refer to Marcus Mangelsdorf's answer.
    Best Regards,
    Daniel Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, December 11, 2019 8:08 AM