Include ignores properties with complex mapping in referenced objects

[firedog]

When querying using NPoco.Linq Include() to pull in referenced data NPoco seems to ignore properties in the referenced objects that are mapped using Complex mapping. The type of reference, OneToOne or Foreign, does not seem to matter.

Tested on SQL Server 2019 using .NET Core 5.0 and Npoco version 5.3.1.

To reproduce create a .NET Core Console application and replace Program.cs with the following code:

using System;
using System.Linq;
using System.Reflection;
using Microsoft.Data.SqlClient;
using NPoco;
using NPoco.FluentMappings;
using NPoco.Linq;

namespace NPocoIncludeTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var connectionString = "<ConnectionStringToNpocoDatabaseGoesHere>";

            var maps = Assembly.GetAssembly(typeof(CarMap)).GetTypes().Where(t => typeof(IMap).IsAssignableFrom(t))
                .Select(t => Activator.CreateInstance(t) as IMap).ToArray();

            var factory = DatabaseFactory.Config(x =>
            {
                x.UsingDatabase(() =>
                    new Database(connectionString, DatabaseType.SqlServer2012, SqlClientFactory.Instance));
                x.WithFluentConfig(FluentMappingConfiguration.Configure(maps));
            });

            // Insert test data...
            using (var db = factory.GetDatabase())
            {
                Car car = null;
                Engine engine = null;
                Chassis chassis = null;

                var carId = new Guid("EB9CF396-1EA7-4125-8DF2-FC47B32985BC");
                var engineId = new Guid("16708CC6-9919-4928-B22C-923B2DF3CD69");
                var chassisId = new Guid("1AADE8B2-3736-40DE-ADA1-9BC2323D8C6B");

                engine = new Engine
                {
                    Id = engineId,
                    Cylinders = 12,
                    Identity = new Identity
                    {
                        Manufacturer = "Lamborghini",
                        Serial = "1234567890"
                    }
                };
                db.Insert(engine);

                car = new Car
                {
                    Id = carId,
                    Make = "Skodaghini",
                    Engine = engine
                };
                db.Insert(car);

                chassis = new Chassis
                {
                    Id = chassisId,
                    CarId = carId,
                    Wheels = 4,
                    Identity = new Identity()
                    {
                        Manufacturer = "Skoda",
                        Serial = "9876543210"
                    }
                };
                db.Insert(chassis);

                // Fetch data...
                Console.WriteLine("Fetch Car using 'Include'");
                Console.WriteLine("--------------------------------------------");
                car = db.Query<Car>()
                    .Include(c => c.Chassis, JoinType.Inner)
                    .Include(c => c.Engine, JoinType.Inner)
                    .SingleOrDefault(c => c.Id == carId);

                PrintNullStatus("Car.Engine", car.Engine.Identity);
                PrintNullStatus("Car.Chassis", car.Chassis.Identity);

                Console.WriteLine("\nFetch Engine and Chassis separately");
                Console.WriteLine("--------------------------------------------");
                engine = db.Query<Engine>().SingleOrDefault(e => e.Id == engineId);
                PrintNullStatus("Engine", engine.Identity);

                chassis = db.Query<Chassis>().SingleOrDefault(c => c.Id == chassisId);
                PrintNullStatus("Chassis", chassis.Identity);

                Console.WriteLine("\nFetch Car using SQL statement");
                Console.WriteLine("--------------------------------------------");
                car = db.Query<Car>(
                    "Select c.*, e.*, ch.* from Cars c JOIN Engines e ON e.Id = c.EngineId JOIN Chassis ch ON ch.CarId = c.Id WHERE c.Id = @0",
                    carId).SingleOrDefault();

                PrintNullStatus("Car.Engine", car.Engine.Identity);
                PrintNullStatus("Car.Chassis", car.Chassis.Identity);

                Console.WriteLine("\nPress ENTER to exit");
                Console.ReadLine();
            }
        }

        private static void PrintNullStatus(string owner, Identity i)
        {
            Console.WriteLine($"{owner}.Identity is{(i != null ? " not" : "")} null!");
        }

        public class Car
        {
            public Guid Id { get; set; }

            public string Make { get; set; }

            public Engine Engine { get; set; }

            public Chassis Chassis { get; set; }
        }

        public class Engine
        {
            public Guid Id { get; set; }

            public int Cylinders { get; set; }

            public Identity Identity { get; set; }
        }

        public class Chassis
        {
            public Guid Id { get; set; }

            public int Wheels { get; set; }

            public Guid CarId { get; set; }

            public Identity Identity { get; set; }
        }

        public class Identity
        {
            public string Serial { get; set; }

            public string Manufacturer { get; set; }
        }

        public class CarMap : Map<Car>
        {
            public CarMap()
            {
                TableName("Cars").PrimaryKey(x => x.Id, autoIncrement: false);

                Columns(x =>
                {
                    x.Column(y => y.Make);
                    x.Column(y => y.Engine).Reference(engine => engine.Id, ReferenceType.Foreign).WithName("EngineId");
                    x.Column(y => y.Chassis).Reference(chassis => chassis.CarId, ReferenceType.OneToOne).WithName("Id");
                });
            }
        }

        public class EngineMap : Map<Engine>
        {
            public EngineMap()
            {
                TableName("Engines").PrimaryKey(x => x.Id, false);

                Columns(x =>
                {
                    x.Column(y => y.Id);
                    x.Column(y => y.Cylinders);
                    x.Column(y => y.Identity).ComplexMapping("Identity");
                });
            }
        }

        public class ChassisMap : Map<Chassis>
        {
            public ChassisMap()
            {
                TableName("Chassis").PrimaryKey(x => x.Id, false);

                Columns(x =>
                {
                    x.Column(y => y.Id);
                    x.Column(y => y.Wheels);
                    x.Column(y => y.Identity).ComplexMapping("Identity");
                });
            }
        }
    }
}

Create a SQL Server database and run the following sql script to create the tables

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cars](
    [Id] [uniqueidentifier] NOT NULL,
    [Make] [nvarchar](50) NOT NULL,
    [EngineId] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Chassis](
    [Id] [uniqueidentifier] NOT NULL,
    [CarId] [uniqueidentifier] NOT NULL,
    [Wheels] [tinyint] NOT NULL,
    [Identity__Serial] [nvarchar](50) NOT NULL,
    [Identity__Manufacturer] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Chassis] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Engines](
    [Id] [uniqueidentifier] NOT NULL,
    [Cylinders] [tinyint] NOT NULL,
    [Identity__Serial] [nvarchar](50) NOT NULL,
    [Identity__Manufacturer] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Engines] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Cars]  WITH CHECK ADD  CONSTRAINT [FK_Cars_Engines] FOREIGN KEY([EngineId])
REFERENCES [dbo].[Engines] ([Id])
GO
ALTER TABLE [dbo].[Cars] CHECK CONSTRAINT [FK_Cars_Engines]
GO
ALTER TABLE [dbo].[Chassis]  WITH CHECK ADD  CONSTRAINT [FK_Chassis_Cars] FOREIGN KEY([CarId])
REFERENCES [dbo].[Cars] ([Id])
GO
ALTER TABLE [dbo].[Chassis] CHECK CONSTRAINT [FK_Chassis_Cars]
GO

The output when running this application is:

Fetch Car using 'Include'
--------------------------------------------
Car.Engine.Identity is null!
Car.Chassis.Identity is null!

Fetch Engine and Chassis separately
--------------------------------------------
Engine.Identity is not null!
Chassis.Identity is not null!

Fetch Car using SQL statement
--------------------------------------------
Car.Engine.Identity is not null!
Car.Chassis.Identity is not null!

Press ENTER to exit

[schotime]

What is the SQL that is produced? I.e. is it a mapping or SQL gen issue?

[firedog]

Seems like an SQL issue as the "complex" columns aren't selected...

SELECT [C].[Id] as [Id], [C].[Make] as [Make], [C1].[Id] as [Chassis__Id], [C1].[Wheels] as [Chassis__Wheels], [C1].[CarId] as [Chassis__CarId], [E].[Id] as [Engine__Id], [E].[Cylinders] as [Engine__Cylinders] FROM [Cars] [C]  
  INNER JOIN [Chassis] [C1] ON [C].[Id] = [C1].[CarId] 
  INNER JOIN [Engines] [E] ON [C].[EngineId] = [E].[Id] 
WHERE ([C].[Id] = @0) 

Compared with the SQL that is generated from the second statement (selecting the Engine) we can see that the Identity object is correctly selected there.

SELECT [E].[Id] as [Id], [E].[Cylinders] as [Cylinders], [E].[Identity__Serial] as [Identity__Serial], [E].[Identity__Manufacturer] as [Identity__Manufacturer] 
FROM [Engines] [E] 
WHERE ([E].[Id] = @0)