CODEWITHSUNDEEP
joinlinq-to-entities
Duk
Duk

Reputation: 927

join multiple tables using linq query and orderby the tables

I have the 4 tables. i want to create linq query for using joins and fetching the data by who are all active employers. my tables are,

1. Jobs

 EmployerId

2. Employerregistrationddetails

  Employerid

  planid

  amount

3. EmployerPlans

  planid

  alerts

4. AlertDones

  employerid

  alertssent

In the Jobs table employerid equal to *Emp_Reg_Details.employerId* and *emp_reg_details.planId* equals to employerplans.planId and Employerplans.alerts equals alertdones.alertssent means it will order first the above conditions then the rest of the jobs will order..

i put linq query for this.. but it is not exactly working.. My query is

 return (from job in _db.Jobs 
         join employerregdetails in _db.EmployerRegistrationDetails 
             on job.OrganizationId equals employerregdetails.EmployerId into e
         join ep in _db.EmployerPlans 
             on emp.PlanId equals ep.EmployerPlanId
         join alr in _db.AlertsDones 
             on ep.Alerts equals alr.AlertsSent
         from emp in e.DefaultIfEmpty()

         orderby job.OrganizationId != -1 descending, 
                 job.OrganizationId != null descending
         orderby job.OrganizationId != -1 descending, 
                 alr.EmployerId == job.OrganizationId descending, 
                 job.CreatedDate descending
         select job);

If i use the above query i have an error emp.PlanId. It is not taking the emp variable.. I did any mistake please clarify me?

Upvotes: 0

Views: 795

Answers (1)

Amir Sherafatian
Amir Sherafatian

Reputation: 2083

you can not see the emp variable because it has defined after line you want to refer that, you need to define emp before this line:

join ep in _db.EmployerPlans on emp.PlanId equals ep.EmployerPlanId

like this:

return (from job in _db.Jobs 
        join employerregdetails in _db.EmployerRegistrationDetails 
            on job.OrganizationId equals employerregdetails.EmployerId into e
        from emp in e.DefaultIfEmpty()
        join ep in _db.EmployerPlans on emp.PlanId equals ep.EmployerPlanId

but notice, you maybe get error when a jobs does not have any EmployerRegistrationDetails, because you try to use left join, and in this case emp is null

Upvotes: 2

Related Questions