Wrong values mapped to properties when properties have the same names

[firedog]

When using a poco with a combination of inheritance and a nested class (complex mapping) NPoco maps incorrect values into properties having the same names (Id in my case).

Tested on SQL Server 2017 using .NET Core 3.0 and Npoco versions 3.9.4 and 4.0.2.

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

    class Program
    {
        public abstract class Entity
        {
            public Guid Id { get; set; }
            public long Version { get; set; }
        }

        public enum ScopeType
        {
            Full,
            Partial
        }

        public class Owner
        {
            public Guid Id { get; set; }
            public ScopeType Scope { get; set; }
        }

        public class OneInherited : Entity
        {
            public OneInherited()
            {
                Manys = new List<Many>();
            }

            public string Name { get; set; }
            public Referenced Referenced { get; set; }
            public string ReferencedName { get; set; }
            public Owner Owner { get; set; }
            public List<Many> Manys { get; set; }
        }

        public class One
        {
            public One()
            {
                Manys = new List<Many>();
            }

            public Guid Id { get; set; }
            public long Version { get; set; }
            public string Name { get; set; }
            public Referenced Referenced { get; set; }
            public string ReferencedName { get; set; }
            public Owner Owner { get; set; }
            public List<Many> Manys { get; set; }
        }

        public class Referenced : Entity
        {
            public string Name { get; set; }
        }

        public class Many : Entity
        {
            public Guid OneId { get; set; }
            public string Name { get; set; }
        }

        public class EntityMap : Map<Entity>
        {
            public EntityMap() : this(new TypeDefinition(typeof(Entity)))
            {
            }

            public EntityMap(TypeDefinition t) : base(t)
            {
                Columns(x =>
                {
                    x.Column(y => y.Id);
                    x.Column(y => y.Version).Version(VersionColumnType.Number);
                });
            }
        }

        public class OneInheritedMap : Map<OneInherited>
        {
            public OneInheritedMap() : this(new TypeDefinition(typeof(OneInherited)))
            { }

            public OneInheritedMap(TypeDefinition t) : base(t)
            {
                UseMap<EntityMap>();

                TableName("Ones").PrimaryKey(x => x.Id, false);

                Columns(x =>
                {
                    x.Column(y => y.Name);
                    x.Column(y => y.Referenced).WithName("ReferencedName").Reference(r => r.Name, ReferenceType.OneToOne);
                    x.Column(y => y.Owner).ComplexMapping("Owner");
                    x.Many(y => y.Manys).WithName("Id").Reference(z => z.OneId);
                });
            }
        }

        public class OneMap : Map<One>
        {
            public OneMap() : this(new TypeDefinition(typeof(One)))
            { }

            public OneMap(TypeDefinition t) : base(t)
            {
                TableName("Ones").PrimaryKey(x => x.Id, false);

                Columns(x =>
                {
                    x.Column(y => y.Id);
                    x.Column(y => y.Version).Version(VersionColumnType.Number);
                    x.Column(y => y.Name);
                    x.Column(y => y.Referenced).WithName("ReferencedName").Reference(r => r.Name, ReferenceType.OneToOne);
                    x.Column(y => y.Owner).ComplexMapping("Owner");
                    x.Many(y => y.Manys).WithName("Id").Reference(z => z.OneId);
                });
            }
        }

        public class ReferencedMap : Map<Referenced>
        {
            public ReferencedMap() : this(new TypeDefinition(typeof(Referenced)))
            { }

            public ReferencedMap(TypeDefinition t) : base(t)
            {
                TableName("Referenceds").PrimaryKey(x => x.Id, false);

                UseMap<EntityMap>();

                Columns(x =>
                {
                    x.Column(y => y.Name);
                });
            }
        }

        public class ManyMap : Map<Many>
        {
            public ManyMap() : this(new TypeDefinition(typeof(Many)))
            { }

            public ManyMap(TypeDefinition t) : base(t)
            {
                TableName("Manys").PrimaryKey(x => x.Id, false);

                UseMap<EntityMap>();

                Columns(x =>
                {
                    x.Column(y => y.Name);
                    x.Column(y => y.OneId);
                });
            }
        }


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

            var maps = Assembly.GetAssembly(typeof(EntityMap)).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));
            });

            using (var db = factory.GetDatabase())
            {
                var oneId = new Guid("A34146A0-516D-4ABD-8F02-117088DEFDCC");
                var manyId1 = new Guid("285BB20F-02FA-4E73-A95C-1A8FF5A58C3E");
                var manyId2 = new Guid("50719361-34A1-4BF3-B856-2D665F1B86B0");
                var ownerId = new Guid("0C5419CC-1F77-44DE-A51D-2DD1CB842133");
                var referencedId = new Guid("B8690FA4-1875-4E23-AFAF-4F4F19522451");

                // insert test data
                var referenced = new Referenced()
                {
                    Id = referencedId,
                    Name = "Referenced"
                };

                db.Insert(referenced);

                // We just use one of the two Ones when inserting...
                var one = new OneInherited
                {
                    Id = oneId,
                    Name = "Test",
                    ReferencedName = referenced.Name,
                    Owner = new Owner()
                    {
                        Id = ownerId,
                        Scope = ScopeType.Full
                    }
                };

                one.Manys.Add(new Many
                {
                    Id = manyId1,
                    OneId = one.Id,
                    Name = "Many 1"
                });

                one.Manys.Add(new Many
                {
                    Id = manyId2,
                    OneId = one.Id,
                    Name = "Many 2"
                });

                db.Insert(one);
                db.InsertBatch(one.Manys);

                // Fetch an inherited One
                var failing = db.Query<OneInherited>()
                    .Include(o => o.Referenced)
                    .IncludeMany(o => o.Manys)
                    .Single(o => o.Id == oneId);

                // Fetch One without base class
                var succeeding = db.Query<One>()
                    .Include(o => o.Referenced)
                    .IncludeMany(o => o.Manys)
                    .Single(o => o.Id == oneId);

                Console.WriteLine($"Failing: Id = {failing.Id}");
                Console.WriteLine($"Failing: Version = {failing.Version}");
                foreach (var many in failing.Manys)
                {
                    Console.WriteLine($"Failing: many.Id = {many.Id}");
                    Console.WriteLine($"Failing: many.OneId = {many.OneId}");
                }
                Console.WriteLine($"Failing: Owner.Id = {failing.Owner.Id}");
                Console.WriteLine($"Failing: Owner.Scope = {failing.Owner.Scope}");
                Console.WriteLine($"Failing: Referenced.Id = {failing.Referenced.Id}");

                Console.WriteLine("\n--------------------------------------------");

                Console.WriteLine($"Succeeding: Id = {succeeding.Id}");
                Console.WriteLine($"Succeeding: Version = {succeeding.Version}");
                foreach (var many in succeeding.Manys)
                {
                    Console.WriteLine($"Succeeding: many.Id = {many.Id}");
                    Console.WriteLine($"Succeeding: many.OneId = {many.OneId}");
                }
                Console.WriteLine($"Succeeding: Owner.Id = {succeeding.Owner.Id}");
                Console.WriteLine($"Succeeding: Owner.Scope = {succeeding.Owner.Scope}");
                Console.WriteLine($"Succeeding: Referenced.Id = {succeeding.Referenced.Id}");
            }

            Console.WriteLine("\n--------------------------------------------");
            Console.WriteLine("Press ENTER to exit");
            Console.ReadLine();
        }
    }

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

    USE [npoco]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Manys](
        [Id] [uniqueidentifier] NOT NULL,
        [Version] [bigint] NOT NULL,
        [Name] [nvarchar](50) NOT NULL,
        [OneId] [uniqueidentifier] NOT NULL,
     CONSTRAINT [PK_Manys] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Ones](
        [Id] [uniqueidentifier] NOT NULL,
        [Version] [bigint] NOT NULL,
        [Name] [nvarchar](50) NOT NULL,
        [ReferencedName] [nvarchar](50) NOT NULL,
        [Owner__Id] [uniqueidentifier] NULL,
        [Owner__Scope] [int] NULL,
     CONSTRAINT [PK_Ones] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[Referenceds](
        [Id] [uniqueidentifier] NOT NULL,
        [Version] [bigint] NOT NULL,
        [Name] [nvarchar](50) NOT NULL,
     CONSTRAINT [PK_Referenceds] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    ALTER TABLE [dbo].[Manys]  WITH CHECK ADD  CONSTRAINT [FK_Manys_Ones] FOREIGN KEY([OneId])
    REFERENCES [dbo].[Ones] ([Id])
    GO
    ALTER TABLE [dbo].[Manys] CHECK CONSTRAINT [FK_Manys_Ones]
    GO
    ALTER TABLE [dbo].[Ones]  WITH CHECK ADD  CONSTRAINT [FK_Ones_Referenceds] FOREIGN KEY([ReferencedName])
    REFERENCES [dbo].[Referenceds] ([Name])
    GO
    ALTER TABLE [dbo].[Ones] CHECK CONSTRAINT [FK_Ones_Referenceds]
    GO

The output when running this application is:

Failing: Id = 00000000-0000-0000-0000-000000000000
Failing: Version = 0
Failing: many.Id = a34146a0-516d-4abd-8f02-117088defdcc
Failing: many.OneId = a34146a0-516d-4abd-8f02-117088defdcc
Failing: many.Id = a34146a0-516d-4abd-8f02-117088defdcc
Failing: many.OneId = a34146a0-516d-4abd-8f02-117088defdcc
Failing: Owner.Id = 0c5419cc-1f77-44de-a51d-2dd1cb842133
Failing: Owner.Scope = Full
Failing: Referenced.Id = b8690fa4-1875-4e23-afaf-4f4f19522451

---

Succeeding: Id = a34146a0-516d-4abd-8f02-117088defdcc
Succeeding: Version = 1
Succeeding: many.Id = 285bb20f-02fa-4e73-a95c-1a8ff5a58c3e
Succeeding: many.OneId = a34146a0-516d-4abd-8f02-117088defdcc
Succeeding: many.Id = 50719361-34a1-4bf3-b856-2d665f1b86b0
Succeeding: many.OneId = a34146a0-516d-4abd-8f02-117088defdcc
Succeeding: Owner.Id = 0c5419cc-1f77-44de-a51d-2dd1cb842133
Succeeding: Owner.Scope = Full
Succeeding: Referenced.Id = b8690fa4-1875-4e23-afaf-4f4f19522451

---

Press ENTER to exit

[rhegner]

I experience a similar issue with an entity called Parent with columns ParentId and Id. That does seem to confuse Include. Here is a simple and complete toy example:

using Microsoft.Data.Sqlite;
using NPoco;
using System;
using System.IO;
using System.Threading.Tasks;

namespace NPoco_Test
{
    class Program
    {
        static async Task Main()
        {
            File.Delete("Test.db");
            using var db = new Database("DataSource=Test.db", DatabaseType.SQLite, SqliteFactory.Instance);
            await db.ExecuteAsync("CREATE TABLE Parent (ParentId int NOT NULL PRIMARY KEY, Id int NOT NULL)");
            await db.ExecuteAsync("CREATE TABLE Child (ChildId int NOT NULL PRIMARY KEY, ParentId int NOT NULL, CONSTRAINT fk FOREIGN KEY (ParentId) REFERENCES Parent(ParentId))");
            await db.InsertAsync(new Parent() { ParentId = 1, Id = 11 });
            await db.InsertAsync(new Parent() { ParentId = 2, Id = 22 });
            var parents = await db.FetchAsync<Parent>();
            foreach (var parent in parents)
                Console.WriteLine(parent);
            await db.InsertAsync(new Child() { ChildId = 100, ParentId = 1 });
            await db.InsertAsync(new Child() { ChildId = 200, ParentId = 2 });
            var children = await db.Query<Child>().Include(c => c.Parent).ToListAsync();
            foreach (var child in children)
                Console.WriteLine(child);
        }
    }

    [PrimaryKey("ParentId", AutoIncrement = false)]
    public class Parent
    {
        public int ParentId { get; set; }
        public int Id { get; set; }

        public override string ToString() => $"ParentId={ParentId}, Id={Id}";
    }

    [PrimaryKey("ChildId", AutoIncrement = false)]
    public class Child
    {
        public int ChildId { get; set; }
        public int ParentId { get; set; }

        [Reference(ReferenceType.OneToOne, ColumnName = "ParentId", ReferenceMemberName = "ParentId")]
        public Parent Parent { get; set; }

        public override string ToString() => $"ChildId={ChildId}, ParentId={ParentId} ({Parent?.ToString()})";
    }
}

Output:

ParentId=1, Id=11
ParentId=2, Id=22
ChildId=100, ParentId=1 (ParentId=1, Id=0)
ChildId=200, ParentId=2 (ParentId=2, Id=0)

As you can see, querying the Parents directly works fine - but querying the children, including their parent, Parent.Id is 0.

---

PS: I've been using NPoco for many years, but started looking for good alternatives recently (without success) because it looked like this project was abandoned. So I'm really happy to see again some activity and a new version coming up! Keep up the good work!!

[schotime]

Is in v5 alpha-5