There has to be a better way to add this clause in linq

Question

var result = from R in db.Clients.Where(clientWhere)    
             join RA in db.ClientAgencies on R.SysID equals RA.SysID
             join A in db.Agencies.Where(agencyWhere) on RA.AgencyID equals A.AgencyID
             join AC in db.AdCommittees on A.AgencyID equals AC.AgencyID into temp
             from x in temp.DefaultIfEmpty().Distinct()
             select new {R,RA,x};

If user enters CommitteeID this is what I do, but I feel there has to be a better way.

 var query = (from R in result
              where R.x.CommitteeID == params.CommitteeID 
              select R.R).Distinct();
  return query;

Is there a better way?

Answer 1

How are you using the data. The joins could be hurting you depending on what you're trying to achieve (which is very difficult for us to view without context of your data structures).

I can't fault the linq other than to say that you appear to have a log of data being joined which you may or may not need.

The other problem I have is that you will execute the query when you call DefaultIfEmpty(). This means to do your filter you may hit the database again to calculate it's result.

Could you provide some info on your DB Schema and what you are trying to get from your query?

Answer 2

If you're not using your intermediate query for anything else, I would flip it (filter by committeeID first):

Client GetCommitteeClient(int committeeID)
{
    return (
        from AC in db.AdCommittees
        where AC.CommitteeID == committeeID 
        join A in db.Agencies.Where(agencyWhere) on AC.AgencyID equals A.AgencyID
        join RA in db.ClientAgencies on A.AgencyID equals RA.AgencyID
        join R in db.Clients.Where(clientWhere) on RA.SysID equals R.SysID
        select R
        ).SingleOrDefault();
}