How to linq filter on multiple GUID at once generating an appropriate sql where clause

Question

I'm having a hard time generating a properly filtered sql query with fluent API in Entity Framework core rc2-final connected to a sqlite database.

I have the following simple entity :

public class Thing
{
    [Required, Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid Id { get; set; }

    public string Name { get; set; }
}

I need to query the database for all books that are in a stash list of Books I provide. So far, sounds easy. This is how we could write it :

// 'GetShortListFromSomewhere' returns 2 items that ALREADY exist in db
List<Thing> externalList = GetShortListFromSomewhere();
var result = db.Things.Where(thing => externalList.Contains(o.Id)).ToList();

But the following query is generated which is kind of correct and kind of .. not .. for sqlite :

SELECT "o"."Id", "o"."Name" FROM "Thing" AS "o"
    WHERE "o"."Id" IN ('7edbc016-abfa-4005-83d1-b39618b047df', '2def16bb-4203-417d-847c-7bdf053a00e8')

As expected in code, this returns me 0 items although Ids are perfectly valid. Guid on Sqlite as stored on Blob and although EF-Core seems to handle the filter nicely for single item filter, it doesn't seem to handle it in this case.

How can I get around this issue knowing that I need it to be a sql operation ?

ps: Last thing I want is for EF Core to download the whole Thing collection from db and then filter it ! It would be madness in my case.

Answer 1

This is issue #5801. Unfortunately, it won't be fixed for the 1.0.0 release. To workaround, map the columns to byte[] properties.