Table Per Type继承层次结构unions不相关的表 [英] Table Per Type inheritance hierarchy unions irrelevant tables

查看：65
本文介绍了Table Per Type继承层次结构unions不相关的表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

目前，我正在处理Entity Framework在生成查询时处理继承的方式中的一些奇怪的行为。我希望你们中的一些人可能知道发生了什么，并且可以指出我正确的方向。 


我有一个玩具示例，它显示了我的数据结构，但没有重现奇怪的行为，但我希望有助于讨论这个问题。


 
公共课点
 {
 public double x {get;组; } 
 public double y {get;组; } 
 public double z {get;组; } 
} 
 
 [表（"基数"）] 
公共抽象类基数
 {
 public int Id {get;组; } 
 
 public string Type {get;组; } 
} 
 
 [表（"SubType1"）] 
 class SubType1：Base 
 {
 public double Spacing {get;组; } 
公共点位置{get;组; } 
} 
 
 [表（"Subtype2"）] 
 class SubType2：Base 
 {
 public string PathToFile {get;组; } 
} 
 
 [表格（"模型"）] 
公共抽象类模型：基础
 {
公共点中心{get;组; } 
 public int ParentId {get;组; } 
} 
 
 [表（"SingleModel"）] 
 class SingleModel：Model 
 {
 public string Description {get;组; } 
} 
 
 [表格（"CompoundModel"）] 
 class CompoundModel：Model 
 {
 public virtual ICollection< Model>模型{get;组; } 
}

此外，许多类（例如CompoundModel）未在DbContext中显式声明为DbSet，而是注册动态地在OnModelCreating中。要访问这些，我们使用  Set< T>或OfType< T>。


因此，如果我执行查询，例如下一个查询：



< pre class ="prettyprint"style =""> using（var db = new Db（））{
var result = db.Set< CompoundModel>（）。FirstOrDefault（）;
}

我将得到如下查询：

 SELECT 
" Limit1"。" C6" AS"C1"，
"Limit1"。"C5"。 AS"C2"，
"Limit1"。"C1"和"C1"。 AS"C3"，
"Limit1".type，
"Limit1"。"C2"和"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&qu​​ot; UnionAll1" .id =" Extent3" .id 
 INNER JOIN" Base" AS" Extent4" ON&qu​​ot; UnionAll1" .id =" Extent4&q uot; .id 
 LIMIT 1 
）AS"Limit1"

如您所见，SubType1包含在生成的查询的输出中，而不要求。 


如果删除代码行，我会得到正确的查询  

公共点位置{get;组; }

从SubType1来看，这太奇怪了。 



正确的查询是：

 SELECT 
" Limit1"。" C2" AS"C1"，
"Limit1"。"C1"和"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" ;模型"AS"Extent2" ON&qu​​ot; Extent1" .id =" Extent2" .id 
 INNER JOIN" Base" AS" Extent3" ON&qu​​ot; Extent1" .id =" Extent3" id 
 LIMIT 1 
）AS"Limit1"

注意SubType 1将具有以下模式定义。 

 CREATE TABLE public。" SubType1" 
（
"Id"整数NOT NULL，
"间距"双精度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（" FOREIGN KEY） ; Id"）
 REFERENCES public。" Base"（" Id"）MATCH SIMPLE 
 ON UPDATE NO ACTION 
 ON DELETE NO ACTION 
）

如何在SubType1中添加Point类型的推进会导致SubType1出现在查询中以显示CompoundModel？


任何帮助都会有很大帮助赞赏。









解决方案

嗨gajus，


基于在您的描述和相关代码中，我创建了如下的演示，它作为例外工作。请你分享一份完整的样本，可以重现这个问题。


#Models。

公共课点
 {
 public int Id {get;组; } 
 public double x {get;组; } 
 public double y {get;组; } 
 public double z {get;组; } 
} 
 
 [表（"基数"）] 
公共抽象类基数
 {
 public int Id {get;组; } 
 public string Type {get;组; } 
} 
 
 [表（"SubType1"）] 
 class SubType1：Base 
 {
 public double Spacing {get;组; } 
公共点位置{get;组; } 
} 
 
 [表（"Subtype2"）] 
 class SubType2：Base 
 {
 public string PathToFile {get;组; } 
} 
 
 [表格（"模型"）] 
公共抽象类模型：基础
 {
公共点中心{get;组; } 
 public int ParentId {get;组; } 
} 
 
 [表（"SingleModel"）] 
 class SingleModel：Model 
 {
 public string Description {get;组; } 
} 
 
 [表格（"CompoundModel"）] 
 class CompoundModel：Model 
 {
 public virtual ICollection< Model>模型{get;组; } 
}

#DbContext 

 public partial class TPTContext：DbContext 
 {
 public TPTContext（）
：base（" name = TPTContext"）
 {
} 
 
 public DbSet< Base>基地{得到;组; } 
 
 public DbSet< Model>模型{get;组; } 
 
 public DbSet< Point>积分{得;组; } 
 
 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（）; 
} 
}






祝你好运，


张龙 


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.


解决方案

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 


这篇关于Table Per Type继承层次结构unions不相关的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆