Linq to SQL DTOs and composite objects

Question

I am using a similar approach to others in keeping my LINQ objects in my LINQ data provider and returning an IQueryable to allow filtering etc. This works fine for filtering a simple object by it's ID or other property, but I am having a problem with a join table object that is composed of other child objects

    //CoreDBDataContext db = coreDB;
public IQueryable<DTO.Position> GetPositions()    {
     return from p in coreDB.Positions
         select new DTO.Position
             {
             DTO.User = new DTO.User(p.User.id,p.User.username, p.User.firstName,p.User.lastName,p.User.email,p.User.isActive),
             DTO.Role = new DTO.Role(p.Role.id, p.Role.name, p.Role.isActive),
             DTO.OrgUnit = new DTO.OrgUnit(p.OrgUnit.id,p.OrgUnit.name,p.OrgUnit.isActive)
             };

The coreDB.Positions is my Linq Position Object and I am returning a DTO Position which is composed of a User, OrgUnit and Role (the underlying table is a join table with UserID, RoleID, and OrgUnitID)

The problem I am having is that when I try to add a filter on the Iqueryable I get a SQL Error saying that there is no translation available for my DTO.User object

public static IQueryable<Position> WithUserID(this IQueryable<Position> query, int userID)
    {
        return query.Where(p => p.User.ID == userID);
    }

I am at a complete loss as to how to go about resolving this, as all of my Google results seem to be with people working directly with the generated LINQ objects

Any thought as to how to make this work, or am I doing something completely wrong here?

Thanks

Answer 1

I ended up not using filters for my complex queries. Instead, I added methods to the repository for the more complex query requirements. I feel this will make the system easier to understand and hence maintain.