InheritanceMappingAttribute is working incorrectly when abstract class is present in inheritance tree

[alexey-leonovich]

Describe your issue

I have a base class, it's child and a grandchild (it inherits from a child through an abstract class). Base class, child and grandchild are saved into same database table (I use InheritanceMappingAttribute and discriminator column). For a child it's working ok, but for grandchild discriminator code of a child ("Child") is inserted. The very same code was working correctly ("GrandChild" was inserted) via Linq to SQL DataContext (it was System.Data.Linq namespace instead of LinqToDB):

Steps to reproduce

1. Create .Net Framework 4.7.2 console application
2. Reference linq2db.SqlCe nuget (I used latest version 5.4.0)
3. Copy sample code below to Program.cs
4. Run application - get grandchild empty List.

using System;
using System.IO;
using LinqToDB.Data;
using LinqToDB;
using LinqToDB.Mapping;
using System.Linq;
using LinqToDB.DataProvider.SqlCe;
using System.Diagnostics;

namespace InheritanceTest
{
    [Table(Name = "InheritanceTest")]
    [InheritanceMapping(Code = "Child", IsDefault = true, Type = typeof(Child))]
    [InheritanceMapping(Code = "GrandChild", Type = typeof(GrandChild))]
    public abstract class Base
    {
        [Column(IsDiscriminator = true)] public string Code { get; set; }
        [Column(Name = "id", DbType = "uniqueidentifier", IsPrimaryKey = true)] public Guid Id { get; set; }
    }

    [Table(Name = "InheritanceTest")]
    public class Child : Base
    {
        [Column] public string Name { get; set; }
    }

    public abstract class Abstract : Child
    {
        [Column] public string Surname { get; set; }
    }

    [Table(Name = "InheritanceTest")]
    public class GrandChild : Abstract
    { }

    internal class Program
    {
        static void Main(string[] args)
        {
            using (var db = new DataConnection(new DataOptions().UseSqlCe(@"Data Source=test.sdf")))
            {
                if (!File.Exists("test.sdf"))
                {
                    SqlCeTools.CreateDatabase("test.sdf");
                    db.CreateTable<Base>();
                    db.Insert(new GrandChild() { Id = Guid.NewGuid(), Name = "Tom", Surname = "Black"});
                }
                var grandChildren = db.GetTable<GrandChild>().ToList(); //empty!!!
                var children = db.GetTable<Child>().ToList();                     //contains Tom Black
            }
            Console.ReadKey();
        }
    }
}

Generated SQL for inserting a grandchild:

IDECLARE @Code NVarChar(5) -- String
SET     @Code = 'Child'
DECLARE @Id UniqueIdentifier -- Guid
SET     @Id = '90e125d8-a3a7-408e-ba9f-0b43a17ca9db'
DECLARE @Name NVarChar(3) -- String
SET     @Name = 'Tom'
DECLARE @Surname NVarChar(5) -- String
SET     @Surname = 'Black'

INSERT INTO [InheritanceTest]
(
        [Code],
        [id],
        [Name],
        [Surname]
)
VALUES
(
        @Code,
        @Id,
        @Name,
        @Surname
)

P. S. Is it ok that for db.GetTable<Child>() LinqToDB generates WHERE ([t1].[Code] <> 'GrandChild' OR [t1].[Code] IS NULL) clause? For db.GetTable<GrandChild>() it generates WHERE ([t1].[Code] = 'GrandChild') clause and it's definitelly expected.

Environment details

Linq To DB version: 5.4.0

Database (with version): SQL Server Compact 4.0

ADO.NET Provider (with version): linq2db.SqlCe 5.4.0

Operating system: Windows 10 22H2

.NET Version: .Net Framework 4.7.2

[alexey-leonovich]

@MaceWindu Were you able to reproduce this? Is that a bug or am I missing something?

[MaceWindu]

I would suspect mapped types inheritance doesn't work from your description. Current Inheritance mapping implementation needs serious rework and till it is not done I would recommend to not inherit from mapped type.

[alexey-leonovich]

@MaceWindu I've investigated the problem further and actually there are two separate issues here:

1. For today LinqToDb is working correctly only for the simplest 2-level class hierarchy. For more complex hierarchy reading is working correctly but inserting has a bug - it inserts discriminator value of the nearest mapped parent. Possible workaround here is to set discriminator value explicitly in every class constructor. For my particular example it is:

    [Table(Name = "InheritanceTest")]
    [InheritanceMapping(Code = "Child", IsDefault = true, Type = typeof(Child))]
    [InheritanceMapping(Code = "GrandChild", Type = typeof(GrandChild))]
    [InheritanceMapping(Code = "GrandChild2", Type = typeof(GrandChild2))]
    public abstract class Base
    {
        [Column(IsDiscriminator = true)] public string Code { get; set; }
        [Column(Name = "id", DbType = "uniqueidentifier", IsPrimaryKey = true)] public Guid Id { get; set; }
    }

    [Table(Name = "InheritanceTest")]
    public class Child : Base
    {
        [Column] public string Name { get; set; }
        public Child()
        {
            Code = "Child";
         }
    }

    public abstract class Abstract : Child
    {
        [Column] public string Surname { get; set; }
    }

    [Table(Name = "InheritanceTest")]
    public class GrandChild : Abstract
    {
        public GrandChild ()
        {
            Code = "GrandChild";
         }
    }

    [Table(Name = "InheritanceTest")]
    public class GrandChild2 : Abstract
    {
        public GrandChild2 ()
        {
            Code = "GrandChild2";
         }
    }

2. For today LinqToDb is working incorrectly with intermediate abstract class in class hierarchy. Having a base abstract class is possible and is working correctly. That bug is described in detail (even possible workarounds and tests for them) in issue Referencing intermediate child classes of an abstract class using Linq2Db single table inheritance. #4364.
   In my case instead of var ret = this.GetTable<Base>().OfType<Abstract>.Where(a => a.Surname == "Black").ToList() I can use an ugly workaround var ret = this.GetTable<Base>().Where(e => (e.Code == "GrandChild") || (e.Code == "GrandChild2").Cast<Abstract>.Where(a => a.Surname == "Black").ToList(). According the tests only LINQ will fail (WhereCastIntermediateThings_Linq) - this is acceptable tradeoff for me for today. But I kindly ask to fix at least this error - it has been first reported in Aug 2021 Multiple Levels of Abstract Inheritance Results in Failed Reads #3184. Thank you.

[alexey-leonovich]

@MaceWindu Another aspect where inheritance mapping is broken: when two child classes have property with same name (mapped to columns with different names) - only first one is created by *Tools.CreateDatabase():

using LinqToDB.Data;
using LinqToDB.Mapping;
using LinqToDB;
using System;
using System.IO;
using LinqToDB.DataProvider.SqlCe;

namespace ConsoleApp1
{
    [Table(Name = "Tickets")]
    [InheritanceMapping(Code = "TicketChild", IsDefault = true, Type = typeof(TicketChild))]
    [InheritanceMapping(Code = "TicketChild2", Type = typeof(TicketChild2))]
    public abstract class TicketBase
    {
        [Column(IsDiscriminator = true)] public string EventCode { get; set; }
        [Column] public int Id { get; set; }
    }

    public class TicketChild : TicketBase
    {
        [Column(Name = "TicketChildCode")] public string Code { get; set; }
    }

    public class TicketChild2 : TicketBase
    {
        [Column(Name = "TicketChild2Code")] public string Code { get; set; }
        [Column(CanBeNull = true)] public decimal Price { get; set; }
    }

    internal class Program
    {
        static void Main(string[] args)
        {
            using (var db = new DataConnection(new DataOptions().UseSqlCe("Data Source=test.sdf")))
            {
                if (!File.Exists("test.sdf"))
                {
                    SqlCeTools.CreateDatabase("test.sdf");
                    db.CreateTable<TicketBase>();
                    db.Insert(new TicketChild() { Id = 1, Code = "Code1"});
                    //error - column TicketChild2Code doesn't exist
                    db.Insert(new TicketChild2() { Id = 2, Code = "Code2", Price = 12.357m }); 
                }
            }
            Console.ReadKey();
        }
    }
}

[alexey-leonovich]

@MaceWindu another broken aspect - is SomeInterface condition in WHERE:

using LinqToDB.Data;
using LinqToDB;
using System;
using System.IO;
using System.Linq;
using LinqToDB.DataProvider.SqlCe;
using LinqToDB.Mapping;

namespace InheritanceMappingGetByInterfaceTest
{
    [Table(Name = "Base")]
    [InheritanceMapping(Code = "Base", IsDefault = true, Type = typeof(Base))]
    [InheritanceMapping(Code = "Child", Type = typeof(Child))]
    [InheritanceMapping(Code = "Child2", Type = typeof(Child2))]
    public class Base
    {
        [Column(IsDiscriminator = true)] public string Code { get; set; }
        [Column] public Guid Id { get; set; }
    }

    public interface IChild
    {
        string Name { get; }
    }

    [Table(Name = "Base")]
    public class Child : Base, IChild
    {
        [Column(CanBeNull = true)] public string Name { get; set; }

        public Child()
        {
            Code = "Child";
        }
    }

    [Table(Name = "Base")]
    public class Child2 : Base
    {
        [Column(CanBeNull = true)] public int Age { get; set; }

        public Child2()
        {
            Code = "Child2";
        }
    }

    internal class Program
    {
        static void Main(string[] args)
        {
            using (var db = new DataConnection(new DataOptions().UseSqlCe(@"Data Source=test.sdf")))
            {
                if (!File.Exists("test.sdf"))
                {
                    SqlCeTools.CreateDatabase("test.sdf");
                    db.CreateTable<Base>();
                    db.Insert(new Child() { Id = Guid.NewGuid(), Name = "Jane" });
                    db.Insert(new Child2() { Id = Guid.NewGuid(), Age = 10 });
                }

                var allChild2 = db.GetTable<Base>().Where(e => e.Code != "Child").ToList(); //correct SQL
                allChild2 = db.GetTable<Base>().Where(e => !(e is IChild) ).ToList(); //generates WHERE 1 = 0
            }
            Console.ReadKey();
        }
    }
}

db.GetTable<Base>().Where(e => !(e is IChild) ).ToList() generates incorrect SQL:

SELECT
	[e].[Code],
	[e].[Id],
	[e].[Name],
	[e].[Age]
FROM
	[Base] [e]
WHERE
	1 = 0

[alexey-leonovich]

@MaceWindu another broken aspect - some boolean variable equals true in WHERE:

using LinqToDB.Data;
using LinqToDB;
using System;
using System.IO;
using System.Linq;
using LinqToDB.DataProvider.SqlCe;
using LinqToDB.Mapping;

namespace InheritanceMappingGetByInterfaceTest
{
    [Table(Name = "Base")]
    [InheritanceMapping(Code = "Base", IsDefault = true, Type = typeof(Base))]
    [InheritanceMapping(Code = "BaseChild", Type = typeof(BaseChild))]
    [InheritanceMapping(Code = "Child", Type = typeof(Child))]
    [InheritanceMapping(Code = "Child2", Type = typeof(Child2))]
    public class Base
    {
        [Column(IsDiscriminator = true)] public string Code { get; set; }
        [Column] public Guid Id { get; set; }
    }

    [Table(Name = "Base")]
    public class BaseChild : Base
    {
        [Column(CanBeNull = true)] public string Name { get; set; }

        public BaseChild()
        {
            Code = "BaseChild";
        }
    }

    [Table(Name = "Base")]
    public class Child : BaseChild
    {
        [Column(CanBeNull = true)] public bool IsMale { get; set; }

        public Child()
        {
            Code = "Child";
        }
    }

    [Table(Name = "Base")]
    public class Child2 : BaseChild
    {
        [Column(CanBeNull = true)] public int Age { get; set; }

        public Child2()
        {
            Code = "Child2";
        }
    }

    internal class Program
    {
        static void Main(string[] args)
        {
            using (var db = new DataConnection(new DataOptions().UseSqlCe(@"Data Source=test.sdf")))
            {
                if (!File.Exists("test.sdf"))
                {
                    SqlCeTools.CreateDatabase("test.sdf");
                    db.CreateTable<Base>();
                    db.Insert(new Child() { Id = Guid.NewGuid(), Name = "Jane"});
                    db.Insert(new Child2() { Id = Guid.NewGuid(), Age = 10 });
                }

                bool additionalFlag = true;   //generates correct query when additionalFlag = false
                var children = db.GetTable<Base>().Where(e => e is BaseChild ? additionalFlag || e.Id != Guid.Empty : e.Id != Guid.Empty).ToList();   //THEN CASE WHEN () THEN 1 ELSE 0
            }
            Console.ReadKey();
        }
    }
}

When additionalFlag = false correct SQL is generated. When additionalFlag = true LinqToDB generates incorrect

THEN CASE
WHEN () THEN 1
ELSE 0

P. S. I post all these "another broken aspects" not to bother you but for future test when InheritanceMapping will be fixed at last.