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

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for SQLite
Post Reply
bairog
Posts: 117
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Mon 28 Sep 2020 08:51

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
Site Admin
Posts: 9077
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Mon 05 Oct 2020 16:55

bairog wrote:
Mon 28 Sep 2020 08:51
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
Posts: 117
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Tue 06 Oct 2020 10:37

Shalex wrote:
Mon 05 Oct 2020 16:55
If navigation property is null, lazy loading is disabled (correct behavior).
I was mistakenly thinking that disabling Lazy Loading will result in Explicit Loading.
Shalex wrote:
Mon 05 Oct 2020 16:55
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?


Shalex
Site Admin
Posts: 9077
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Sat 17 Oct 2020 10:44

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

Shalex
Site Admin
Posts: 9077
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Mon 19 Oct 2020 09:50

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
Posts: 117
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Mon 19 Oct 2020 13:34

Shalex wrote:
Mon 19 Oct 2020 09:50
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
Site Admin
Posts: 9077
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Thu 22 Oct 2020 09:32

bairog wrote:
Mon 19 Oct 2020 13:34
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.
bairog wrote:
Mon 19 Oct 2020 13:34
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
Posts: 117
Joined: Mon 29 Apr 2013 09:05

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

Post by bairog » Thu 22 Oct 2020 12:15

Shalex wrote:
Thu 22 Oct 2020 09:32
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?
Shalex wrote:
Thu 22 Oct 2020 09:32
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
Site Admin
Posts: 9077
Joined: Thu 14 Aug 2008 12:44

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

Post by Shalex » Fri 23 Oct 2020 09:24

bairog wrote:
Thu 22 Oct 2020 12:15
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.
bairog wrote:
Thu 22 Oct 2020 12:15
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.

Post Reply