locked
Table Per Type inheritance hierarchy unions irrelevant tables RRS feed

  • Question

  • Currently I am dealing with some real strange behavior in the way Entity Framework handles inheritance when generating queries. I hope some of you might have an idea as to what is going on and can point me in the right direction. 

    I have a toy example which shows my data structure but does not reproduce the strange behavior but I hope is helpful to discuss the issue.

    public class Point { public double x { get; set; } public double y { get; set; } public double z { get; set; } } [Table("Base")] public abstract class Base { public int Id { get; set; }

    public string Type { get; set; } } [Table("SubType1")] class SubType1 : Base { public double Spacing { get; set; } public Point Location { get; set; } } [Table("Subtype2")] class SubType2 : Base { public string PathToFile { get; set; } } [Table("Model")] public abstract class Model : Base { public Point Center { get; set; } public int ParentId { get; set; } } [Table("SingleModel")] class SingleModel : Model { public string Description { get; set; } } [Table("CompoundModel")] class CompoundModel : Model { public virtual ICollection<Model> Models { get; set; } }

    Moreover, a number of classes (like for example CompoundModel) aren't declared explicitly as a DbSet in the DbContext but rather registered dynamically in the OnModelCreating. To access these we use Set<T> or OfType<T>.

    So if I execute a query as for example the next one:

    using (var db = new Db()) {
         var result = db.Set<CompoundModel>().FirstOrDefault();
    }

    I will get a query as follows:

    SELECT 
    "Limit1"."C6" AS "C1",
    "Limit1"."C5" AS "C2",
    "Limit1"."C1" AS "C3",
    "Limit1".type,
    "Limit1"."C2" AS "C4",
    "Limit1"."C3" AS "C5",
    "Limit1"."C4" AS "C6"
    FROM ( SELECT 
    	"UnionAll1".id AS "C1",
    	"UnionAll1".parent_id AS "C2",
    	"UnionAll1".composite_model_id AS "C3",
    	"Extent4".type,
    	CAST('0X0X0X' AS varchar) AS "C5",
    	1 AS "C6"
    	FROM    (SELECT 
    		"Extent1".id,
    		"Extent1".parent_id,
    		"Extent1".composite_model_id,		
    		FROM "Models" AS "Extent1"
    	UNION ALL
    		SELECT 
    		"Extent2".id,
    		CAST(NULL AS uuid) AS "C1",
    		CAST(NULL AS uuid) AS "C2",
    		CAST(NULL AS uuid) AS "C3"
    		FROM "SubType1" AS "Extent2") AS "UnionAll1"
    	INNER JOIN "CompoundModel" AS "Extent3" ON "UnionAll1".id = "Extent3".id
    	INNER JOIN "Base" AS "Extent4" ON "UnionAll1".id = "Extent4".id
    	LIMIT 1 
    )  AS "Limit1"

    As you can see SubType1 is included in the output of the generated query while is not requested for. 

    I get the correct query if I remove the line of code 

    public Point Location { get; set; }

    From SubType1, this is just too weird to be true. 

    The correct query is:

    SELECT 
    "Limit1"."C2" AS "C1",
    "Limit1"."C1" AS "C2",
    "Limit1".id,
    "Limit1".type,
    "Limit1".parent_id,
    "Limit1".compound_model_id,
    FROM ( SELECT 
    	"Extent1".id,
    	"Extent2".compound_model_id,	
    	"Extent2".parent_id,
    	"Extent3".type,	
    	CAST('0X0X0X' AS varchar) AS "C1",
    	1 AS "C2"
    	FROM   "CompoundModel" AS "Extent1"
    	INNER JOIN "Model" AS "Extent2" ON "Extent1".id = "Extent2".id
    	INNER JOIN "Base" AS "Extent3" ON "Extent1".id = "Extent3".id
    	LIMIT 1 
    )  AS "Limit1"

    Note SubType one will have the following schema definition.

    CREATE TABLE public."SubType1"
    (
        "Id" integer NOT NULL,
        "Spacing" double precision NOT NULL,
        "Location_x" double precision NOT NULL,
        "Location_y" double precision NOT NULL,
        "Location_z" double precision NOT NULL,
        CONSTRAINT "PK_SubType1" PRIMARY KEY ("Id"),
        CONSTRAINT "FK_SubType1_TypeConstraint_From_Base_To_SubType1" FOREIGN KEY ("Id")
            REFERENCES public."Base" ("Id") MATCH SIMPLE
            ON UPDATE NO ACTION
            ON DELETE NO ACTION
    )

    How is it possible that adding a propery of type Point to SubType1 will cause SubType1 to show up in the query for a CompoundModel?

    Any help will be greatly appreciated.



    • Edited by gajus Monday, July 9, 2018 1:29 PM
    Monday, July 9, 2018 1:27 PM

All replies

  • Hi gajus,

    Based on your description and related code, I create a demo as below, it works as excepted. could you please share a complete sample, which could reproduce the issue.

    #Models.

    public class Point
        {
            public int Id { get; set; }
            public double x { get; set; }
            public double y { get; set; }
            public double z { get; set; }
        }
    
        [Table("Base")]
        public abstract class Base
        {
            public int Id { get; set; }
            public string Type { get; set; }
        }
    
        [Table("SubType1")]
        class SubType1 : Base
        {
            public double Spacing { get; set; }
            public Point Location { get; set; }
        }
    
        [Table("Subtype2")]
        class SubType2 : Base
        {
            public string PathToFile { get; set; }
        }
    
        [Table("Model")]
        public abstract class Model : Base
        {
            public Point Center { get; set; }
            public int ParentId { get; set; }
        }
    
        [Table("SingleModel")]
        class SingleModel : Model
        {
            public string Description { get; set; }
        }
    
        [Table("CompoundModel")]
        class CompoundModel : Model
        {
            public virtual ICollection<Model> Models { get; set; }
        }

    #DbContext

    public partial class TPTContext : DbContext
        {
            public TPTContext()
                : base("name=TPTContext")
            {
            }
    
            public DbSet<Base> Bases { get; set; }
    
            public DbSet<Model> Models { get; set; }
    
            public DbSet<Point> Points { get; set; }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
            }
        }

    #Usage

     static void Main(string[] args)
            {
                using (var db = new TPTContext())
                {
                    db.Database.Log = Console.WriteLine;
                    var result = db.Set<CompoundModel>().FirstOrDefault();
    
                    Console.ReadLine();
                }
            }

    Best regards,

    Zhanglong 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 10, 2018 7:29 AM
  • Hi Zhanglong, thanks for your quick response. I have stripped everything from my codebase in order to provide the minimum amount of code that reproduces the problem. 

    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity;
    using System.Linq;
    
    namespace MSDN_Example
    {
        #region Complex types
        public class Point3D
        {
            public double x { get; set; }
            public double y { get; set; }
            public double z { get; set; }
        }
    
        public class Orientation
        {
            protected Orientation() { }
            public double Roll { get; set; }
            public double Pitch { get; set; }
            public double Azimuth { get; set; }
        }
        #endregion Complex types
    
        #region Entities
        [Table("SourceContainer")]
        public class SourceContainer
        {
            [Required, Index]
            public Guid Id { get; set; }
    
            public virtual ICollection<Source> Sources { get; set; }
        }
    
        public abstract class Source
        {
            [Index]
            public Guid Id { get; set; }
    
            public string Type { get; set; } // Remove this property and the problem does not occur
        }
    
        [Table("Images")]
        public class Image : Source
        {
            public Orientation Orientation { get; set; } // Remove this property and Image does not occur in the sql generated by EF
        }
    
        [Table("Scans")]
        public class Scan : Source
        {
            public Point3D Translation { get; set; } // Remove this property and Image does not occur in the sql generated by EF
        }
    
        [Table("Models")]
        public abstract class Model : Source
        {
            public Point3D Center { get; set; }
    
            public string Name { get; set; }
        }
    
        [Table("CompoundModel")]
        public class CompoundModel : Model
        {
            public virtual ICollection<Model> Models { get; set; }
        }
    
        // Remove this class and the problem should not occur
        [Table("SingleModel")]
        public class SingleModel : Model { }
    
        #endregion Entities
    
        #region DbCtx
        public class Db : DbContext
        {
            public Db() : base("MyDb")
            {
                this.Database.Log = Console.WriteLine;
            }
    
            protected override void OnModelCreating(DbModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Image>();
                modelBuilder.Entity<Scan>();
                modelBuilder.Entity<Model>();
                modelBuilder.Entity<SourceContainer>();// Remove this line and the problem does not occur
                base.OnModelCreating(modelBuilder);
            }
        }
        #endregion DbCtx
    
        class Program
        {
            static void Main(string[] args)
            {
                using (Db db = new Db())
                {
                    var result = db.Set<CompoundModel>()
                        .FirstOrDefault();
                }
            }
        }
    }

    Which produces the following output:

    SELECT
        [Limit1].[C3] AS [C1],
        [Limit1].[C2] AS [C2],
        [Limit1].[C1] AS [C3],
        [Limit1].[Type] AS [Type],
        [Limit1].[Name] AS [Name],
        [Limit1].[Center_x] AS [Center_x],
        [Limit1].[Center_y] AS [Center_y],
        [Limit1].[Center_z] AS [Center_z],
        [Limit1].[CompoundModel_Id] AS [CompoundModel_Id],
        [Limit1].[SourceContainer_Id] AS [SourceContainer_Id]
        FROM ( SELECT TOP (1)
            [UnionAll2].[Id] AS [C1],
            [Extent4].[Type] AS [Type],
            [Extent4].[SourceContainer_Id] AS [SourceContainer_Id],
            [Extent5].[CompoundModel_Id] AS [CompoundModel_Id],
            [Extent5].[Name] AS [Name],
            [Extent5].[Center_x] AS [Center_x],
            [Extent5].[Center_y] AS [Center_y],
            [Extent5].[Center_z] AS [Center_z],
            '0X0X0X' AS [C2],
            1 AS [C3]
            FROM    (SELECT
                [Extent1].[Id] AS [Id]
                FROM [dbo].[Images] AS [Extent1]
            UNION ALL
                SELECT
                [Extent2].[Id] AS [Id]
                FROM [dbo].[CompoundModel] AS [Extent2]
            UNION ALL
                SELECT
                [Extent3].[Id] AS [Id]
                FROM [dbo].[Scans] AS [Extent3]) AS [UnionAll2]
            INNER JOIN [dbo].[Sources] AS [Extent4] ON [UnionAll2].[Id] = [Extent4].[Id]
            INNER JOIN [dbo].[Models] AS [Extent5] ON [UnionAll2].[Id] = [Extent5].[Id]
        )  AS [Limit1]
    
    Note that I am using EF 6.2. Also removing the Model Class and having CompoundModel inherit directly from Source resolves the problem. I suspect the inheritance in the data model has something to do with it but it is a data model that I inherited and can not do much about.

    Thanks in advance.

    Best regards gajus

    Tuesday, July 10, 2018 3:05 PM
  • Hi gajus,

    Based on your description and related code, I reproduce your issue on my side, I think it is a entity framework generated issue. since entity framework has open sourced, you could check related source. 

    https://github.com/aspnet/EntityFramework6

    As a workaround, we could remove Model's inheritance, like this:

    [Table("Models")]
        public abstract class Model 
        {
            public Guid Id { get; set; }
    
            public string Type { get; set; } // Remove this property and the problem does not occur
    
            public Point3D Center { get; set; }
    
            public string Name { get; set; }
        }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 11, 2018 1:40 AM
  • Thanks Zhanglong. Your help is very much appreciated.
    Wednesday, July 11, 2018 6:06 AM