New Posts New Posts RSS Feed: Entity SQL cannot materialize an entity if you select individual columns instead of selecting the entity
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Entity SQL cannot materialize an entity if you select individual columns instead of selecting the entity
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post Topic: Entity SQL cannot materialize an entity if you select individual columns instead of selecting the entity
    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.
Back to Top
pk55 View Drop Down
Senior Member
Senior Member


Joined: 22-Jul-2009
Location: CA
Posts: 105 		Post Options Post Options   Quote pk55 Quote  Post ReplyReply Direct Link To This Post 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

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
 
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
 
 
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down