EF Lazy loading #10917

.Net Core, Angular, 11.0.0

Am I missing something here, I was certain EF lazy loading was disabled in Zero. I have a simple call to a table in my application layer:

var listTest = _ngFormSubmissionRepository.GetAll().ToList();

My FormSubmission table has millions of rows and a few parent tables, enabled in code as follows:

    public class NgFormSubmission : FullAuditedEntity<long>, IMustHaveTenant
    {
        public int TenantId { get; set; }
        public long? OrganizationUnitId { get; set; }

        [Required]
        public long UserId { get; set; }

        [ForeignKey("NgFormId")]
        public virtual NgForm NgForm { get; set; }
        public virtual int? NgFormId { get; set; }

        [ForeignKey("NgEntityId")]
        public virtual NgEntity NgEntity { get; set; }
        public System.Guid? NgEntityId { get; set; }

        [ForeignKey("NgMobileDeviceId")]
        public virtual NgMobileDevice NgMobileDevice { get; set; }
        public int? NgMobileDeviceId { get; set; }

        [ForeignKey("NgActionId")]
        public virtual NgAction NgAction { get; set; }
        public long? NgActionId { get; set; }
... code removed for brevity

I make a single call to get a list of FormSubmissions:

var listTest = _ngFormSubmissionRepository.GetAll().ToList();

and the SQL created as a result includes calls to parent entities (NgForm, NgEntity, NgMobileDevice and so on) which is not so bad but also included are calls to children of those entities.

SELECT [t].[ClientCreatedDatetime], [t].[CreationTime], COALESCE([t].[CreatorUserId], CAST(0 AS bigint)), [t].[DeleterUserId], [t].[DeletionTime], [t].[Id], [t].[IsDeleted], [t].[IsFlagged], [t].[LastModificationTime], [t].[LastModifierUserId], CASE
    WHEN [t0].[Id] IS NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, [t0].[CreationTime], COALESCE([t0].[CreatorUserId], CAST(0 AS bigint)), [t0].[DeleterUserId], [t0].[DeletionTime], [t0].[DisplayName], [t0].[ExtensionData], [t0].[Id], [t0].[IsDeleted], [t0].[LastModificationTime], [t0].[LastModifierUserId], CASE
    WHEN [t1].[Id] IS NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, [t1].[CreationTime], COALESCE([t1].[CreatorUserId], CAST(0 AS bigint)), [t1].[DeleterUserId], [t1].[DeletionTime], [t1].[Description], [t1].[DisplayName], [t1].[ExtensionData], [t1].[FormReportTemplate], [t1].[Id], [t1].[IsDeleted], [t1].[IsEditable], [t1].[LastModificationTime], [t1].[LastModifierUserId], [t1].[SerializedJson], [t1].[TenantId], [t0].[NgFormId], [t0].[OrganizationUnitId], [t0].[ParentId], [t0].[TenantId], [t].[NgActionId], CASE
    WHEN [t2].[Id] IS NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, [t2].[CreationTime], COALESCE([t2].[CreatorUserId], CAST(0 AS bigint)), [t2].[DeleterUserId], [t2].[DeletionTime], [t2].[DisplayName], [t2].[ExtensionData], [t2].[Id], [t2].[IsDeleted], [t2].[LastModificationTime], [t2].[LastModifierUserId], [t2].[TenantId], [t].[NgEntityId], CASE
    WHEN [t3].[Id] IS NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, [t3].[CreationTime], COALESCE([t3].[CreatorUserId], CAST(0 AS bigint)), [t3].[DeleterUserId], [t3].[DeletionTime], [t3].[Description], [t3].[DisplayName], [t3].[ExtensionData], [t3].[FormReportTemplate], [t3].[Id], [t3].[IsDeleted], [t3].[IsEditable], [t3].[LastModificationTime], [t3].[LastModifierUserId], [t3].[SerializedJson], [t3].[TenantId], [t].[NgFormId], CASE
    WHEN [t4].[Id] IS NULL THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END, [t4].[AppVersion], [t4].[AuthorisedTime], [t4].[Cordova], [t4].[CreationTime], COALESCE([t4].[CreatorUserId], CAST(0 AS bigint)), [t4].[DeleterUserId], [t4].[DeletionTime], [t4].[ExtensionData], [t4].[Id], [t4].[IMEI], [t4].[IsAuthorised], [t4].[IsDeleted], [t4].[IsOnline], [t4].[IsVirtual], [t4].[LastModificationTime], [t4].[LastModifierUserId], [t4].[Manufacturer], [t4].[Model], [t5].[Id], [t5].[LoginDatetime], [t5].[LogoutDatetime], [t5].[NgMobileDeviceId], [t5].[TenantId], [t5].[UserId], [t6].[CreationTime], [t6].[CreatorUserId], [t6].[DeleterUserId], [t6].[DeletionTime], [t6].[Directive], [t6].[ExtensionData], [t6].[Id], [t6].[IsDeleted], [t6].[NgMobileDeviceId], [t6].[Priority], [t6].[TenantId], [t4].[Platform], [t4].[Serial], [t4].[TenantId], [t4].[UUID], [t4].[Version], [t].[NgMobileDeviceId], [t].[OrganizationUnitId], [t].[ReportString], [t].[SerializedJson], [t].[TenantId], [t].[UserId]
FROM (
    SELECT [n].[Id], [n].[ClientCreatedDatetime], [n].[CreationTime], [n].[CreatorUserId], [n].[DeleterUserId], [n].[DeletionTime], [n].[IsDeleted], [n].[IsFlagged], [n].[LastModificationTime], [n].[LastModifierUserId], [n].[NgActionId], [n].[NgEntityId], [n].[NgFormId], [n].[NgMobileDeviceId], [n].[OrganizationUnitId], [n].[ReportString], [n].[SerializedJson], [n].[TenantId], [n].[UserId]
    FROM [NgFormSubmission] AS [n]
    WHERE ((((@__ef_filter__p_0 = CAST(1 AS bit)) OR ([n].[IsDeleted] = CAST(0 AS bit))) AND ((@__ef_filter__p_1 = CAST(1 AS bit)) OR ([n].[TenantId] = @__ef_filter__CurrentTenantId_2))) AND ([n].[IsDeleted] = @__isDeleted_0)) AND ([n].[NgEntityId] = @__p_1)
    ORDER BY [n].[ClientCreatedDatetime] DESC
    OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY
) AS [t]
LEFT JOIN (
    SELECT [n0].[Id], [n0].[CreationTime], [n0].[CreatorUserId], [n0].[DeleterUserId], [n0].[DeletionTime], [n0].[DisplayName], [n0].[ExtensionData], [n0].[IsDeleted], [n0].[LastModificationTime], [n0].[LastModifierUserId], [n0].[NgFormId], [n0].[OrganizationUnitId], [n0].[ParentId], [n0].[TenantId]
    FROM [NgAction] AS [n0]
    WHERE ((@__ef_filter__p_3 = CAST(1 AS bit)) OR ([n0].[IsDeleted] = CAST(0 AS bit))) AND ((@__ef_filter__p_4 = CAST(1 AS bit)) OR ([n0].[TenantId] = @__ef_filter__CurrentTenantId_5))
) AS [t0] ON [t].[NgActionId] = [t0].[Id]
LEFT JOIN (
    SELECT [n1].[Id], [n1].[CreationTime], [n1].[CreatorUserId], [n1].[DeleterUserId], [n1].[DeletionTime], [n1].[Description], [n1].[DisplayName], [n1].[ExtensionData], [n1].[FormReportTemplate], [n1].[IsDeleted], [n1].[IsEditable], [n1].[LastModificationTime], [n1].[LastModifierUserId], [n1].[SerializedJson], [n1].[TenantId]
    FROM [NgForm] AS [n1]
    WHERE ((@__ef_filter__p_6 = CAST(1 AS bit)) OR ([n1].[IsDeleted] = CAST(0 AS bit))) AND ((@__ef_filter__p_7 = CAST(1 AS bit)) OR ([n1].[TenantId] = @__ef_filter__CurrentTenantId_8))
) AS [t1] ON [t0].[NgFormId] = [t1].[Id]
LEFT JOIN (
    SELECT [n2].[Id], [n2].[CreationTime], [n2].[CreatorUserId], [n2].[DeleterUserId], [n2].[DeletionTime], [n2].[DisplayName], [n2].[ExtensionData], [n2].[IsDeleted], [n2].[LastModificationTime], [n2].[LastModifierUserId], [n2].[TenantId]
    FROM [NgEntity] AS [n2]
    WHERE ((@__ef_filter__p_9 = CAST(1 AS bit)) OR ([n2].[IsDeleted] = CAST(0 AS bit))) AND ((@__ef_filter__p_10 = CAST(1 AS bit)) OR ([n2].[TenantId] = @__ef_filter__CurrentTenantId_11))
) AS [t2] ON [t].[NgEntityId] = [t2].[Id]
LEFT JOIN (
    SELECT [n3].[Id], [n3].[CreationTime], [n3].[CreatorUserId], [n3].[DeleterUserId], [n3].[DeletionTime], [n3].[Description], [n3].[DisplayName], [n3].[ExtensionData], [n3].[FormReportTemplate], [n3].[IsDeleted], [n3].[IsEditable], [n3].[LastModificationTime], [n3].[LastModifierUserId], [n3].[SerializedJson], [n3].[TenantId]
    FROM [NgForm] AS [n3]
    WHERE ((@__ef_filter__p_6 = CAST(1 AS bit)) OR ([n3].[IsDeleted] = CAST(0 AS bit))) AND ((@__ef_filter__p_7 = CAST(1 AS bit)) OR ([n3].[TenantId] = @__ef_filter__CurrentTenantId_8))
) AS [t3] ON [t].[NgFormId] = [t3].[Id]
LEFT JOIN (
    SELECT [n4].[Id], [n4].[AppVersion], [n4].[AuthorisedTime], [n4].[Cordova], [n4].[CreationTime], [n4].[CreatorUserId], [n4].[DeleterUserId], [n4].[DeletionTime], [n4].[ExtensionData], [n4].[IMEI], [n4].[IsAuthorised], [n4].[IsDeleted], [n4].[IsOnline], [n4].[IsVirtual], [n4].[LastModificationTime], [n4].[LastModifierUserId], [n4].[Manufacturer], [n4].[Model], [n4].[Platform], [n4].[Serial], [n4].[TenantId], [n4].[UUID], [n4].[Version]
    FROM [NgMobileDevice] AS [n4]
    WHERE ((@__ef_filter__p_12 = CAST(1 AS bit)) OR ([n4].[IsDeleted] = CAST(0 AS bit))) AND ((@__ef_filter__p_13 = CAST(1 AS bit)) OR ([n4].[TenantId] = @__ef_filter__CurrentTenantId_14))
) AS [t4] ON [t].[NgMobileDeviceId] = [t4].[Id]


NOTE THESE LEFT JOINS.....

LEFT JOIN (
    SELECT [n5].[Id], [n5].[LoginDatetime], [n5].[LogoutDatetime], [n5].[NgMobileDeviceId], [n5].[TenantId], [n5].[UserId]
    FROM [NgMobileDeviceAndUserLoginAudit] AS [n5]
    WHERE (@__ef_filter__p_15 = CAST(1 AS bit)) OR ([n5].[TenantId] = @__ef_filter__CurrentTenantId_16)
) AS [t5] ON [t4].[Id] = [t5].[NgMobileDeviceId]
LEFT JOIN (
    SELECT [n6].[CreationTime], [n6].[CreatorUserId], [n6].[DeleterUserId], [n6].[DeletionTime], [n6].[Directive], [n6].[ExtensionData], [n6].[Id], [n6].[IsDeleted], [n6].[NgMobileDeviceId], [n6].[Priority], [n6].[TenantId]
    FROM [NgMobileDeviceOfflineDirective] AS [n6]
    WHERE ((@__ef_filter__p_17 = CAST(1 AS bit)) OR ([n6].[IsDeleted] = CAST(0 AS bit))) AND ((@__ef_filter__p_18 = CAST(1 AS bit)) OR ([n6].[TenantId] = @__ef_filter__CurrentTenantId_19))
) AS [t6] ON [t4].[Id] = [t6].[NgMobileDeviceId]**
ORDER BY [t].[ClientCreatedDatetime] DESC, [t].[Id], [t0].[Id], [t1].[Id], [t2].[Id], [t3].[Id], [t4].[Id], [t5].[Id]

My NgMobileDevice entity has audit tables for geolocation tracking and user sign-in/sign-out, each of which has several million rows making the query very ugly indeed. I thought Zero worked the lines of:

var listTest = _ngFormSubmissionRepository.GetAll()
                .Include(m => m.NgMobileDevice)
                .ToList();

when you wanted to include parents and children? Am I missing something and is there any way I can switch off EF lazy loading? More for my understanding of the application, maybe I've beign doing things incorrectly all these years?????

Cheers, Bob