Join query creating problems in Linq to SQL

Question

I have SQL query as:

SELECT b.firstName + ' ' + b.lastName AS Name, a.*
FROM   EH_PP_TeacherObservations AS a INNER JOIN
       account AS b ON a.EH_PP_TeacherAcctId = b.id LEFT JOIN
       EH_PP_ObserverStatus AS c ON c.EH_PP_AcctId = b.id
WHERE  a.EH_PP_TOSRT_TeacherObservationStatusIDEH = '0B823C51-EEAE-4490-B0EC-C1F0B1206AEB' AND 
       c.EH_PP_O_isObserver = 1

I wanted to have same query in linq.

I made it as follows:

List<Entity.account> list = new List<Entity.account>();

                list = (

                    from a in context.EH_PP_TeacherObservations
                    join b in context.accounts on new { EH_PP_TeacherAcctId = a.EH_PP_TeacherAcctId } equals new { EH_PP_TeacherAcctId = b.id }
                    join c in context.EH_PP_ObserverStatus on new { EH_PP_AcctId = b.id } equals new { EH_PP_AcctId = Convert.ToGuid(c.EH_PP_AcctId) } into c_join
                    from c in c_join.DefaultIfEmpty()
                    where
                      a.EH_PP_TOSRT_TeacherObservationStatusIDEH == new Guid("0B823C51-EEAE-4490-B0EC-C1F0B1206AEB") &&
                      c.EH_PP_O_isObserver == true
                    select new
                    {
                        Name = (b.firstName + " " + b.lastName),
                        EH_PP_ObservationID = a.EH_PP_ObservationID,
                        EH_PP_TE_TeacherEvalID = a.EH_PP_TE_TeacherEvalID,
                        EH_PP_TOT_ObservationStartDateTime = a.EH_PP_TOT_ObservationStartDateTime,
                        EH_PP_TOT_ObservationEndDateTime = a.EH_PP_TOT_ObservationEndDateTime,
                        EH_PP_TOT_Announced = a.EH_PP_TOT_Announced,
                        EH_PP_TOT_ObservationNum = a.EH_PP_TOT_ObservationNum,
                        EH_PP_TeacherAcctId = a.EH_PP_TeacherAcctId,
                        EH_PP_ObserverAcctID = a.EH_PP_ObserverAcctID,
                        EH_PP_TOSRT_TeacherObservationStatusIDEH = a.EH_PP_TOSRT_TeacherObservationStatusIDEH
                    }

                    ).ToList<Entity.account>();

But its giving me error on

join b in context.accounts on new { EH_PP_TeacherAcctId = a.EH_PP_TeacherAcctId } equals new { EH_PP_TeacherAcctId = b.id }

error is on 'join' word.

Type of one of expressions in the join clause is incorrect.
Type interface failed to call to 'join'

Please help me.

What is the mistake???

Answer 1

try this

List<Entity.account> list = new List<Entity.account>();


                list = (

                    from a in context.EH_PP_TeacherObservations
                    join b in context.accounts on b.id equals a.EH_PP_TeacherAcctId 
                    join c in context.EH_PP_ObserverStatus on Convert.ToGuid(c.EH_PP_AcctId) equals  b.id into c_join
                    from c in c_join.DefaultIfEmpty()
                    where
                      a.EH_PP_TOSRT_TeacherObservationStatusIDEH == new Guid("0B823C51-EEAE-4490-B0EC-C1F0B1206AEB") &&
                      c.EH_PP_O_isObserver == true
                    select new
                    {
                        Name = (b.firstName + " " + b.lastName),
                        EH_PP_ObservationID = a.EH_PP_ObservationID,
                        EH_PP_TE_TeacherEvalID = a.EH_PP_TE_TeacherEvalID,
                        EH_PP_TOT_ObservationStartDateTime = a.EH_PP_TOT_ObservationStartDateTime,
                        EH_PP_TOT_ObservationEndDateTime = a.EH_PP_TOT_ObservationEndDateTime,
                        EH_PP_TOT_Announced = a.EH_PP_TOT_Announced,
                        EH_PP_TOT_ObservationNum = a.EH_PP_TOT_ObservationNum,
                        EH_PP_TeacherAcctId = a.EH_PP_TeacherAcctId,
                        EH_PP_ObserverAcctID = a.EH_PP_ObserverAcctID,
                        EH_PP_TOSRT_TeacherObservationStatusIDEH = a.EH_PP_TOSRT_TeacherObservationStatusIDEH
                    }

                    ).ToList<Entity.account>();