2

I am trying to query the 3rd level table ef_staff table 3 times to get 3 diff staff objects for each row. How to translate this in LINQ? 

SELECT a.a_appraisalid, a.a_year, c.s_staffName, c2.s_staffName, c3.s_staffName
FROM   ef_appraisal a, idp_application b, ef_staff c, ef_staff c2, ef_staff c3
WHERE  a.a_appraisalid = b.a_appraisalid AND
       a.a_staffid = c.s_staffid AND
       a.a_appraisedby = c2.s_staffid AND
       a.a_reviewedby = c3.s_staffid

I have been trying many ways but there is still an error 'Type Inference Failed' in the 2nd & 3rd joining of Staff. What am I missing here?

from application in applications

join appraisal in pmsEntities.ef_appraisal on application.a_appraisalid equals appraisal.a_appraisalid

join staff in pmsEntities.ef_staff on appraisal.a_staffid equals staff.s_staffid

join appraiser in pmsEntities.ef_staff on staff.s_appraisedby equals appraiser.s_staffid into ap

from appraiser in ap.DefaultIfEmpty() 

join reviewer in pmsEntities.ef_staff on staff.s_reviewedby equals reviewer.s_staffid into rv

from reviewer in rv.DefaultIfEmpty() 

join company in pmsEntities.ef_company on appraisal.a_companyid equals company.c_companyid into jc

from company in jc.DefaultIfEmpty()

select appraisal, staff.staffName, appraiser.staffName, reviewer.staffName, company.compName
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user1782661
  • 61
  • 5
  • 2
    Can you format your queries to make them readable by humans? – El Ronnoco Oct 31 '12 at 12:01
  • Is there a reason you are using hash joins rather than normal joins ? – Mark Broadhurst Oct 31 '12 at 12:06
  • No reason, the joins are used by the previous author. – user1782661 Oct 31 '12 at 12:13
  • What is the datatype of your columns, specifically the columns which you use in the joins? And are you going to extend your query? Since you only use columns from table ef_appraisal in your result-set, you can change you query to use WHERE IN () to eliminate the joins. – Maarten Oct 31 '12 at 12:21
  • I am using all tables joined, the joining columns are ids so it's either int or string. – user1782661 Nov 01 '12 at 02:45
  • Any advise on the Linq query? Only way I could think of is do 2 separate queries for each row. – user1782661 Nov 01 '12 at 03:57
  • Which would disallow me to filter the query by staff's appraiser and reviewer. I have tried using enumeration filer which has performance issue for big volume. – user1782661 Nov 01 '12 at 04:05

1 Answers1

1

I fixed the mistake. The first level joining of staff object should be linked to the 2nd level of staff object as below:

join staff in pmsEntities.ef_staff on appraisal.a_staffid equals staff.s_staffid into staffj

                    from staff1 in staffj

                    join appraiser in pmsEntities.ef_staff on staff1.s_appraisedby equals appraiser.s_icno into staff2

`

Hope it helps

user1782661
  • 61
  • 5