CODEWITHSUNDEEP
c#linqmaster-detail
omarmallat
omarmallat

Reputation: 734

select master based on detail in LINQ

I have ServiceRequest table, where it contains a field to specify to location (the branch), and the type of the Support(IT issue, fleet issue, Maintenance...)

ServiceRequest: { srID(PK), srDetail, LocationID(FK), SupportID(FK), StatusID(FK), ServiceDate }

then I have Supervisor table, where it define the list of location, and the type of issues he's supervising:

Supervisor: { UserID(FK), LocationID(FK), SupportID(FK) }

what is the query that I can use so when a supervisor login, I want to show him all service requests that belong to the locations and the support types he covers.

currently, I am getting all (active) service requests using:

short statusId = short.Parse(ddlStatus.Value);
return db.ServiceRequests.Where(sr => sr.StatusID == statusId)
         .OrderByDescending(s=>s.ServiceDate).ToList();

Upvotes: 0

Views: 265

Answers (1)

jayanta
jayanta

Reputation: 163

You can try this

       short statusId = short.Parse(ddlStatus.Value);

        int userId= 1;// keep supervisor Id

        var  serviceRequest =  (from  e in db.Supervisor where e.UserID==userId    
                                join f in db.ServiceRequests on new {e.LocationID, e.SupportID} 
                                equals new {f.LocationID, f.SupportID}
                                where f.StatusID == statusId
                                select f 
                              ).OrderByDescending(s=>s.ServiceDate).ToList();

      return serviceRequest ;

Upvotes: 3

Related Questions