"Apply joins is not supported" in SQLite Query

Question

I'm trying convert my application with MS SQL Server 2014 database to SQlite. This query works well on SQL Server, but with SQLite, i encounter "APPLY JOINS is not supported" error.

this error exist only with *select ( & include) query.

Query:

        public static IList<Projet> GetListByClientWithDetails(long IdClient)
    {
        IList<Projet> resultList = null;

        using (FITSEntities db_context = new FITSEntities())
        {
            resultList = db_context.Projet.Where(s => s.IdClient == IdClient)
                .Include(s => s.Cdts.Select(r => r.CdtFiches))
                .Include(s => s.Cdts.Select(r => r.Sessions))
                .Include(s => s.Fiches.Select(r => r.FicheVersions))
                .ToList();
        }
        return resultList;
    }

If i comment this line: .Include(s => s.Cdts.Select(r => r.CdtFiches))

        public static IList<Projet> GetListByClientWithDetails(long IdClient)
    {
        IList<Projet> resultList = null;

        using (FITSEntities db_context = new FITSEntities())
        {
            resultList = db_context.Projet.Where(s => s.IdClient == IdClient)
              //  .Include(s => s.Cdts.Select(r => r.CdtFiches))
                .Include(s => s.Cdts.Select(r => r.Sessions))
                .Include(s => s.Fiches.Select(r => r.FicheVersions))
                .ToList();
        }
        return resultList;
    }

It works well.

If i comment another line: .Include(s => s.Cdts.Select(r => r.Sessions))

        public static IList<Projet> GetListByClientWithDetails(long IdClient)
    {
        IList<Projet> resultList = null;

        using (FITSEntities db_context = new FITSEntities())
        {
            resultList = db_context.Projet.Where(s => s.IdClient == IdClient)
                .Include(s => s.Cdts.Select(r => r.CdtFiches))
               // .Include(s => s.Cdts.Select(r => r.Sessions))
                .Include(s => s.Fiches.Select(r => r.FicheVersions))
                .ToList();
        }
        return resultList;
    }

it works well too.

Are there any specific rules to sqlite select query?

Arthur Attout · Answer 1 · 2022-06-17T08:58:06.037

I know this is an old thread, but I encountered it right today, so here's my two cent for any future reader

This somewhat unconventional error is either due to the way SQLite database works OR the way the SQLite provider for EF has been written.

In either case, there's little hope that this can be fixed in order to simply "make the query work".

However, there is a workaround I have found to circumvent this issue. While it probably doesn't leverage the power of EF, it gets the job done.

Core of the problem

The main issue is that this LINQ query is attempting to Include two one-to-many navigation properties on the same one-to-many table (in your case, Cdts).

When trying to Include on 'multiple levels', a way to do this in pure LINQ using Include is to throw-in a Select

resultList = db_context.Projet.Where(s => s.IdClient == IdClient)
    .Include(s => s.Cdts.Select(r => r.CdtFiches))
    .Include(s => s.Cdts.Select(r => r.Sessions))

Here, I suppose you wanted to include both CdtFiches and Sessions which are one-to-many relationships on the Cdt table. However SQLite doesn't like that (I have no idea why, though, because SQLServer is fine with it).

What you need to do is to manually Select your root entity and force-fetch the related entities using ToList. This achieves exactly the same result as Include (although I suspect the latter is more efficient).

In your case

resultList = db_context.Projet.Where(s => s.IdClient == IdClient)
    .Select(_toDeepProjet)
    .ToList()
    
private Projet _toDeepProjet(Projet p)
{
    p.Cdts = p.Cdts.Select(_toDeepCdts).ToList();
    return p;
}

private Cdts _toDeepCdts(Cdts c)
{
    // Force the fetching of entities
    // It is equivalent to writing two Includes in your original query
    c.CdtFiches = c.CdtFiches.ToList();
    c.Sessions = c.Sessions.ToList();
    return c;
}

It's hacky. But it works.

"Apply joins is not supported" in SQLite Query

1 Answers1

Core of the problem