Naturally, these are on my mind because my hand still stings from getting slapped by the runtime when I tried to do this.
LINQ to Entities does not support projecting into known types structures.
Note, I tried this first with a structure, then saw the forum thread which I quoted below that I apparently misconstrued and landed on the wrong conclusion. The post stopped me from experimenting further. I have since edited the post to be improve its accuracy. Thanks to Larry Parker for pointing out the gap.
If you have a type or struct defined such as:
Public Structure KeyListItem
Public ID As Integer
Public Name As String
End Structure
and are doing a query that will project out an ID and a Name, this is the natural way to write the query, given some experience with LINQ to [anything (including LINQ to SQL) but Entities].
Dim custs = From cust In context.Customers _
Select New KeyListItem With {.ID = cust.ContactID, .Name = cust.Name}
The compiler is fine with this because at compile time, it's merely a LINQ query.
However when you execute this query, you will get the error:
Only parameterless constructors and initializers are supported by LINQ to Entities.
LINQ to Entities can't construct a new KeyListItem structure on the fly with the ID and Name parameters.
Here's an explanation of why this is the behavior for LINQ to Entities from Sushil Chordia on the DP team (from this forum thread).
The idea of supporting only parameter-less constructor was one of the hard decisions we made as a product team. The main idea with this approach was we shouldn't open up new surface area in LINQ over Entities that is not supported by EDM. EDM in general doesn't allow you to construct in random objects (using the NEW constructor in eSQL). To make it consistent with the whole stack, we decided to implement it like wise. Limiting the constructions only to:
- Parameterless constructors
- Anonymous types (as it’s the only way to do multi-project in LINQ over Entities)
The query will work if you project into a class, not a structure. If you must use a structure then, instead you need to go the long way around with this.
Dim custs = From cust In context.Customers _
Select cust.ContactID, cust.Name
Dim custlist = New List(Of KeyListItem)
For Each cust In custs
custlist.Add(New KeyListItem With {.ID = cust.ContactID, .Name = cust.FullNameAlpha})
Next
Okay, now the next thing you might accidentally try if your mind is wandering is to use custom properties from your partial classes in queries.
In my model I have a firstname property and a lastname property. I got sick of concatenating them, so I cleverly created two new properties in the entity's partial class: FullName and FullNameAlpha. A long long time ago, I got over the fact that I couldn't pull this off in the model and I'm extremely happy that this scenario is being targeted for v2.
So I'm happily coding along and writing a query where I want to return the FullNameAlpha field and sorted.
Because LINQ depends on the compiled classes to let us code with Intellisense, it's fine with me using the FullNameAlpha property in my query
Dim custs = From cust In .Customer _
Order By cust.FullNameAlpha _
Select cust.ContactID, cust.FullNameAlpha
But of course FullNameAlpha is not part of the model, so when EF tries to compile the query, it throws the obvious exception:
The specified type member 'FullNameAlpha' is not supported in LINQ to Entities.
Only initializers, entity members, and entity navigation properties are supported.
Oops. Yeah, right, I knew that!
So what that means is I still have to freaking concatenate the first name and last name in my query.
But there's another rule to watch out for which is a LINQ rule, not LINQ to Entities. When you create an on the fly property in a projection and your are doing so with a function, LINQ cannot figure out what to call that property, so you must name it as I have done here by using the variable name "fullname".
Dim custs = From cust In .Customer _
Order By cust.FullNameAlpha _
Select cust.ContactID, fullname = cust.LastName.Trim & "," & cust.FirstName
Otherwise you will get the LINQ error:
(VB) Range variable name can be inferred only from a simple or qualified name with no arguments
(C#) Invalid anonymous type member declarator. Anonymous type members must be declared with a member assignment, simple name or member access.
I imagine I'll repeat these errors over and over and have to be reminded by the thrown exceptions over and over.