Linq to EF guid in where clause always returns null

Question

I've tried many different ways and the "topic" is always null from the LINQ statement. Is there something special that has to be done when comparing GUIDs with SQLite as the data source?

    public TopicModel GetTopicModel(Guid id)
    {
        TopicModel topicModel = null;
        using (var context = new onenessEntities())
        {
            var topic = context.Topics.FirstOrDefault(x => x.Id.Equals(id));

            if (topic != null)
            {
                topicModel = new TopicModel
                {
                    Id = topic.Id,
                    Description = topic.Description,
                    Title = topic.Title
                };
            }
        }

        return topicModel;
    }

Auto Generated SQL

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[CategoryId] AS [CategoryId], 
[Extent1].[Language] AS [Language], 
[Extent1].[Title] AS [Title], 
[Extent1].[Description] AS [Description], 
[Extent1].[Keywords] AS [Keywords], 
[Extent1].[Version] AS [Version], 
[Extent1].[CurrentPosition] AS [CurrentPosition], 
[Extent1].[Notes] AS [Notes]
FROM [Topic] AS [Extent1]
WHERE ([Extent1].[Id] = @p__linq__0) AND (@p__linq__0 IS NOT NULL) LIMIT 1


-- p__linq__0: '90f8f2c6-31cf-47a1-b8fb-61d5f4130d8f' (Type = AnsiStringFixedLength)

-- Executing at 9/17/2015 2:56:37 PM -05:00

-- Completed in 1 ms with result: SQLiteDataReader

Edit: I've learned this issue is due to SQLite storing GUIDs as binary blobs. As a work around I changed the data type from GUID to CHAR(36) and now am able to use LINQ to EF to retrieve the records. I'd still like to see if someone can answer this original question though.

score 2 · Answer 1 · answered Aug 23 '16 at 23:27

To fix this issue append this to the connection string:

;binaryguid=False

If true - GUID columns are stored in binary form; otherwise GUID columns are stored as text. I think the issue otherwise caused by the way EF injects the parameter (Type = AnsiStringFixedLength).

Queries using guid parameters will still work fine with binaryguid when using a raw query (SQLiteCommand with execute reader). Avoiding EF lets you supply your own.

command.Parameters.Add(new SQLiteParameter(DbType.Guid)
{
    ParameterName = "@id",
    Value = id
});

score 1 · Answer 2 · edited May 23 '17 at 12:24

1

I wonder what SQL is being generated for the where clause x => x.Id.Equals(id)? I found this link which discusses a bug in the Expression Analyzer if id happens to be a nullable uniqueidentifier.

The suggested fix was to use Equals in this manner instead: x => Object.Equals(x.Id, id)

edited May 23 '17 at 12:24

Community

1
1

answered Sep 08 '15 at 00:00

meataxe

969
13
33

I added the generated SQL to the original post. – Jim Harkins Sep 17 '15 at 20:00
x => Object.Equals(x.Id, id) generated this error {"Unable to cast the type 'System.Guid' to type 'System.Object'. LINQ to Entities only supports casting EDM primitive or enumeration types."} – Jim Harkins Sep 17 '15 at 20:03
Your generated SQL looks OK, in that the parameters are turning up in the right spot for a valid comparison, but there's a mismatch in your datatypes. I'm guessing `[Topic].[Id]` in the db is a blob datatype and you need to get `Id` and `@p__linq__0` into the same datatype for the comparison to work. At that point I'm at the limit of my knowledge of SQLLite, however I did find this discussion about [SQLite & EF guids](http://stackoverflow.com/questions/27279177/how-does-the-sqlite-entity-framework-6-provider-handle-guids) which might be some help. – meataxe Sep 17 '15 at 22:28
Thanks, I saw that article too. Storing the ID as text seems more efficient to me than generating new GUIDs during a query. Also found an article about adding an environment variable to the connection string to tell SQLite to not store the GUID as a blob, but I could not get it to work in visual studio with EF connection string. – Jim Harkins Sep 18 '15 at 14:46

score 1 · Accepted Answer · answered Feb 02 '16 at 17:38

1

I changed the data type of Id to CHAR instead of GUID to work around the issue.

answered Feb 02 '16 at 17:38

Jim Harkins

383
4
15

Linq to EF guid in where clause always returns null

3 Answers3