Linq with EF method cannot be translated into a store expression(in repository pattern)

Question

i am trying to access data from multiple model in repository pattern using Ling subquery. but when i try to access data in inner query using .GetQueryable() i receive following error.

ERRORLINQ to Entities does not recognize the method 'System.Collections.Generic.List1[<>f__AnonymousType1702[System.Int32,System.Int32]] ToList[<>f__AnonymousType1702](System.Collections.Generic.IEnumerable1[<>f__AnonymousType170`2[System.Int32,System.Int32]])' method, and this method cannot be translated into a store expression.

var query = (from i in
                                 (from student in _tblStudents.GetQueryable()
                                  join Sections in _tblStudentsSections.GetQueryable() on student.StudentID equals Sections.StudentID
                                  join Class in _tblClasses.GetQueryable() on Sections.ClassID equals Class.ClassID
                                  join Program in _tblPrograms.GetQueryable() on Class.ProgramID equals Program.ProgramID
                                  //where student.IsForeign == false
                                  select new
                                  {
                                      ProgramID = Program.ProgramID,
                                      program = Program.ProgramName,
                                      ClassIDs = Sections.ClassID,
                                      TotalSeats = Program.NoOfAdmissions,
                                      IsForeign = student.IsForeign
                                  })

                             group i by new { i.ProgramID, i.IsForeign, i.TotalSeats, i.program } into grp
                             select new AdmissionSummaryReportModel
                             {
                                 program = grp.Key.program,
                                 TotalSeats = grp.Key.TotalSeats,
                                 //SeatsFilled = grp.Select(m => m.ClassIDs).Count(),
                                 AvailableForeignSeats = 22,
                                 SeatsFilled = (int)(from student in _tblStudents.GetQueryable()
                                                            join StudentSections in _tblStudentsSections.GetQueryable() on student.StudentID equals StudentSections.StudentID
                                                            join Class in _tblClasses.GetQueryable() on StudentSections.ClassID equals Class.ClassID
                                                            join Program in _tblPrograms.GetQueryable() on Class.ProgramID equals Program.ProgramID
                                                     where student.IsForeign == false && Program.ProgramID == grp.Key.ProgramID
                                                            select new
                                                            {
                                                                StudentSections.ClassID
                                                            }).ToList().Count(),

                                 ForeignSeatsFilled = (int)(from student in _tblStudents.GetQueryable()
                                                            join StudentSections in _tblStudentsSections.GetQueryable() on student.StudentID equals StudentSections.StudentID
                                                            join Class in _tblClasses.GetQueryable() on StudentSections.ClassID equals Class.ClassID
                                                            join Program in _tblPrograms.GetQueryable() on Class.ProgramID equals Program.ProgramID
                                                            where student.IsForeign && Program.ProgramID == grp.Key.ProgramID
                                                            select new
                                                            {
                                                                StudentSections.ClassID
                                                            }).ToList().Count()

                             }).ToList();

how to overcome this error with .GetQueryable() or provide me any alternative mean

Answer 1

The problem is using the ToList function in your LINQ query, that is not what you want to do, since that cannot be translated to a proper SQL query. You want to use ToList only outside the actual LINQ query. To get the count inside, use the LINQ Count function instead, for instance :

select new                                                       
{

StudentSections.ClassID

}).Count()

Answer 2

Queryable gets translated into sql query at runtime, in the subquery you are parsing with int which is known to c# compiler not to query translation. Either user IEnumerable or remove int as count will return the int.