C# 实体框架 - 无效的列名“*_ID”

Question

提问by Clarence Klopfstein

I've narrowed this down to some issue between Code First and Database first EF, but I'm not sure how to fix it. I'll try to be as clear as I can, but I honestly am missing some of the understanding here myself. This is Entity Framework 4.4

我已经将其范围缩小到 Code First 和 Database first EF 之间的一些问题，但我不确定如何解决它。我会尽量说清楚，但老实说，我自己在这里错过了一些理解。这是实体框架 4.4

I inherited a project where Entity Framework was used, but many of the actual files were deleted with no real way to go back. I re-added EF (Database first) and replicated a T4 setup that the project was built around. It generated code versions of all the database models and a DBContext code file.

我继承了一个使用 Entity Framework 的项目，但许多实际文件被删除，无法返回。我重新添加了 EF（数据库优先）并复制了一个围绕该项目构建的 T4 设置。它生成了所有数据库模型的代码版本和一个 DBContext 代码文件。

If my connection string looks like a "normal" .NET connection string I get an error about an invalid column Name "ProcessState_ID" does not exist. ProcessState_ID is not in the code base at all, it is not in the EDMX file or anything. This appears to be some automatic EF conversion in the query.

如果我的连接字符串看起来像一个“正常”的 .NET 连接字符串，我会收到一个关于无效列名称“ProcessState_ID”不存在的错误。ProcessState_ID 根本不在代码库中，也不在 EDMX 文件或任何东西中。这似乎是查询中的一些自动 EF 转换。

When I make the connection string match the Entity Framework model it works fine.

当我使连接字符串与实体框架模型匹配时，它工作正常。

Now in trying to match the previous code with Entity Framework I'd like to keep the "normal" .NET connection string.

现在，在尝试将以前的代码与实体框架相匹配时，我想保留“正常”的 .NET 连接字符串。

So I have two questions here: 1. What is a good way to go from a normal connection string to an EF connection string in code? 2. Is there another fix here that I'm not seeing to stop the invalid column name error?

所以我在这里有两个问题： 1. 在代码中从普通连接字符串到 EF 连接字符串的好方法是什么？2. 此处是否有其他修复方法可以阻止无效的列名错误？

Answer 1

回答by drewid

Check to see if you have any ICollections.

检查您是否有任何 ICollections。

What I have figured out is when you have an ICollection that references a table and there is no column that it can figure out, it creates one for you to try to make the connection between the tables. This specifically happens with ICollection and has driven me "batty" trying to figure it out.

我发现当你有一个引用表的 ICollection 并且没有它可以找出的列时，它会为你创建一个来尝试在表之间建立连接。这特别发生在 ICollection 上，并促使我“疯狂”地试图弄清楚。

Answer 2

回答by Ben

Holy cow - after many hours of trying, I finally figured this out.

天啊 - 经过几个小时的尝试，我终于想通了。

I am doing EF6 database first and I was wondering about the "extent unknown column" error - it was generating table name underscore column name for some reason, and trying to find a nonexistent column.

我首先在做 EF6 数据库，我想知道“范围未知列”错误 - 它出于某种原因生成表名下划线列名，并试图找到一个不存在的列。

In my case, one of my tables had two foreign key references to the same primary key in another table - something like this:

在我的例子中，我的一个表有两个外键引用到另一个表中的同一个主键 - 像这样：

Animals            Owners
=======            ======
AnimalID (PK)      Pet1ID    <- FK to AnimalID
                   Pet2ID    <- also FK to AnimalID

EF was generating some weird column name like Owners_AnimalID1and Owners_AnimalID2and then proceeded to break itself.

EF产生了一些奇怪的列名状Owners_AnimalID1和Owners_AnimalID2，继而又打破本身。

The trick here is that these confusing foreign keys need to be registered with EF using Fluent API!

这里的诀窍是，需要使用 Fluent API 向 EF 注册这些令人困惑的外键！

In your main database context, override the OnModelCreatingmethod and change the entity configuration. Preferably, you'll have a separate file which extends the EntityConfigurationclass, but you can do it inline.

在您的主数据库上下文中，覆盖该OnModelCreating方法并更改实体配置。最好有一个单独的文件来扩展EntityConfiguration类，但您可以内联进行。

Any way you do it, you'll need to add something like this:

无论如何，您都需要添加如下内容：

public class OwnerConfiguration : EntityTypeConfiguration<Owner>
{
    public OwnerConfiguration()
    {
        HasRequired(x => x.Animals)
            .WithMany(x => x.Owners)  // Or, just .WithMany()
            .HasForeignKey(x => x.Pet1ID);
    }
}

And with that, EF will (maybe) start to work as you expect. Boom.

这样，EF 将（可能）开始按您的预期工作。繁荣。

Also, you'll get that same error if you use the above with a nullable column - just use .HasOptional()instead of .HasRequired().

另外，如果你使用上面用空列，你会得到同样的错误-只使用.HasOptional()代替.HasRequired()。

Here's the link that put me over the hump:

这是让我陷入困境的链接：

https://social.msdn.microsoft.com/Forums/en-US/862abdae-b63f-45f5-8a6c-0bdd6eeabfdb/getting-sqlexception-invalid-column-name-userid-from-ef4-codeonly?forum=adonetefx

And then, the Fluent API docs help out, especially the foreign key examples:

然后，Fluent API 文档会有所帮助，尤其是外键示例：

http://msdn.microsoft.com/en-us/data/jj591620.aspx

You can also put the configurations on the other end of the key, as described here:

您还可以将配置放在密钥的另一端，如下所述：

http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx.

http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx。

There's some new problems I'm running into now, but that was the huge conceptual gap that was missing. Hope it helps!

我现在遇到了一些新问题，但这是缺失的巨大概念差距。希望能帮助到你！

Answer 3

回答by Prisoner ZERO

This is a late entry for those (like me) who didn't immediately understand the other 2 answers.

对于那些没有立即理解其他 2 个答案的人（如我）来说，这是一个迟到的条目。

So...

所以...

EF is trying to map to the EXPECTED name from the PARENT TABLES KEY-REFERENCE...and since...the FOREIGN KEY name was "changed or shortened" in the databases CHILD TABLE relationship...you would get the message above.

EF 正试图从 PARENT TABLES KEY-REFERENCE 映射到 EXPECTED 名称......并且由于......在数据库 CHILD TABLE 关系中，外键名称被“更改或缩短”......你会得到上面的消息。

(this fix may differ between versions of EF)

（此修复可能因 EF 版本而异）

FOR ME THE FIX WAS:
ADDING the "ForeignKey" attribute to the model

对我来说，修复是：
将“ForeignKey”属性添加到模型中

public partial class Tour
{
    public Guid Id { get; set; }

    public Guid CategoryId { get; set; }

    [Required]
    [StringLength(200)]
    public string Name { get; set; }

    [StringLength(500)]
    public string Description { get; set; }

    [StringLength(50)]
    public string ShortName { get; set; }

    [StringLength(500)]
    public string TourUrl { get; set; }

    [StringLength(500)]
    public string ThumbnailUrl { get; set; }

    public bool IsActive { get; set; }

    [Required]
    [StringLength(720)]
    public string UpdatedBy { get; set; }

    [ForeignKey("CategoryId")]
    public virtual TourCategory TourCategory { get; set; }
}

Answer 4

回答by Ciaran Bruen

I also had this problem and it seems like there are a few different causes. For me it was having an id property mistakenly defined as int instead of long in the parent class that contained a navigation object. The id field in the database was defined as bigint which corresponds to long in C#. This didn't cause a compile time error but did cause the same run time error as the OP got:

我也有这个问题，似乎有几个不同的原因。对我来说，它有一个 id 属性错误地定义为 int 而不是包含导航对象的父类中的 long 。数据库中的 id 字段定义为 bigint，对应于 C# 中的 long。这并没有导致编译时错误，但确实导致了与 OP 相同的运行时错误：

// Domain model parent object
public class WidgetConfig 
{
    public WidgetConfig(long id, int stateId, long? widgetId)
    {
        Id = id;
        StateId = stateId;
        WidgetId = widgetId;
    }

    private WidgetConfig()
    {
    }

    public long Id { get; set; }

    public int StateId { get; set; }

    // Ensure this type is correct
    public long? WidgetId { get; set; } 

    public virtual Widget Widget { get; set; }
}

// Domain model object
public class Widget
{
    public Widget(long id, string name, string description)
    {
        Id = id;
        Name = name;
        Description = description;
    }

    private Widget()
    {
    }

    public long Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }
}

// EF mapping
public class WidgetConfigMap : EntityTypeConfiguration<WidgetConfig>
{
    public WidgetConfigMap()
    {
        HasKey(x => x.Id);
        ToTable(nameof(WidgetConfig));
        Property(x => x.Id).HasColumnName(nameof(WidgetConfig.Id)).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity).IsRequired();
        Property(x => x.StateId).HasColumnName(nameof(WidgetConfig.StateId));
        Property(x => x.WidgetId).HasColumnName(nameof(WidgetConfig.WidgetId));
    }
}   

// Service
public class WidgetsService : ServiceBase, IWidgetsService
{
    private IWidgetsRepository _repository;

    public WidgetsService(IWidgetsRepository repository)
    {
        _repository = repository;
    }

    public List<WidgetConfig> ListWithDetails()
    {
        var list = _repository.ListWithDetails();

        return new WidgetConfigMapping().ConvertModelListToDtoList(list).ToList();
    }
}   

// Repository
public class WidgetsRepository: BaseRepository<WidgetConfig, long>, IWidgetsRepository
{
    public WidgetsRepository(Context context)
        : base(context, id => widget => widget.Id == id)
    {
    }

    public IEnumerable<WidgetConfig> ListWithDetails()
    {
        var widgets = Query
            .Include(x => x.State)
            .Include(x => x.Widget);

        return widgets;
    }
}

Answer 5

回答by Renan

For me the problem is that I had the table mapped in my app twice - once via Code First, once via Database First.

对我来说，问题是我在我的应用程序中映射了两次表 - 一次通过代码优先，一次通过数据库优先。

Removing either one solves the problem in my case.

在我的情况下，删除任何一个都可以解决问题。

Answer 6

回答by RAM

Assumptions:

假设：

Table
OtherTable
OtherTable_ID

Table
OtherTable
OtherTable_ID

Now choose one of this ways:

现在选择以下方式之一：

A)

一种）

Remove ICollection<Table>

消除 ICollection<Table>

If you have some error related to OtherTable_IDwhen you are retrieving Table, go to your OtherTablemodel and make sure you don't have an ICollection<Table>in there. Without a relationship defined, the framework will auto-assume that you must have a FK to OtherTable and create these extra properties in the generated SQL.

如果您OtherTable_ID在检索时遇到一些错误Table，请转到您的OtherTable模型并确保ICollection<Table>那里没有。如果没有定义关系，框架将自动假定您必须具有指向 OtherTable 的 FK 并在生成的 SQL 中创建这些额外的属性。

All Credit of this answer is belongs to @LUKE. The above answer is his comment under @drewid answer. I think his comment is so clean then i rewrote it as an answer.

这个答案的所有功劳都属于@LUKE。上面的回答是他在@drewid 回答下的评论。我认为他的评论非常干净，然后我将其重写为答案。

B)

乙)

Add OtherTableIdto Table

添加OtherTableId到Table

and

和

Define OtherTableIdin the Tablein database

定义OtherTableId的Table数据库

Answer 7

回答by Pallavi

If you have foreign key references to the same table more than once, then you can use InverseProperty

如果您对同一个表有多次外键引用，那么您可以使用 InverseProperty

Something like this-

像这样的东西——

[InverseProperty("MyID1")]
public virtual ICollection<MyTable> set1 { get; set; }
[InverseProperty("MyID2")]
public virtual ICollection<MyTable> set2 { get; set; }

Answer 8

回答by Seth

In my case I was incorrectly defining a primary key made up of two foreign keys like this:

就我而言，我错误地定义了一个由两个外键组成的主键，如下所示：

HasKey(x => x.FooId);
HasKey(x => x.BarId);

HasRequired(x => x.Foo)
    .WithMany(y => y.Foos);
HasRequired(x => x.Bar);

The error I was getting was, "invalid column name Bar_ID".

我得到的错误是“无效的列名 Bar_ID”。

Specifying the composite primary key correctly fixed the problem:

正确指定复合主键解决了问题：

HasKey(x => new { x.FooId, x.BarId });

...

Answer 9

回答by neuronz

For me (using Visual Studio 2017 and the database-first model under Entity Framework 6.1.3), the problem went away after restarting Visual Studio and Rebuilding.

对我来说（使用 Visual Studio 2017 和 Entity Framework 6.1.3 下的数据库优先模型），重新启动 Visual Studio 和重建后问题就消失了。

Answer 10

回答by Prokurors

For me cause of this behavior was because of issue with defined mapping with Fluent API. I had 2 related types, where type A had optional type B object, and type B had many A objects.

对我来说，这种行为的原因是由于使用 Fluent API 定义映射的问题。我有 2 个相关类型，其中类型 A 有可选的类型 B 对象，类型 B 有许多 A 对象。

public class A 
{
    …
    public int? BId {get; set;}
    public B NavigationToBProperty {get; set;}
}
public class B
{
    …
    public List<A> ListOfAProperty {get; set;}
}

I had defined mapping with fluent api like this:

我用 fluent api 定义了映射，如下所示：

A.HasOptional(p=> p.NavigationToBProperty).WithMany().HasForeignKey(key => key.BId);

But the problem was, that type B had navigation property List<A>, so as a result I had SQLException Invalid column name A_Id

但问题是，那个类型 B 有导航属性List<A>，所以我有SQLException Invalid column name A_Id

I attached Visual Studio Debug to EF DatabaseContext.Database.Log to output generated SQL to VS Output->Debug window

我将 Visual Studio Debug 附加到 EF DatabaseContext.Database.Log 以将生成的 SQL 输出到 VS Output->Debug 窗口

db.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

And generated SQL had 2 relations from B table -> one with correct id and other with the A_Id

并且生成的 SQL 有 2 个来自 B 表的关系 -> 一个具有正确的 id，另一个与 A_Id

The issue for the problem was, that I did not add this B.List<A>navigation property into mapping.

问题的问题是，我没有将此B.List<A>导航属性添加到映射中。

So this is how in my case correct mapping had to be:

所以在我的情况下，正确的映射必须是这样的：

A.HasOptional(p=> p.NavigationToBProperty).WithMany(x => x.ListOfAProperty).HasForeignKey(key => key.BId);

C# 实体框架 - 无效的列名“*_ID”

提问by Clarence Klopfstein

回答by drewid

回答by Ben

回答by Prisoner ZERO

回答by Ciaran Bruen

回答by Renan

回答by RAM

A)

一种）

B)

乙)

回答by Pallavi

回答by Seth

回答by neuronz

回答by Prokurors

相关推荐

最近更新

标签

C# 实体框架 - 无效的列名“*_ID”

提问by Clarence Klopfstein

回答by drewid

回答by Ben

回答by Prisoner ZERO

回答by Ciaran Bruen

回答by Renan

回答by RAM

A)

一种）

B)

乙)

回答by Pallavi

回答by Seth

回答by neuronz

回答by Prokurors

相关推荐

c#显示Windows窗体

C# 使 Wcf 服务集成 Windows 身份验证

C# 填充 datagridview 组合框

C# 如何使用linq查找具有最大值的项目？

相关推荐

最近更新

标签