Linq filtering an IQueryable<T> (System.Data.Linq.DataQuery) object by a List<T> (System.Collection.Generic.List) object?

Question

My IQueryable line is:

 // find all timesheets for this period - from db so System.Data.Linq.DataQuery
 var timesheets = _timesheetRepository.FindByPeriod(dte1, dte2);

My List line is:

 // get my team from AD - from active directory so System.Collection.Generic.List
 var adUsers = _adUserRepository.GetMyTeam(User.Identity.Name);

I wish to only show timesheets for those users in the timesheet collection that are present in the user collection.

If I use a standard c# expression such as:

 var teamsheets = from t in timesheets
                  join user in adUsers on t.User1.username equals user.fullname
                  select t;

I get the error "An IQueryable that returns a self-referencing Constant expression is not supported"

Any recommendations?

Answer 1

The join construct won't work, but you can use the Contains approach. This next snippet might do the trick.

string[] usernames = adUsers.Select(u => u.fullname).ToArray();

var teamsheets =
    from t in timesheets
    where usernames.Contains(t.User1.username)
    select t;

Answer 2

Linq to Sql will try to generate a sql query to represent the whole expression. This means that it will try to pass in the collection of ad users as parameters to a sql query. If there are too many users the query will hit the parameter limit (2100) and it cannot be executed. If there are not that many users in a team, then you can use a contains expression which will be converted to an "IN" expression in sql.

This is what Steven has suggested:

string[] usernames = adUsers.Select(u => u.fullname).ToArray();

var teamsheets =
from t in timesheets
where usernames.Contains(t.User1.username)
select t;

The only way to use a join in the way that you have attempted would be to retrieve all of the timesheets from the database into memory (using ToList on the timesheets var), and then the join will happen in memory. If this performs better than using Contains, or you cannot use Contains because of team sizes, this might be worth considering.