LINQ to Entities materialization error

Question

I have Entity Framework 4 model over SQLite databese. Part of the model looks like this enter image description here

All Id fields have type Guid (uniqueidentifier in SQLite table DDL). But when I run following LINQ query I get exception.

var query = AbonentPfrs.Select(a => 
    new 
    {
        AbPfr = a,
        Pfr = a.PfrCertificates.Select(c => c.Id), 
        Ac = a.AcCertificates.Select(c => c.Id)
    }
);
query.ToList();

When I run it I get

System.InvalidOperationException: The type of the key field 'Id' is expected to be 'System.Guid', but the value provided is actually of type 'System.String'.

UPDATE: I found out that EF generate SQL query that really returns strings as identifiers for one of the entities.

The SQL that goes to the DB ((query as ObjectQuery).ToTraceString()) looks like this:

SELECT 
[UnionAll1].[C2] AS [C1], 
[UnionAll1].[C3] AS [C2], 
[UnionAll1].[Id] AS [C3], 
[UnionAll1].[PfrRegNumber] AS [C4], 
[UnionAll1].[PfrUnitId] AS [C5], 
[UnionAll1].[Account_Id] AS [C6], 
[UnionAll1].[ActiveCertificate_Id] AS [C7], 
[UnionAll1].[C1] AS [C8], 
[UnionAll1].[Id1] AS [C9], 
[UnionAll1].[C4] AS [C10]
FROM  (SELECT 
  CASE WHEN ([Extent2].[Id] IS NULL) THEN NULL ELSE 1 END AS [C1], 
  1 AS [C2], 
  1 AS [C3], 
  [Extent1].[Id] AS [Id], 
  [Extent1].[PfrRegNumber] AS [PfrRegNumber], 
  [Extent1].[PfrUnitId] AS [PfrUnitId], 
  [Extent1].[Account_Id] AS [Account_Id], 
  [Extent1].[ActiveCertificate_Id] AS [ActiveCertificate_Id], 
  [Extent2].[Id] AS [Id1], 
  NULL AS [C4]
  FROM  [AbonentPfrs] AS [Extent1]
  LEFT OUTER JOIN [Certificates] AS [Extent2] ON [Extent1].[Id] = [Extent2].[AbonentPfr_PfrCertificates_Certificate_Id]
UNION ALL
  SELECT 
  2 AS [C1], 
  2 AS [C2], 
  2 AS [C3], 
  [Extent3].[Id] AS [Id], 
  [Extent3].[PfrRegNumber] AS [PfrRegNumber], 
  [Extent3].[PfrUnitId] AS [PfrUnitId], 
  [Extent3].[Account_Id] AS [Account_Id], 
  [Extent3].[ActiveCertificate_Id] AS [ActiveCertificate_Id], 
  NULL AS [C4], 
  [Extent4].[Id] AS [Id1]
  FROM  [AbonentPfrs] AS [Extent3]
  INNER JOIN [Certificates] AS [Extent4] ON [Extent3].[Id] = [Extent4].[AbonentPfr_AcCertificates_Certificate_Id]) AS [UnionAll1]
ORDER BY [UnionAll1].[Id] ASC, [UnionAll1].[C1] ASC

enter image description here

The last to columns are ids for Certificate entities. But the last have type Guid and the previous - string. If I run parts of the query separately then I get next column types (fot last two):

First select: Guid, Object
Second select: Object, Guid
Union all: Guid, Object
Outer select: String, Guid

Why outer select returns strings in last but one column?

I think it's because two relationships on the the same table - from AbonentPfr to Certificates. But I'm not sure... — sedovav, Jun 28 '13 at 14:35
Probably because SQLite stores GUIDs as strings? AFAIK SQLite does not have a native GUID data type. — okrumnow, Jun 28 '13 at 14:42

score 0 · Answer 1 · edited May 23 '17 at 12:28

0

When executing the outer join, the line CASE WHEN ([Extent2].[Id] IS NULL) THEN NULL ELSE 1 END AS [C1] will return a null value for C1 when your query encounters a missing value. In .NET, GUID is a value type (128-bit integer), so it cannot be set to null without jumping through hoops. (see Why isn't there a Guid.IsNullOrEmpty() method for more info on the nullabilty of the GUID structure.)

Since the value returned is nullable and formatted as a xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, it is assumed to be a string.

edited May 23 '17 at 12:28

Community

1
1

answered Jul 03 '13 at 23:14

Jeremy S

1,043
1
7
9

I've changed the picture of the result to show what type columns have. As you can see the problem is in "C9" column that is the union of `[Extent2].[Id]` and `NULL`. But the "C10" column is good - result type is Guid. And I don't see the difference. C10 is the result of union of `NULL` and `[Extent4].[Id]`. Need to say that both `[Extent2].[Id]` and `[Extent4].[Id]` have the same db type. – sedovav Jul 04 '13 at 07:48

score 0 · Accepted Answer · answered Jul 04 '13 at 08:41

It seems that the ordering is the point. Without ordering the result types of last two columns are Guid and Objects. With ordering - String and Guid. Both order expressions depend on [Extent2].[Id] column. SQLite doesn't operate Guid type so, I think, values are converted into String. It's one of difficulties of EF4 and System.Data.SQLite integration. So I need to find a way to force EF do not generate ordering clause...

LINQ to Entities materialization error

2 Answers2