none
Linq To Entities Query Executes same sql statement multiple times RRS feed

  • Question

  • Good Day,
    I'm using AnjLab.SqlProfiler to check the sql executed by my linq query. I found that the Query is running the same SQL Query Multiple Times(once for each row in db it seems). Can anyone explain to me why this is happening. Using EF4
     
                var query = from p in Entities.People.OfType<Data.Student>()
                                      join e in Entities.Enrolments on p.Id equals e.StudentId
                                        where p.Deleted == showdeleted
                                           && e.Deleted == showdeleted
                                           && e.AcademicYearId == year
                                        select new
                                        {
                                            p.Id,
                                            p.Surname,
                                            p.MaidenName,
                                            p.FullNames,
                                            Title = p.TitleId,
                                            p.Initials,
                                            p.PreferredName,
                                            p.IdNumber,
                                            p.PassportNumber,
                                            p.BirthDate,
                                            p.AdmissionNumber,
                                            p.AdmissionDate,
                                            Family=p.FamilyId,
                                            Grade=e.StudentGradeId,
                                            Class=e.StudentClassId,
                                            Sportshouse=e.SportsHouseId,
                                            ResidenceType = e.ResidenceTypeId,
                                            p.VulnerabilityAlert,
                                            Gender = p.GenderId,
                                            MaritalStatus = p.MaritalStatusId,
                                            Religion = p.ReligionId,
                                            EthnicGroup = p.EthnicGroupId,
                                            p.DoctorName,
                                            p.DoctorNumber,
                                            MedicalAidScheme = p.MedicalAidSchemeId,
                                            p.MedicalAidOption,
                                            p.MedicalAidMember,
                                            p.MedicalAidNumber,
                                            p.IsImmigrant,
                                            p.ImmigrationDate,
                                            p.VisaType,
                                            p.VisaExpiryDate,
                                            p.EmailNotification,
                                            p.SmsNotification,
                                            p.ParentsDeceasedStatusId,
                                            PreGradeRType = p.PreGradeRTypeId,
                                            PreGrade1Type = p.PreGrade1TypeId,
                                            p.PreviousSchoolName,
                                            p.PreviousSchoolHighestGrade,
                                            p.PreviousSchoolHighestGradeYear,
                                            p.PreviousSchoolLastResult,
                                            p.YearsInGrade,
                                            p.YearsInPhase,
                                            p.IsFirstRegistrationInProvince,
                                            p.InSchoolLastYear,
                                            p.ExaminationNumber,
                                            p.LSENNumber,
                                            p.LSENWeight,
                                            p.SNEInclusionId,
                                            DeactivateReason = p.StudentDeactivateReasonId,
                                            CauseOfDeath = p.CauseOfDeathId,
                                            p.OfficialCode,
                                            p.CreateUser,
                                            p.CreateDate,
                                            p.ChangeUser,
                                            p.ChangeDate,
                                            p.Deleted,
                                            p.DeleteUser,
                                            p.DeleteDate
                                        };
                IList list = query.ToList();
    Tuesday, September 13, 2011 7:10 AM

Answers

  • Ok, I seem to have found the problem. The Student table contains a computed field. If I remove this from the query it only executes the backend SQL once? Go figure...

    • Marked as answer by Jaco Roux Wednesday, September 14, 2011 6:46 AM
    Wednesday, September 14, 2011 6:45 AM

All replies

  • On 9/13/2011 3:10 AM, Jaco Roux wrote:
    > Good Day,
    > I'm using AnjLab.SqlProfiler to check the sql executed by my linq query.
    > I found that the Query is running the same SQL Query Multiple Times(once
    > for each row in db it seems). Can anyone explain to me why this is
    > happening.
     
    > IList list = query.ToList();
     
    That query.ToList() I suspect is making it go back to the database
    through each iteration through the List. You need to change the way you
    are doing the query and make it stop doing it. 'query' is still
    connected to the database.
     
    Tuesday, September 13, 2011 4:53 PM
  • Ok, I seem to have found the problem. The Student table contains a computed field. If I remove this from the query it only executes the backend SQL once? Go figure...

    • Marked as answer by Jaco Roux Wednesday, September 14, 2011 6:46 AM
    Wednesday, September 14, 2011 6:45 AM
  • So really, the use of EF has nothing to do with the cause--a regular select statement in SSMS on a table with a computed column would show the same thing in the profiler.

    • Edited by Justin Stolle Monday, October 17, 2011 10:49 PM Answered my own question
    Monday, October 17, 2011 9:07 PM