The LINQ expression could not be translated

[cosmin-ciuc]

I have a LINQ query that is executed perfectly when using Microsoft.EntityFrameworkCore.Sqlite 2.2.6 and Microsoft.EntityFrameworkCore 2.2.6. But when upgrading to Microsoft.EntityFrameworkCore 3.1.2 and Microsoft.EntityFrameworkCore.Sqlite 3.1.2 the same LINQ query throws the exception:

The LINQ expression 'DbSet<DocumentEntity>
    .Where(d => !(d.IsDeleted))
    .Join(
        outer: DbSet<DocumentRelationEntity>
            .Where(d0 => !(d0.IsDeleted)), 
        inner: d => new { 
            DocumentId = d.Id, 
            ObjectType = 0
         }, 
        outerKeySelector: d0 => new { 
            DocumentId = d0.DocumentId, 
            ObjectType = d0.ObjectType
         }, 
        innerKeySelector: (d, d0) => new TransparentIdentifier<DocumentEntity, DocumentRelationEntity>(
            Outer = d, 
            Inner = d0
        ))
    .Join(
        outer: DbSet<DocumentCaseEntity>
            .Where(d1 => !(d1.IsDeleted)), 
        inner: ti => ti.Inner.ObjectId.ToLower(), 
        outerKeySelector: d1 => d1.Id.ToString().ToLower(), 
        innerKeySelector: (ti, d1) => new TransparentIdentifier<TransparentIdentifier<DocumentEntity, DocumentRelationEntity>, DocumentCaseEntity>(
            Outer = ti, 
            Inner = d1
        ))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

Steps to reproduce

I'm attaching a .NET Core 3.1 console app that reproduces the error.
ConsoleApp1.zip

Further technical details

EF Core version: 3.1.2
Database provider: Microsoft.EntityFrameworkCore.Sqlite 3.1.2
Target framework: NET Core 3.1
Operating system: Windows 10 Enterprise
IDE: Visual Studio 2019 16.4.3

[smitpatel]

Sqlite does not support ToString translation to server in 3.1. See #17223

[smitpatel]

Try rewriting d1.Id.ToString().ToLower() to (d1.Id + "").ToString() to see if it works.

[cosmin-ciuc]

Try rewriting d1.Id.ToString().ToLower() to (d1.Id + "").ToString() to see if it works.

@smitpatel I have replaced my second Join with

                .Join(//// We assume that every document is part of a DoumentCaseArchive. Documents that do not have a DocumentCase relation won't be returned
                    ctx.DocumentCases.AsQueryable(),
                    doc => doc.DocumentCaseId,
                    documentCase => (documentCase.Id + ""),
                    (doc, documentCase) => new DocumentSearchEntity

and now it throws the exception

Microsoft.Data.Sqlite.SqliteException: 'SQLite Error 1: 'no such table: Document'.'

[cosmin-ciuc]

@smitpatel, I figured it out why I was getting the exception 'SQLite Error 1: 'no such table: Document'.'
Because I forgot to add

ctx.Database.OpenConnection();

before EnsureCreated.

I had to remove any call to ToString() or ToLower() and it worked.

So the working query is like this:

    class Program
    {
        static void Main(string[] args)
        {
            using var ctx = new MyDbContext();
            ctx.Database.OpenConnection();
            ctx.Database.EnsureDeleted();
            ctx.Database.EnsureCreated();
            var includeDraftVersionInResult = true;
            var id = Guid.NewGuid();

            var query = ctx.Documents.AsQueryable()
                .Include(document => document.TypeFsCode)
                .Join(
                    ctx.DocumentRelations.AsQueryable(),
                    doc => new
                    {
                        DocumentId = doc.Id,
                        ObjectType = (int)ObjectType.DocumentCase,
                    },
                    relation => new
                    {
                        relation.DocumentId,
                        relation.ObjectType,
                    },
                    (doc, rel) => new
                    {
                        DocumentEntity = doc,
                        DocumentCaseId = rel.ObjectId,
                    })
                .Join(//// We assume that every document is part of a DoumentCaseArchive. Documents that do not have a DocumentCase relation won't be returned
                    ctx.DocumentCases.AsQueryable(),
                    doc => doc.DocumentCaseId,
                    documentCase => documentCase.Id + "",
                    (doc, documentCase) => new DocumentSearchEntity
                    {
                        DocumentEntity = doc.DocumentEntity,
                        DocumentCaseId = documentCase.Id,
                        DocumentCaseNumber = documentCase.Number,
                        LatestVersion = doc.DocumentEntity.Versions
                                                            .Where(verFinal => verFinal.Status == (int)VersionStatus.Final)
                                                            .OrderByDescending(verFinal => verFinal.CreatedOn)
                                                            .FirstOrDefault(),
                        DraftVersion = includeDraftVersionInResult
                                       ? doc.DocumentEntity.Versions
                                                            .Where(verDraft => verDraft.Status == (int)VersionStatus.Draft)
                                                            .OrderByDescending(verDraft => verDraft.CreatedOn)
                                                            .FirstOrDefault()
                                       : null,
                    });
            var query2 = query.Where(entry => entry.DocumentEntity.Id == id)
                        .Select(entry => entry.TransferPropertiesToDocumentEntity(CultureInfo.GetCultureInfo("en-US")));
            var entity = query.SingleOrDefault();
            ctx.Database.CloseConnection();
        }
    }

[ajcvickers]

Duplicate of #17223