How to specify which columns can be returned from linq to sql query

Question

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.

I don't think there are any strong arguments for not returning the entire Employee object. Performance is certainly not one. Linq will take care of not returning any associations unless they are accessed. — cdonner
– cdonner, Commented Sep 24, 2009 at 18:41
@cdonner I suppose it would be appropriate to return all columns when the following four conditions are true: A) You have SSD drives for your SQL instances, and B) Have 10GBps connections to SQL SAN units, and C) Have twice the memory on all your SQL servers and application servers, and D) Have more SQL core licenses than you know what to do with. — Brain2000
– Brain2000, Commented Apr 26, 2020 at 22:57

John Farrell · Accepted Answer · 2009-06-19 01:26:34Z

3

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.

answered Jun 19, 2009 at 1:26

John Farrell

24.7k10 gold badges82 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave Markle · Accepted Answer · 2009-06-19 01:30:33Z

2

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();

answered Jun 19, 2009 at 1:30

Dave Markle

98.3k20 gold badges152 silver badges172 bronze badges

3 Comments

Justin Over a year ago

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();

Dave Markle Over a year ago

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...

Brain2000 Over a year ago

Can this be specified dynamically, such as with a comma separated string?

Linus · Accepted Answer · 2009-06-19 01:32:43Z

0

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.

edited Jun 19, 2009 at 1:32

answered Jun 19, 2009 at 1:26

Linus

1,2561 gold badge12 silver badges23 bronze badges

Collectives™ on Stack Overflow

How to specify which columns can be returned from linq to sql query

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related