Linq to Sql - Explicit construction of entity type '###' in query is not allowed

Hi

We are busy building a big project with various layers. We are starting to rely more and more on anonymous types(which cannot be returned from a method.) In most cases we are returning IQueryable instead of IQueryable<Comment> or List<Comment>. Take the following example:

var p = from c in Context.Comment
select c;

vs

var p = from c in Context.Comment
select new Comment{
commentID=c.CommentID,
commentText=c.commentText
};

Why is it with linq to sql that you cannot specify which columns you would like of a table. if the second example is run “Explicit construction of entity type ‘###’ in query is not allowed” .

The first query is fine,IF the table doesnt have many columns. But this can become a problem with joins and TEXT fields that are returned that isnt necessary. I know of lazy loading fields,but that is hardly a solution,because of the extra queries that are run once the fields are required.

I know there are ways of returning the var query as type object,and then using reflection to get the values. But surely that is not the answer?

I have also seen that if you make a class that inherits from Comment,lets say CommentCopy, and you select specific columns for CommentCopy,the “Explicit construction of entity type ‘###’ in query is not allowed” error does not appear? Why is that? I dont want to make clone classes of all of my classes to just select the specific columns that I need.

Is it at all possible to only select the columns you want from the generated linq classes without using an anonymous type or coding a specific class for each and everyone of your queries as return type?

regards
Niekie

Very interesting question. I am actually have the exact same issue. I have been trying for a while now to find a solution.

If anyone could should some light for us, it would be much appreciated.

Thanks

Quit treating things like database fields and start treating things like objects. Meaning, if you need a special view object, create a separate view object then select into it using Linq or what have you. Or just select the objects and work with them. What is the reason for stripping out the fields?

hi,thanx for the reply

My problem is with the basic functionality of linq to sql. Lets say you have an object- Person - with 20 properties,which are mapped to the sql with the dbml. Now I have a query where I only want to have the Person’s Name and email.

Do I have to create a custom class for every such operation? eg-PersonWithTwoFields

public PersonWithTwoFields getUsers()
{

var p = from c in Context.Person
select new PersonWithTwoFields{
name=c.memberName,
email=c.memberEmail
};
return p.FirstOrDefault();
}

Or do i have to create a anonymous type for each and every query? eg

public IQueryable getUsers()
{
var p = from c in Context.Person
select new {
name=c.memberName,
email=c.memberEmail
};
return p;
}

Im not even talking about having fields that are not part of person,like the amount of comments the person has made etc…–in those cases the anonymous type is fine–eg

public IQueryable getUsers()
{
var p = from c in Context.Person
select new {
amountOfComments=Context.Comment.Where(o=>o.ParentID==v.VideoID && o.MediaTypeID==(int)Enums.MediaType.Video).Count(),
randomField=“terterter”,
randomField2=“345345”,
email=c.memberEmail
};
return p;
}

You are missing my point–why the need to only get the two fields you are using? With ORMs (such as Linq2Sql) you generally just get an instance of your object and then let the other layers of the application figure out what to use.

yes sorry,thought that I was going a bit of track–I should have stated that the problem with all of this is the underlying sql that gets generated…

var p = from c in Context.Comment
select c;

That should be fine in most cases,but the sql statement that is generated-it’s selecting all of the sql columns…

the thing is you cant select which colums it should get,otherwise “Explicit construction of entity type ‘###’ in query is not allowed”

what if there were 20 columns in Comments,and 20 in members,and I join the 2,its a total of 40 columns im selecting,when I only wanted 3 columns

var p = from c in Context.Comment
//do a join with members
select new Comment{
commentID=c.CommentID,
commentText=c.commentText,
memberID=m.memberID
}

Unless any of the columns have massive text or binary fields, it really isn’t worth the trouble of worrying about it IMHO. Premature optimization is the root of all evil.

PS: If you can’t defeat your DBA instincts, you could try something like:


from f in Foo
select new { a = Foo.Bar, b=Foo.Foo}

@wwb. Yes fair enough. But my database has a lot of colums in 2 tables for eg. like members and inbox. Now I want to do a join just to get the members username and now it selects ALL the columns, which is not needed. Especially with large fields. I would not worry if there where not a large number of columns as well as large fields. But it does and this is a high traffic site, so would really wana eliminate all possible bytes coming down the wire from SQL server.

I have tried your example, but that doesnt not work, as then I would have to return IQueryable instead of IQueryable<Member> and therefor cannot do anything with it as its of type anonymous.

I want to select a certain type with only a few properties set, but linq does not allow that for some reason and I would just like to know if there is anyway around that.

Thanks

Well, the trick here is that if you are really concerned about having good managability of the data layer, Linq2Sql isn’t for you. It is on that lite end of ORM options which are more focused on rapid development and less focused on performance concerns. Or, more simply, one is trading performance and tweakability for development time. A trade that often makes sense given the relative expense of developers vs. hardware.

Options here are:

  1. You should be able to use the above tactic in read-only scenarios, IQueryable is bindable methinks, if not .AsEnumerable and .AsList are definitely bindable.
  2. Create some “data view objects” for common scenarios where you want stripped down fields.
  3. Switch to a different ORM product which gives you more control.
  4. Refactor the database so the big fields are stored separately somehow. Actually often a good idea in a general sense.

Note by big fields, I mean large VARBINARY(MAX) or VARCHAR(MAX). As in over one sql page (8000 characters). Anything under that should not really materially effect stuff as you don’t get nasty hits until you start spanning pages.

Also note that I wouldn’t get into data stream optimization until I either a) know I have a problem or b) have enough metrics to show that I actually am gaining significant performance. I’d generally bet that you could get much more performance out of a solid caching strategy rather than tweaking the SELECT statements to reduce fields. That said, examining what happens on the databases’ end of things is definitely a key part of any optimization strategy.

Thanks for all the info.Yes, IQueryable is bindable, but sometimes I would not have to bind them so need them as an object b4 binding. But I guess that is not possible with linq unless I create another object for this reason.

I have heard from a lot of people now that Linq2Sql might not be the best tool for the job unless I need to do a rapid deployment.

What ORM would you suggest and y? I was thinking of EF or NHibernate? Or is there something else you think I would be able to get into easier? Considering my Linq2Sql knowledge, would EF be easier?

I have also heard that the EF is quite complex and hard 2 use if your starting with ORM. Is this true?

Thanks for any thoughts