0

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???

1
  • no...no one of them is nullable Commented May 30, 2014 at 11:10

1 Answer 1

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>();
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.