Explicit construction of entity type '###' in query is not allowed.

linq-to-sql

Entities can be created outside of queries and inserted into the data store using a DataContext. You can then retrieve them using queries. However, you can't create entities as part of a query.

I am finding this limitation to be very annoying, and going against the common trend of not using SELECT * in queries.

Still with c# anonymous types there is a workaround, by fetching the objects into an anonymous type, and then copy it over into the correct type.

For example:

var q = from emp in employees where emp.ID !=0
select new {Name = emp.First + " " + emp.Last, EmployeeId = emp.ID }
var r = q.ToList();
List<User> users = new List<User>(r.Select(new User
   {
        Name = r.Name,
        EmployeeId = r.EmployeeId 
   }));

And in the case when we deal with a single value (as in the situation described in the question) it is even easier, and we just need to copy directly the values:

var q = from emp in employees where emp.ID !=0 
select new { Name = emp.First + " " + emp.Last, EmployeeId = emp.ID }
var r = q.FirstOrDefault();
User user = new User { Name = r.Name, EmployeeId = r.ID };

If the name of the properties match the database columns we can do it even simpler in the query, by doing select

var q = from emp in employees where emp.ID !=0 
select new { emp.First, emp.Last, emp.ID }

One might go ahead and write a lambda expression that can copy automatically based on the property name, without needing to specify the values explictly.

Explicit construction of entity type '###' in query is not allowed.

Related

Recent Posts