Efcore: Cannot filter on Guid column with SQLite provider

[spallister]

Upon attempting to filter for a single record with a Guid PK, where such a record actually exists in a SQLite database, null is being returned. The Guid PK value is being stored as a Blob in the SQLite database, and it seems that the way EF Core is generating SQL from LINQ queries, does not allow for querying using Guid values - I must first convert Guid values to strings for filtering to successfully work.

Steps to reproduce

• Using the PowerShell Add-Migration and Update-Database commands together with the SQLite DB provider, create a table (e.g. DbSet<Foo>) having a Guid PK property named Id.

• Add a new Foo entry with some Guid PK value, then attempt to query for that entry using (where query is some object with Id as a Guid property):
  c# var foo = _context .Foos .SingleOrDefault(f => f.Id == query.Id);
  ... and observe that foo is null.

• Attempt to query for the entry using:
  c# var foo = _context .Foos .SingleOrDefault(f => f.Id.ToString() == query.Id.ToString());
  ... and observe that the non-null entry is returned.

[bricelam]

What version are you using? (A similar bug #5801 was fixed in version 1.0.1)

[spallister]

Using Microsoft.EntityFrameworkCore.Sqlite 2.0.1.

[ajcvickers]

@spallister Can you please post a runnable project/solution or full code listing so that we can attempt to reproduce what you are seeing?

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

[ysmoradi]

I've faced the same issue.
Project details:

Visual Studio 15.5.5
Xamarin iOS 11.6.1.4
iOS 11.2 (Simulator)
Microsoft.EntityFrameworkCore.Sqlite 2.0.1

Code:

var people = await context.People.Take(10).ToArrayAsync();
var person = await context.People.FindAsync(people.First().Id);
// person is null

[msangtarash]

@spallister , your second code is working

var foo = _context
    .Foos
    .SingleOrDefault(f => f.Id.ToString() == query.Id.ToString());

because entity framework core executes that locally instead of on database. (Because current version of ef core is unable to translate .ToString() of Guid)

I'm working on this issue, but it seems it's related to sql lite instead of ef core.

Stay tuned.

[msangtarash]

Following codes works too

SqliteParameter idParameter = new SqliteParameter("@id", SqliteType.Text);
idParameter.Value = myGuid.ToString();
context.People.FromSql("select * from People where Id = @id", idParametere);

[ysmoradi]

Using parameter with type text works only when you've set db type of that column to text by [Column] attribute for example. I'm gonna create sample app for all cases

[bricelam]

Is this only with existing databases with TEXT values?

One wat of dealing with that is just to map the colunm as a string property and deal with the conversions to/from Guid in the app code.

[ysmoradi]

The database is created using ef core itself. My tests are not finished yet, but it seems there is no problem with sqlite db created by ef on Windows/Android.
I'm not satisfied with string properties. I'd like to have guid props. I've some DTO classes which are shared between client and odata server. The string approach affects both server & client. The FromSql workaround is working at the moment. But let's (please) fix it completely :-S

[bricelam]

Yes, please, as far as we know Guid equality comparison should work on EF Core 1.0.1+. If you can get us a repro, we'll look into it

[ysmoradi]

I'm afraid and please accept my apologies! As we've inserted some data in our migration using .Sql method, and at there, instead of X'a701a6147c764c19a016e4ea9ab735ea' we had used 'a701a614-7c76-4c19-a016-e4ea9ab735ea', we experienced a lot of problems.
Sorry for my bad.

[bricelam]

Thanks for getting to the bottom of it!

[RouR]

This is not work.

var foo = _context
    .Foos
    .SingleOrDefault(f => f.SomeField == query.SomeField);

var connection = new SqliteConnection("DataSource=:memory:");

Microsoft.EntityFrameworkCore.Sqlite 2.2.4

There is one very important difference between .SingleOrDefault(f => f.SomeField == query.SomeField); and .SingleOrDefault(f => f.SomeField.ToString() == query.SomeField.ToString()); - where it will be evaluated .
Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'where ([x].SomeField .ToString() == __ToString_1)' could not be translated and will be evaluated locally.'

This LINQ must work - .SingleOrDefault(f => f.SomeField == query.SomeField);
But it does not.

[ajcvickers]

@RouR Please file a new issue and include a small, runnable project/solution or complete code listing that demonstrates the behavior you are seeing.

[RouR]

My mistake.
I did some investigation.
It seem that Sqlite-in-memory does not support raw sql queries. So, some logic changes in DB was skip, and result query is not valid. Not valid query returns null.

I guess it need a warning in log for context.Database.GetDbConnection().CreateCommand() and context.SomeEntities.FromSql

Some tests for play with it http://github.com/RouR/Sqlite-InMemoryTest