LINQ Error when using multiple JOIN in same statement on CRM 2011 Plug-in

Question

Hi I am trying to join multiple entities in CRM 2011 but I get and error saying: {"'xrmsm_sessionEnrollments' entity doesn't contain attribute with Name = 'xrmsm_termsid'."}. That is correct but I am joining the entity that have that attribute.

My Linq Query:

var query2 = from e in svsContext.xrmsm_sessionEnrollmentsSet

        join s in svsContext.xrmsm_sessionsSet on e.xrmsm_SessionLookup.Id equals s.xrmsm_sessionsId
        join ic in svsContext.xrmsm_institutionCoursesSet on s.xrmsm_institutionCourseLookup.Id equals ic.xrmsm_institutionCoursesId
        join ts in svsContext.xrmsm_term_sessionsSet on e.xrmsm_termSessionLookup.Id equals ts.xrmsm_term_sessionsId
        join t in svsContext.xrmsm_termsSet on ts.xrmsm_TermLookup.Id equals t.xrmsm_termsId
        where (e.xrmsm_StudentLookup.Equals(studentlookup)
               && e.xrmsm_YearLookup.Equals(entity.GetAttributeValue("xrmsm_studentlookup"))
               && ic.xrmsm_institutionCoursesId == institutionCourseGuid
               && t.xrmsm_termsId == termGuid)
        select new { sessionName = s.xrmsm_sessionsName, StudentName = e.xrmsm_studentsName, StudentId = e.xrmsm_StudentLookup.Name };

My original SQL query that works on SQL Server:

SELECT en.xrmsm_currentsessionenrollmentsname
      ,en.xrmsm_isreadonlyname
      ,en.xrmsm_sessionenrollmentsaverage
      ,en.xrmsm_sessionenrollmentsgrade
      ,en.xrmsm_sessionenrollmentsid
      ,en.xrmsm_sessionenrollments_id
      ,en.xrmsm_sessionlookup as sessionid
      ,en.xrmsm_sessionlookupname
      ,en.xrmsm_sessionsname
      ,en.xrmsm_studentlookup AS studentid
      ,en.xrmsm_studentlookupname
      ,en.xrmsm_studentsname
      ,en.xrmsm_termsessionlookup
      ,en.xrmsm_termsessionlookupname
      ,en.xrmsm_withdrawal
      ,en.xrmsm_yearaverage
      ,en.xrmsm_yeargrade
      ,en.xrmsm_yearlookup
      ,en.xrmsm_yearlookupname
  FROM CoseyTest_MSCRM.dbo.Filteredxrmsm_sessionEnrollments as en INNER JOIN 
  CoseyTest_MSCRM.dbo.Filteredxrmsm_sessions crmsessions ON 
  (en.xrmsm_sessionlookup = crmsessions.xrmsm_sessionsid AND en.xrmsm_yearlookup = crmsessions.xrmsm_yearlookup)
  INNER JOIN Filteredxrmsm_institutionCourses institutionCourses
  on crmsessions.xrmsm_institutioncourselookup = institutionCourses.xrmsm_institutioncoursesid
  Inner Join Filteredxrmsm_term_sessions as termsession
  on en.xrmsm_termsessionlookup = termsession.xrmsm_term_sessionsid
  Inner Join Filteredxrmsm_terms as terms
  on termsession.xrmsm_termlookup = terms.xrmsm_termsid 
  where en.xrmsm_yearlookup = '4BA07BED-3F51-E211-8359-00155D004403' 
  and en.xrmsm_studentlookup = 'C844AF65-5B51-E211-8359-00155D004403'
  and terms.xrmsm_termsid = 'D1D107B7-4551-E211-8359-00155D004403' 
  and institutionCourses.xrmsm_institutioncoursesid = '2121914E-4551-E211-8359-00155D004403'

Answer 1

Thanks for all your responses. I did the following workaround.

1. Make a query that retrieve a list of all the Student enrollments

2. Use the Find method of the List<T> class to find if a record exist with the conditions I am searching for.

    var query2 = (from e in svsContext.xrmsm_sessionEnrollmentsSet
                    join s in svsContext.xrmsm_sessionsSet on e.xrmsm_SessionLookup.Id equals s.xrmsm_sessionsId
                    join ic in svsContext.xrmsm_institutionCoursesSet on s.xrmsm_institutionCourseLookup.Id equals ic.xrmsm_institutionCoursesId
                    join ts in svsContext.xrmsm_term_sessionsSet on e.xrmsm_termSessionLookup.Id equals ts.xrmsm_term_sessionsId
                    join t in svsContext.xrmsm_termsSet on ts.xrmsm_TermLookup.Id equals t.xrmsm_termsId
                    where (e.xrmsm_StudentLookup.Equals(studentlookup))
                    select new
                    {
                        EnrollmentId = e.xrmsm_sessionEnrollments_id,
                        SessionId = s.xrmsm_sessions_id,
                        EnrollmentYear = e.xrmsm_YearLookup,
                        InstitutionCourseId = (Guid)ic.xrmsm_institutionCoursesId,
                        TermId = (Guid)t.xrmsm_termsId,
                        Student = e.xrmsm_StudentLookup
                    }).ToList();
   
   
       var q = query2.Find(r => r.EnrollmentYear.Id == entity.GetAttributeValue<EntityReference>("xrmsm_yearlookup").Id
                            && r.InstitutionCourseId == institutionCourseGuid
                            && r.TermId == termGuid);