Linq to SQL DTO and Composite Objects

Question

I take a similar approach for others, storing my LINQ objects in my LINQ data provider and returning IQueryable to allow filtering, etc. This works great for filtering a simple object by its id or other property, but I have a problem with a connection table object made up 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)
             };

CoreDB.Positions is my Linq position object, and I am returning a DTO position consisting of User, OrgUnit and Role (the base table is the connection table with UserID, RoleID and OrgUnitID).

The problem I am facing is that when I try to add a filter to Iqueryable, I get an SQL error saying that there is no translation for my DTO.User object.

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

, , Google, , , LINQ

, , - ?

Answer 1

. . , , , .

Answer 2

Linq2SQL . , , .

, Linq Linq2SQL, SQL, , . DTO Linq2SQL, Linq2SQL , SQL.

, Linq2SQL DTO.

, :

: Expression<>, a IQueryable<>. .

public IEnumerable<DTO.Position> FindPositions(Expression<Func<Position, bool>> criteria)
{
    return from p in coreDB.Positions
           where criteria.Invoke(p)
           select new DTO.Position
                      {
                          User = new DTO.User(p.User.id, p.User.username, p.User.firstName, p.User.lastName,
                                       p.User.email, p.User.isActive),
                          Role = new DTO.Role(p.Role.id, p.Role.name, p.Role.isActive),
                          OrgUnit = new DTO.OrgUnit(p.OrgUnit.id, p.OrgUnit.name, p.OrgUnit.isActive)
                      };
}

Answer 3

:

var courses = from c in Map(origCourses)
where !expiredStatuses.Contains(c.Status)
select c;

:

    select new UserCourseListItem
    {
        CourseID = c.CourseID,
        CourseName = cm.CourseName,
        CourseType = c.CourseType.Value
        ...

( ).

Ps. , expiredStatuses .

Update 1: . This is similar to a similar scenario because:

• The map returns an IQueryable, which is a POCO object.
• After calling the Map method, which returns an IQueryable with a POCO object, I apply a filter to it.