Disabling Lazy loading in not working (and Eager loading multiple same-leveled entites) in Devart.Data.SQLite.Entity.EF6

[bairog]

Hello.
Assume we have the following entities:

Code: Select all

public class UserRole
    {
        public Guid Id { get; set; }
        public String Name { get; set; }
        public virtual IList<User> Users { get; set; }
    }
    public class User
    {
        public Guid Id { get; set; }
        public String Name { get; set; }
        public Guid UserRoleId { get; set; }
        public virtual UserRole UserRole { get; set; }
        public virtual IList<Message> Messages { get; set; }
        public virtual IList<Picture> Pictures { get; set; }
    }
    public class Message
    {
        public Guid Id { get; set; }
        public String Text { get; set; }
        public Guid UserId { get; set; }
        public virtual User User { get; set; }
    }
    public class Picture
    {
        public Guid Id { get; set; }
        public String Name { get; set; }
        public Guid UserId { get; set; }
        public virtual User User { get; set; }
    } 

I'm trying to load all data from database (including all related entites).
First I've tried to disable Lazy loading:

Code: Select all

DbAccess.ActiveContext.Configuration.LazyLoadingEnabled = false;
            var userRoles = DbAccess.ActiveContext.UserRoles.AsNoTracking().ToList();

But userRoles.Users is null (looks like Lazy loading still working despite of my configuration setting).
After that I've tried Eager loading:

Code: Select all

var userRoles = DbAccess.ActiveContext.UserRoles.AsNoTracking().Include(ur => ur.Users.Select(us => us.Messages))
                .Include(ur => ur.Users.Select(us => us.Pictures)).ToList();

but I've got an exception

{"OUTER APPLY is not supported by SQLite."}

Sample project I've uploaded here (I'm using Devart.Data.SQLite.Entity.EF6 5.15.1587)
Click FillDatabase button first to create database (on disk D).

[Shalex]

First I've tried to disable Lazy loading:

Code: Select all

DbAccess.ActiveContext.Configuration.LazyLoadingEnabled = false;
            var userRoles = DbAccess.ActiveContext.UserRoles.AsNoTracking().ToList();

But userRoles.Users is null (looks like Lazy loading still working despite of my configuration setting).

If navigation property is null, lazy loading is disabled (correct behavior).

After that I've tried Eager loading:

Code: Select all

var userRoles = DbAccess.ActiveContext.UserRoles.AsNoTracking().Include(ur => ur.Users.Select(us => us.Messages))
                .Include(ur => ur.Users.Select(us => us.Pictures)).ToList();

but I've got an exception

{"OUTER APPLY is not supported by SQLite."}

Could you try to rewrite your LINQ query to avoid generation of OUTER APPLY?

[bairog]

If navigation property is null, lazy loading is disabled (correct behavior).

I was mistakenly thinking that disabling Lazy Loading will result in Explicit Loading.

Could you try to rewrite your LINQ query to avoid generation of OUTER APPLY?

What exactly do you mean? In my query with Eager Loading

Code: Select all

var userRoles = DbAccess.ActiveContext.UserRoles.AsNoTracking().Include(ur => ur.Users.Select(us => us.Messages))
                .Include(ur => ur.Users.Select(us => us.Pictures)).ToList();

I simply want to get all UserRoles with all chidren Users and grand children entities Messages and Pictures. As far as I know that query is the only way to get all that entites. Am I wrong?

[bairog]

Any updates (10 days has passed)?

[Shalex]

We are looking for a workaround. As soon as we have any result, we will notify you.

[Shalex]

If there may be roles for which there are no users, then you have to execute 2 physical queries to the database (we could not find a way to get by with 1 query):

Code: Select all

      var users = DbAccess.ActiveContext.Users
          .Include (u => u.UserRole)
          .Include (u => u.Messages)
          .Include (u => u.Pictures)
          .ToList ();

      var userRoles = DbAccess.ActiveContext.UserRoles.ToList ();
      userRoles.ForEach (role => role.Users = users.Where (u => u.UserRoleId == role.Id) .ToList ());

If each role always has at least one user, then you can get by with 1 request:

Code: Select all

      var users = DbAccess.ActiveContext.Users
          .Include (u => u.UserRole)
          .Include (u => u.Messages)
          .Include (u => u.Pictures)
          .ToList ();

      var userRoles = users.Select (u => u.UserRole) .Distinct (). ToList ();

In both cases, we give examples without .AsNoTracking () since this allows not to complicate the examples, but to demonstrate a possible workaround. Indeed, with .AsNoTracking (), duplication of the same entities is possible, which is important in some scenarios, but not in others.

[bairog]

If each role always has at least one user, then you can get by with 1 request:

Code: Select all

      var users = DbAccess.ActiveContext.Users
          .Include(u => u.UserRole)
          .Include(u => u.Messages)
          .Include(u => u.Pictures)
          .ToList();

      var userRoles = users.Select(u => u.UserRole).Distinct().ToList();

As I can see in your code you avoid using TEntity.Select() inside IQueryable<T>.Include() method (it is one of accepted ways of loading multiple levels of related entities in EF6 according to Microsoft docs - Eagerly loading multiple levels section).
That was the cause of the problem (that leads to the {"OUTER APPLY is not supported by SQLite."} exception)?
Is that a limitation of SQLite engine itself or it is Devart provider bug?

P. S. In EF Core 3.1.9 I can get all I need in one query using ThenInclude method:

Code: Select all

context.UserRoles.Include(ur => ur.Users).ThenInclude(u => u.Messages)
.Include(ur => ur.Users).ThenInclude(u => u.Pictures).ToList();

So that problem is not a limitation of SQLite engine itself..

[Shalex]

That was the cause of the problem (that leads to the {"OUTER APPLY is not supported by SQLite."} exception)?
Is that a limitation of SQLite engine itself or it is Devart provider bug?

Our implementation in dotConnect for SQLite avoids generation of OUTER APPLY if it is possible, but some LINQ queries cannot be translated without employing OUTER APPLY. In this case, you should rewrite your LINQ statement.

P. S. In EF Core 3.1.9 I can get all I need in one query using ThenInclude method:

Code: Select all

context.UserRoles.Include(ur => ur.Users).ThenInclude(u => u.Messages)
.Include(ur => ur.Users).ThenInclude(u => u.Pictures).ToList();

So that problem is not a limitation of SQLite engine itself..

Different versions of ORMs (EF6 and EF Core) translate similar queries in different ways. EF6 is more likely to try to generate an OUTER APPLY that is not supported in SQLite.

[bairog]

Our implementation in dotConnect for SQLite avoids generation of OUTER APPLY if it is possible, but some LINQ queries cannot be translated without employing OUTER APPLY. In this case, you should rewrite your LINQ statement.

Let's clarify: Devart.Data.SQLite.Entity.EFCore.dll is able to translate my LINQ query to SQL without OUTER APPLY, but Devart.Data.SQLite.Entity.EF6.dll cannot do the same thing?

Different versions of ORMs (EF6 and EF Core) translate similar queries in different ways. EF6 is more likely to try to generate an OUTER APPLY that is not supported in SQLite.

Do you mean that the LINQ-to-SQL translation bug (gereration of OUTER APPLY when specific DBMS doesn't support it) is located inside EF6 codebase (EntityFramework.dll) and you provider can do nothing with that (change that behavoir)?

[Shalex]

Let's clarify: Devart.Data.SQLite.Entity.EFCore.dll is able to translate my LINQ query to SQL without OUTER APPLY, but Devart.Data.SQLite.Entity.EF6.dll cannot do the same thing?

That is correct.

LINQ query is converted into an expression tree of some kind by ORM's engine (it is EntityFramework.dll for EF6). Then, the expression tree is converted to SQL.
In EF6 and in EF Core, similar requests are processed differently and different expression trees are formed.

Do you mean that the LINQ-to-SQL translation bug (gereration of OUTER APPLY when specific DBMS doesn't support it) is located inside EF6 codebase (EntityFramework.dll) and you provider can do nothing with that (change that behavoir)?

That is correct.