Screenshot of errorI am getting an SQLException pointing at the 'GetAllItems()' method below in the ItemsService.
"Invalid column name OrderID1"

错误的屏幕截图我收到指向ItemsService中下面的‘GetAllItems()’方法的SQLException。“无效的列名OrderID1”

I have two entities, the Order and Item and I wanted the OrderID associated with each item to be display in the Item views (CRUD)

我有两个实体，Order和Item，我希望与每个Item相关联的OrderID显示在Item view(CRUD)中

 public IEnumerable<Item> GetAllItems()
 {
     return _context.Items.ToList();
 }

Items Controller

项目控制器

    // GET: Items/Create
    public IActionResult Create()
    {
        var Orders = _ordersService.GetAllOrders();
        var orderIDList = new SelectList(Orders, "OrderID", "OrderID");
        var item = new Item
        {
            OrderIDList = orderIDList
        };

        return View(item);
    }

    // POST: Items/Create
    [HttpPost]
    [ValidateAntiForgeryToken]
    public IActionResult Create([Bind("ItemID,ItemName,Price,QuantityInStock,OrderID")] Item item)
    {
        if (ModelState.IsValid)
        {
            _itemsService.CreateItem(item);
            return RedirectToAction(nameof(Index));
        }
        return View(item);
    }

    // GET: Items/Edit/5
    public IActionResult Edit(int? id)
    {
        if (id == null)
        {
            return NotFound();
        }

        var item = _itemsService.GetItemByID(id.Value);

        if (item == null)
        {
            return NotFound();
        }

        var Orders = _ordersService.GetAllOrders();
        var orderIDList = new SelectList(Orders, "OrderID", "OrderID");
        {
            OrderIDList = orderIDList;
        };

        return View(item);
        
    }

Item Model

物品模型

using Microsoft.AspNetCore.Mvc.Rendering;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace OrderEase.Models
{
    public class Item
    {
        [Key]
        [Display(Name = "Item ID")]
        public int ItemID { get; set; }

        [Display(Name = "Item Name")]
        public string ItemName { get; set; }
        public decimal Price { get; set; }

        [Display(Name = "Quantity in stock")]
        [Required(ErrorMessage = "Quantity in stock is required")]
        [Range(0, int.MaxValue, ErrorMessage = "Quantity must be non-negative.")]
        public int QuantityInStock { get; set; }

        //One-to-Many Relationship with Order
        [Display(Name ="Order ID")]
        public int OrderID { get; set; } //Foreign Key referecing Order
        public virtual Order Order { get; set; }

        //ViewModel-related properties
        [NotMapped]
        [ForeignKey("SelectedOrderID")]
        public int SelectedOrderID { get; set; }

        [NotMapped]
        [Display(Name = "Order ID List")]
        public SelectList OrderIDList { get; set; }


    }
}

Here is my DbContext class:

以下是我的DbContext类：

using Microsoft.EntityFrameworkCore;
using OrderEase.Models;

namespace OrderEase.Data
{
    public class OrderDbContext : DbContext
    {
        public OrderDbContext(DbContextOptions<OrderDbContext> options) : base(options)
        {

        }

        //Configure Relationships
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //Using Fluent API for Configuration
            modelBuilder.Entity<Order>()
                .HasKey(o => o.OrderID); //Configure Primary Key
            
            modelBuilder.Entity<Order>()
                .Property(o => o.Quantity)
                .IsRequired();

            modelBuilder.Entity<Order>()
                .Property(o => o.TotalPrice)
                .IsRequired()
                .HasPrecision(18, 2);

            modelBuilder.Entity<Order>()
                .Property(o => o.OrderDate)
                .IsRequired();

            modelBuilder.Entity<Order>()
                .Property(o => o.DeliveryDate)
                .IsRequired();


            modelBuilder.Entity<Order>()
                .Property(o => o.Supplier)
                .IsRequired()
                .HasMaxLength(255);

            //One-to-Many Relationship
            modelBuilder.Entity<Order>()
                .HasMany(o => o.Items)
                .WithOne(i => i.Order).HasForeignKey(i => i.OrderID);

            //Item Configuration
            modelBuilder.Entity<Item>()
                .HasKey(i => i.ItemID);

            modelBuilder.Entity<Item>()
                .Property(i => i.ItemName)
                .IsRequired()
                .HasMaxLength(255);

            modelBuilder.Entity<Item>()
                .Property(i => i.Price)
                .IsRequired()
                .HasPrecision(18, 2);

            modelBuilder.Entity<Item>()
                .Property(i => i.QuantityInStock)
                .IsRequired();

            //Relationship
            modelBuilder.Entity<Item>()
                .HasOne(i => i.Order)
                .WithMany(o => o.Items)
                .HasForeignKey(i => i.OrderID);

            base.OnModelCreating(modelBuilder);
        }

        //Defining Tables

        public DbSet<Order> Orders { get; set; }
        public DbSet<Item> Items { get; set; }
    }
}

What is going on, and how can I resolve this error?

这是怎么回事，我如何解决这个错误？

Here is the rest of the code: https://github.com/Bless-Siba/OrderEase-Final/tree/master/Models

下面是代码的其余部分：https://github.com/Bless-Siba/OrderEase-Final/tree/master/Models

Screenshot of Error

错误的屏幕截图

I suspect there is a bit more to your Item table, or possibly another entity you have not included. For instance:

我怀疑您的项目表中还有更多内容，或者可能还有其他您没有包含的实体。例如：

    [NotMapped]
    [ForeignKey("SelectedOrderID")]
    public int SelectedOrderID { get; set; }

This is not valid. The [ForeignKey] attribute can be put on either the FK property or the navigation property, but if on the FK property it should point to the navigation property, not itself. I.e. [ForeignKey("SelectedOrder")]. It would never be combined with a [NotMapped] attribute. That tells EF this is not related to a column in the database.

这是无效的。[ForeignKey]属性可以放在FK属性或导航属性上，但如果放在FK属性上，它应该指向导航属性，而不是它本身。即。[ForeignKey(“SelectedOrder”)]。它永远不会与[NotMaps]属性组合在一起。这告诉EF这与数据库中的列无关。

AS for the dual mapping:

至于对偶映射：

modelBuilder.Entity<Order>()
    .HasMany(o => o.Items)
    .WithOne(i => i.Order).HasForeignKey(i => i.OrderID);

modelBuilder.Entity<Item>()
    .HasOne(i => i.Order)
    .WithMany(o => o.Items)
    .HasForeignKey(i => i.OrderID);

You should remove one or the other. Pick an entity to serve as the root for a relationship and configure it from that side. While having both mappings shouldn't cause problems, it opens the door to leaving something misconfigured resulting in errors or "weird" behaviour.

您应该删除其中一个。选择一个实体作为关系的根，并从该端配置它。虽然拥有这两个映射应该不会造成问题，但它可能会导致错误配置，从而导致错误或“奇怪”的行为。

As for the error itself, this is commonly caused by a bad assumption about EF's convention around resolving FKs. EF can locate FKs such as:

至于误差本身，这通常是由于对EF关于求解FK的约定的错误假设造成的。EF可以定位FK，例如：

public int OrderId { get; set; }
public Order Order { get; set; }

... where you don't even need to add a [ForeignKey] attribute or set up the mapping with the modelBuilder. However, many expect that it should also figure out something like:

..。其中您甚至不需要添加[ForeignKey]属性或设置与Model Builder的映射。然而，许多人预计它也应该想出一些类似的东西：

public int SelectedOrderId { get; set; }
public Order SelectedOrder { get; set; }

... except it won't. It will actually look for something like OrderId1 given the entity already has an "Order" navigation property and FK; similar to the error you are seeing. The EF convention is based on the Type of the navigation property, not the property name. If SelectedOrder is a type Order, EF is using that as the basis for the FK name. This means that a [ForeignKey] attribute or modelBuilder mapping is required. In your case you have a "SelectedOrderID" property which is suspicious, but I don't see a "SelectedOrder" navigation property which may, or may not exist. If you have other entities in your system set up, look for any that have a navigation property of Type "Order" that have a different name, and be sure that these are explicitly mapped.

..。但它不会。它实际上会查找类似于OrderId1的内容，因为实体已经有了“Order”导航属性和FK；类似于您看到的错误。EF约定基于导航属性的类型，而不是属性名称。如果SelectedOrder是类型Order，EF将使用该类型作为FK名称的基础。这意味着需要[ForeignKey]属性或Model Builder映射。在您的例子中，您有一个可疑的“SelectedOrderID”属性，但我没有看到一个可能存在也可能不存在的“SelectedOrder”导航属性。如果您在系统中设置了其他实体，请查找具有不同名称的“Order”类型导航属性的任何实体，并确保这些实体已显式映射。

When a foreign key is used by multiple time (by many relations), the column's name is followed by a number, like OrderID1.

当一个外键被多次(由许多关系)使用时，列的名称后面跟一个数字，如OrderID1。

In your case, the foreign key property Item.OrderID is used in these two relationships :

在本例中，外键属性Item.OrderID用于这两个关系：

public class Item
{
    ...
    public int OrderID { get; set; }Order
    public virtual Order Order { get; set; }
}

public class Order
{
    ...
    public ICollection<Item> Items { get; set; }
    public virtual Item Item { get; set; }
}

The navigation property Order.Items use the foreign key Item.OrderID as defined in OnModelCreating.

导航属性Order.Items使用在OnModelCreating中定义的外键Item.OrderID。

The navigation property Order.Item isn't defined in OnModelCreating, but by convention it's linked to Item.Order that use the foreign key Item.OrderID.

导航属性Order.Item没有在OnModelCreating中定义，但是按照约定，它链接到使用外键Item.OrderID的Item.Order。

The property Item.OrderID is used two times as foreign key and mapped to column OrderID and OrderID1...

属性Item.OrderID被两次用作外键，并映射到列OrderID和OrderID1...

The solution is to specify the foreign key to all navigation properties like :

解决方案是为所有导航属性指定外键，如：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>()
        .HasMany(o => o.Items)
        .WithOne(i => i.Order)
        .HasForeignKey(i => i.OrderID);

    modelBuilder.Entity<Order>()
        .HasOne(o => o.Item)
        .WithOne()
        .HasForeignKey<Order>("ItemId");
}