Print Page | Close Window

Entity SQL cannot materialize an entity if you select individual columns instead of selecting the entity

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2009
Forum Discription: For .NET 3.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1420
Printed Date: 22-Sep-2025 at 4:22am


Topic: Entity SQL cannot materialize an entity if you select individual columns instead of selecting the entity
Posted By: pk55
Subject: Entity SQL cannot materialize an entity if you select individual columns instead of selecting the entity
Date Posted: 10-Aug-2009 at 10:41am
I posted this on the Entity Framework/ADO.Net forum as well since I think it's an EF problem and not DevForce but just in case it is or someone has seen this before, I'm posting it here as well.
 
Entity Framework (.Net 3.5/Visual Studio 2008 SP1/SQL Server 2008) using DevForce RTW with Silverlight 3 using a PassThruESQLQuery.

I have an entity that has one key column (GUID) and a single scalar column (BIGINT) that have been mapped in the model from the database (nothing behind the scenes here).  If I create an ESQL query like:
       SELECT VALUE u FROM myEntitySet as u
the query result can be cast to the entity type correctly.  If however, I specify the column names:
       SELECT u.myKey, u.myCounter FROM myEntitySet as u
then the query is physically executed (I can see it in SQL Profiler) but Entity Framework throws this error:

The specified cast from a materialized 'System.Data.Objects.MaterializedDataRecord' type to the 'MyTestProject.myEntity' type is not valid.
   at System.Data.Common.Internal.Materialization.Translator.CheckedConvert[TSource,TTarget](TSource value)

I've tried switching the order of the columns around; adding an "as" clause (u.myKey AS myKey); using VALUE ROW or just ROW but the issue persists.  When I look at the generated T-SQL, I notice that a "SELECT 1 AS [C1]" is added to the ESQL query that specifies the columns so my result set ends up having three columns instead of two.  I think this is confusing conversion from datarecord to entity:

SELECT
1 AS [C1],
[Extent1].[my_key] AS [my_key],
[Extent1].[totalCount] AS [totalCount]
FROM [dbo].[myEntity] AS [Extent1]

If I just use SELECT VALUE u, it generates:
SELECT
[Extent1].[my_key] AS [my_key],
[Extent1].[totalCount] AS [totalCount]
FROM [dbo].[myEntity] AS [Extent1]

Any help would be appreciated.


Replies:
Posted By: pk55
Date Posted: 11-Aug-2009 at 11:27am

The solution was to use the Type constructor.  MSFT answered:

This is an expected behvaior with Entity Framework. When you use a SELECT VALUE entity SQL you are essentially selecting the entity as a whole, in other words the type is preserverd. When you write an entity SQL that has a list of projection, Entity Framework doesnt know that the values in the projection list are part of the entity. In these cases, it is assigned a default type of Edm.Row which translates to DbDataRecord in the O-Space. More info on the SELECT operator in eSQL at http://msdn.microsoft.com/en-us/library/bb399554.aspx - http://msdn.microsoft.com/en-us/library/bb399554.aspx

If you want to construct the entity again from a list of projection you can use the Type constructor in ESQL. More examples of using the type constructor is at http://msdn.microsoft.com/en-us/library/bb738526.aspx - http://msdn.microsoft.com/en-us/library/bb738526.aspx
 
I had tried the type constructore before:

      SELECT VALUE myEntity(u.myKey, u.myCounter) FROM myEntitySet as u

but that gets an error: 'myEntity' cannot be resolved into a valid type constructor.

I didn't realize you actually need the edm model name to prefix the entity type and when I tried this, it worked:

     SELECT VALUE myModelName.myEntity(u.myKey, u.myCounter) FROM myEntitySet as u
 
 


Print Page | Close Window