views:

262

answers:

3

I'm trying to only return a few columns from a linq to sql query but if I do, it throws the exception:

Explicit construction of entity type 'InVision.Data.Employee' in query is not allowed

Here's the code:

return db.Employees.Select(e => new Employee()
       { EmployeeID = e.EmployeeID, FirstName = e.FirstName,
         LastName = e.LastName }).ToList();

If I return everything then it will throw exceptions about circular references because it needs to be serialized to be used in javascript, so I really need to limit the columns... Thanks for any tips you can give me to solve this.

A: 

Justin,

Explanation of that error here: http://devlicio.us/blogs/derik_whittaker/archive/2008/04/25/linq2sql-explicit-construction-of-entity-exception.aspx

One thing you can do is create a class that derrives off of Employee.

public class EmployeeProxy : Employee
{
}

then you can do your query:

return db.Employees.Select(e => new EmployeeProxy { EmployeeID=e.EmployeeID, FirstName=e.FirstName, LastName=e.LastName }).ToList();

On the other hand, if you only need to serialize the result set to javascript, then you can also just use anonymous classes. This works as well:

return db.Employees.Select(e => new { EmployeeID=e.EmployeeID, FirstName=e.FirstName, LastName=e.LastName }).ToList();

Hope this helps.

Linus
+2  A: 

Because I've had to fight with Linq2Sql and Serialization before I'd recommend using a View object to handle this scenario rather than a Linq2Sql Entity. Its a much easier solution:

return db.Employees
        .Select( e => new EmployeeView() 
        { 
            EmployeeID = e.EmployeeID, 
            FirstName = e.FirstName, 
            LastName = e.LastName 
         }).ToList();

The other alternative is to drag a new copy of your Employee table into the DBML designer, name it something different like SimpleEmployee, delete all the relationships and remove all the columns you don't need.

jfar
A: 

Basically, if you just want the columns, select those. If you want the employee entity, select it. There's not much of a middle ground here. I recommend against creating a new class just for this. Yuck!

Do this:

return db.Employees
   .Select(e => new { e.EmployeeID, e.FirstName, e.LastName })
   .ToList();
Dave Markle
Thanks, but the function returns List<Employee>, and if you just do new without specifying Employee then it won't compile because the query will return List<AnonymousType#1>. I ended up finding the solution:var query = from e in db.Employees select new { e.EmployeeID, e.LoginName, e.FirstName, e.LastName }; var typedQuery = from e in query.AsEnumerable() select new Employee { EmployeeID = e.EmployeeID, LoginName = e.LoginName, FirstName = e.FirstName, LastName = e.LastName }; return typedQuery.ToList();
Justin
That sort of works. But I still don't like it because the caller of your function most likely expects the full Employee object, and you're not giving it to him. Instead of doing all of this, I suggest returning IQueryable<Employee> and returning the whole Employee object. Because you're returning IQueryable and not a list, execution will be deferred to your caller, who can select what they want in their method...
Dave Markle