dynamic join based on where expression - linq/c#

Question

I have a sp which builds a dynamic sql query based on my input params. I tried replicating in linq and somehow it seems incorrect.

My linq:

  var result = from R in db.Committees.Where(committeeWhere)
           join C in db.Employees.Where(employeeWhere) on R.PID equals C.PID
           join K in db.CommitteeTypes.Where(committeesWhere) on R.PID equals K.PID
           select new { R };

The 3 input params i have are: 1. Committee ID and/or

1. Employee ID and/or

2. Committee Type ID

Based on this, i want to be able to make the joins in my linq.

Note: i had to change table names and column names so please do not give thought on the names.

Sql snippet:

IF @committeeID is not null

set @wherestr = @wherestr + 'Committees.committeeID like' + @committeeID + @andstr

//...

IF  len(@wherestr) > 6      


  SELECT  @qrystr = @selectstr + @fromstr + left(@wherestr, len(@wherestr)-3) + ' ORDER BY Committees.committeeID DESC 

EXEC (@qrystr)

Answer 1

Why do you need to use dynamic SQL? Wouldn't this work?

IQueryable<Committee> GetCommittees(int? committeeID, int? employeeID, int? committeeTypeID)
{
    var result = from R in db.Committees.Where(c => committeeID == null || committeeID == c.ID)
                 join C in db.Employees.Where(e => employeedID == null || employeeID == e.ID)
                   on R.PID equals C.PID
                 join K in db.CommitteeTypes.Where(c => committeeTypeID == null || committeeTypeID == c.ID)
                   on R.PID equals K.PID
                 select R;
}

If that won't work, you can use different predicate expressions depending on your parameters:

Expression<Func<Committee, bool>> committeeWhere;
if(committeeID.HasValue)
{
    int id = committeeID.Value;
    committeeWhere = c => c.ID == id;
}
else
{
    committeeWhere = c => true;
}
// etc

Update: Seeing your last comment, maybe you want something like this:

IQueryable<Committee> GetCommittees(int? committeeID, int? employeeID, int? committeeTypeID)
{
    var result = db.Committees.Select(c => c);

    if(committeeID.HasValue)
    {
        result = result.Where(c => c.ID = committeeID);
    }
    else if(employeeID.HasValue)
    {
        result = from R in result
                 join C in db.Employees.Where(e => employeeID == e.ID)
                   on R.PID equals C.PID
                 select R;
    }
    else if(committeeTypeID.HasValue)
    {
        result = from R in result
                 join K in db.CommitteeTypes.Where(ct => committeeTypeID == ct.ID)
                   on R.PID equals K.PID
                 select R;
    }

    return result;
}

Answer 2

If I may improve upon dahlbyk's answer... sometimes joining introduces duplicates. If you really intend to filter - then filter. Also - if you add the relationships in the LinqToSql designer, you'll have properties (such as Committee.Employees) which will be translated for you.

IQueryable<Committee> GetCommittees(int? committeeID, int? employeeID, int? committeeTypeID){
    IQueryable<Committee> result = db.Committees.AsQueryable();
    if(committeeID.HasValue)
    {
        result = result.Where(c => c.ID = committeeID);
    }
    if(employeeID.HasValue)
    {
        result = result
            .Where(committee => committee.Employees
                .Any(e => employeeID == e.ID)
            );
    }
    if(committeeTypeID.HasValue)
    {
        result = result
            .Where(committee => committee.CommitteeTypes
                .Any(ct => committeeTypeID == ct.ID)
            );
    }
    return result;
}