Entity Navigation Property IQueryable cannot be tr

Question

im using Entity Framework designer first and I need to create custom Model Objects starting from the db objects. I don't want to use IEnumerable cause it will query too many fields.

The goal is to remove the inner select within this function:

using (var db = new dbEntities())
{
   var departments= db.departments
                      .Include(p => p.employee)
                      .Where(...)
                      .Select(p => new CustomDepartmentModel()
                      {
                         ID = p.ID,
                         Employees = p.employee
                                .Select(q => new CustomEmployeeModel()
                                {
                                    ID = q.ID,
                                    Name= q.Name
                                }).ToList()
                      });
   return departments.ToList();
}

by using this function:

public static IQueryable<CustomEmployeeModel> ToModel(this IQueryable<employee> Employee)
    {
        return Employee.Select(u => new CustomEmployeeModel()
        {
            ID = u.ID,
            Name = u.Name
        });
    }

But I always get the error: "LINQ to Entities does not recognize the method ToModel".

I did try to use it in these ways without luck:

Employees = p.employee.AsQueryable().ToModel().ToList() //1
Employees = db.Entry(p).Collection(f => f.employee).Query().ToModel().ToList() //2

I think that I need to use something like this:

public static System.Linq.Expressions.Expression<Func<IQueryable<employee>, IQueryable<CustomEmployeeModel>>> ToModel()
    {
        return p => p.Select(u => new CustomEmployeeModel()
        {
            ID = u.ID,
            Name = u.Name
        });
    }

but I really can't figure out how to use it.

Answer 1

I think that I need to use something like this: [snip] but I really can't figure out how to use it.

That's exactly what you need, but you also need LINQKit to make the query work. With it, your code would look like this:

var toModel = ToModel();

var departments2 = db.departments
    .AsExpandable()
    .Include(p => p.employee)
    .Where(p => true)
    .Select(p => new CustomDepartmentModel()
{
    ID = p.ID,
    Employees = toModel.Invoke(p.employee).ToList()
});

Answer 2

The problem is that LINQ to Entities is trying to translate your ToModel() method into a SQL query (since that's what LINQ to Entities is supposed to do), and it can't find a way to do it, hence the error that you're seeing.

In order for you to call ToModel() you'll need to have the information already come in from the database, which will then make any LINQ query a LINQ to Objects query, which will be more than able to do what you are asking for. You can do this by calling ToList() before calling ToModel().