使用现有的主数据库在EF上选择错误 [英] Select error on EF using existing master detail database

问题描述

实体框架v5看起来很酷，我尝试使用现有的 MSSQL（Azure）数据库从Linq-to-SQL切换。
但是关于EF的教程太复杂了。

数据库模式非常简单，如下（由存在的数据库生成）。

表和 SheetDetails 表与1对多关系相关。

  CREATE TABLE [dbo]。[Sheets]（
 [Id] INT IDENTITY（1，1）NOT NULL，
 [Grade] FLOAT（53）CONSTRAINT [DF_Sheets_Grade] DEFAULT（（0））NOT NULL，
 [标题] NVARCHAR（255）CONSTRAINT [DF_Sheets_Title] DEFAULT（''）NOT NULL，
 [描述] NVARCHAR（255）NULL，
 [难度] SMALLINT CONSTRAINT [DF_Sheets_Difficulty] DEFAULT（（0））NOT NULL，
 [Writer] NVARCHAR（255）CONSTRAINT [DF_Sheets_Writer] DEFAULT（''）NOT NULL，
 [Tag] NVARCHAR （255）NULL，
 [持续时间] FLOAT（53）CONSTRAINT [DF_Sheets_Duration] DEFAULT（（0））NOT NULL，
 [Timestamp] DATETIME CONSTRAINT [DF_Sheets_Times tamp] DEFAULT（（0））NOT NULL，
 [RowVersion] ROWVERSION NOT NULL，
 CONSTRAINT [PK_Sheets] PRIMARY KEY CLUSTERED（[Id] ASC）
）; 
 
 CREATE TABLE [dbo]。[SheetDetails]（
 [Id] INT IDENTITY（1，1）NOT NULL，
 [score] FLOAT（53）CONSTRAINT [DF_SheetDetails_Score] DEFAULT（（0））NOT NULL，
 [Number] SMALLINT CONSTRAINT [DF_SheetDetails_Number] DEFAULT（（0））NOT NULL，
 [SubNumer] SMALLINT NULL，
 [IsRandom] BIT CONSTRAINT [ DF_SheetDetails_IsRandom] DEFAULT（（0））NOT NULL，
 [AnswerType] SMALLINT CONSTRAINT [DF_SheetDetails_AnswerType] DEFAULT（（0））NOT NULL，
 [RowVersion] ROWVERSION NOT NULL，
 [SheetDetail_Sheet] INT CONSTRAINT [DF_SheetDetails_SheetDetail_Sheet] DEFAULT（（0））NOT NULL，
 [SheetDetail_Question] INT CONSTRAINT [DF_SheetDetails_SheetDetail_Question] DEFAULT（（0））NOT NULL，
 CONSTRAINT [SheetDetail_Sheet] FOREIGN KEY（[SheetDetail_Sheet]）参考[dbo]。[Sheets]（[Id]）ON DELETE CASCADE 
）; 
  

请注意，FK名称是SheetDetails表中的SheetDetail_Sheet。

结果图如下。

下一页，也许我需要写EntityTypeConfiguration。
我试过它作为问题1。

问题1。
我写了模型创建配置如下，但没有运气。是错的吗很难知道如何使用这个简单的数据库模型编写正确的配置。

  protected override void OnModelCreating（DbModelBuilder modelBuilder）
 {
 modelBuilder.Configurations.Add（new SheetsConfiguration（））; 
 base.OnModelCreating（modelBuilder）; 
} 
 
 public class SheetsConfiguration：EntityTypeConfiguration< Sheets> 
 {
 public SheetsConfiguration（）
：base（）
 {
 HasKey（p => p.Id）; 
属性（p => p.Id）.HasColumnName（Id）。HasDatabaseGeneratedOption（DatabaseGeneratedOption.Identity）.IsRequired（）; 
属性（p => p.RowVersion）.IsConcurrencyToken（）。IsRowVersion（）; 
 HasOptional（p => p.SheetDetails）.WithMany（）。Map（x => x.MapKey（SheetDetail_Sheet））; 
 ToTable（Sheets）; 
} 
} 
  

我使用

$ b执行简单查询
$ b

  var result = _db.Sheets.Where（sheet => sheet.Id == id）.ToList（）; 
  

然后，我得到一个错误列名无效列表SheetDetail_Sheet，执行查询如下。 p>

  SELECT 
 [Extent1]。[Id] AS [Id]，
 [Extent1] AS [Grade]，
 [Extent1]。[标题] AS [标题]，
 [Extent1]。[描述] AS [说明]，
 [Extent1]。[难度] AS [难度]，
 [Extent1]。[Writer] AS [Writer]，
 [Extent1]。[Tag] AS [Tag]，
 [Extent1]。[Duration] AS [Duration] ，
 [Extent1]。[Timestamp] AS [Timestamp]，
 [Extent1]。[RowVersion] AS [RowVersion]，
 [Extent1]。[SheetDetail_Sheet] AS [SheetDetail_Sheet] 
 FROM [dbo]。[Sheets] AS [Extent1] 
 WHERE [Extent1]。[Id] = @ p__linq__0 
  

我可以理解，因为 SheetDetail_Sheet只是一个FK ，并且不存在于edmx属性和数据库列中。
我该如何解决？

我不想编辑自动生成的模型文件，因为它可以被覆盖。也许这似乎是通过EntityTypeConfiguration实现的。

问题2。
有什么有用的和轻量级的引用与常用的主-detail数据库模型？

我很难从现有数据库开始。
stackoverflow，asp.net，blogs等...到很多教程，但很难找到像我的例子。

谢谢。

解决方案

我建议使用Model First创建数据库模型（尽管您最初使用数据库）。获取数据库模型的好文章是 here 。只要忽略MVC的东西，并停止在生成强类型的实体类，因为您正在使用EF5将为您生成POCO类。 Edmx应该处理所有的FK和属性，你不需要担心配置器。

Entity Framework v5 looks cool and I try to switch from Linq-to-SQL with existing MSSQL (Azure) database. but tutorials about EF are too complex to follow.

The database schema is pretty simple as follows (generated by exist db).

Sheets and SheetDetails tables are connected with 1-to-many relationship.

CREATE TABLE [dbo].[Sheets] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [Grade]       FLOAT (53)     CONSTRAINT [DF_Sheets_Grade] DEFAULT ((0)) NOT NULL,
    [Title]       NVARCHAR (255) CONSTRAINT [DF_Sheets_Title] DEFAULT ('') NOT NULL,
    [Description] NVARCHAR (255) NULL,
    [Difficulty]  SMALLINT       CONSTRAINT [DF_Sheets_Difficulty] DEFAULT ((0)) NOT NULL,
    [Writer]      NVARCHAR (255) CONSTRAINT [DF_Sheets_Writer] DEFAULT ('') NOT NULL,
    [Tag]         NVARCHAR (255) NULL,
    [Duration]    FLOAT (53)     CONSTRAINT [DF_Sheets_Duration] DEFAULT ((0)) NOT NULL,
    [Timestamp]   DATETIME       CONSTRAINT [DF_Sheets_Timestamp] DEFAULT ((0)) NOT NULL,
    [RowVersion]  ROWVERSION     NOT NULL,
    CONSTRAINT [PK_Sheets] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[SheetDetails] (
    [Id]                   INT        IDENTITY (1, 1) NOT NULL,
    [Score]                FLOAT (53) CONSTRAINT [DF_SheetDetails_Score] DEFAULT ((0)) NOT NULL,
    [Number]               SMALLINT   CONSTRAINT [DF_SheetDetails_Number] DEFAULT ((0)) NOT NULL,
    [SubNumer]             SMALLINT   NULL,
    [IsRandom]             BIT        CONSTRAINT [DF_SheetDetails_IsRandom] DEFAULT ((0)) NOT NULL,
    [AnswerType]           SMALLINT   CONSTRAINT [DF_SheetDetails_AnswerType] DEFAULT ((0)) NOT NULL,
    [RowVersion]           ROWVERSION NOT NULL,
    [SheetDetail_Sheet]    INT        CONSTRAINT [DF_SheetDetails_SheetDetail_Sheet] DEFAULT ((0)) NOT NULL,
    [SheetDetail_Question] INT        CONSTRAINT [DF_SheetDetails_SheetDetail_Question] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [SheetDetail_Sheet] FOREIGN KEY ([SheetDetail_Sheet]) REFERENCES [dbo].[Sheets] ([Id]) ON DELETE CASCADE
);

Note that the FK name is SheetDetail_Sheet in SheetDetails table.

Result diagram is as below.

Next, maybe I need to write EntityTypeConfiguration. I tried it as question 1.

Question 1. I wrote model creating configuration as follows but no luck. is it wrong? hard to know how to write right configuration with this simple database model.

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new SheetsConfiguration());
        base.OnModelCreating(modelBuilder);
    }

    public class SheetsConfiguration : EntityTypeConfiguration<Sheets>
    {
        public SheetsConfiguration()
            : base()
        {
            HasKey(p => p.Id);
            Property(p => p.Id).HasColumnName("Id").HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).IsRequired();
            Property(p => p.RowVersion).IsConcurrencyToken().IsRowVersion();
            HasOptional(p => p.SheetDetails).WithMany().Map(x => x.MapKey("SheetDetail_Sheet"));
            ToTable("Sheets");
        }
    }

I execute simple query using

var result = _db.Sheets.Where(sheet => sheet.Id == id).ToList();

And then, I got an error "Invalid column name SheetDetail_Sheet" with executed query as follows.

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[Grade] AS [Grade], 
[Extent1].[Title] AS [Title], 
[Extent1].[Description] AS [Description], 
[Extent1].[Difficulty] AS [Difficulty], 
[Extent1].[Writer] AS [Writer], 
[Extent1].[Tag] AS [Tag], 
[Extent1].[Duration] AS [Duration], 
[Extent1].[Timestamp] AS [Timestamp], 
[Extent1].[RowVersion] AS [RowVersion], 
[Extent1].[SheetDetail_Sheet] AS [SheetDetail_Sheet]
FROM [dbo].[Sheets] AS [Extent1]
WHERE [Extent1].[Id] = @p__linq__0

I can understand because SheetDetail_Sheet is just an FK and not exist in the edmx properties and database column. How can I fix it?

I don't want to edit auto-generated model file because it can be overwritten. maybe it seems to be achieved with EntityTypeConfiguration.

Question 2. Is there any useful and lightweight reference with the commonly used master-detail database model?

I'm in trouble to start with existing database. stackoverflow, asp.net, blogs, etc...to many tutorials but hard to find an example like my case.

Thank you.

解决方案

I'd suggest using Model First to create your database model (though you're using database first). A good article for getting the database model is here. Just ignore the MVC stuff, and stop at "Generating Strongly Typed Entity Classes" as you are using EF5 which will generate the POCO classes for you. The Edmx should handle all of the FKs and properties for you and you won't need to worry about the configurator.

这篇关于使用现有的主数据库在EF上选择错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！