Screenshot of errorI am getting an SQLException pointing at the ‘GetAllItems()‘ method below in the ItemsService.
"Invalid column name 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)
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:
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
2
Answers
When a foreign key is used by multiple time (by many relations), the column’s name is followed by a number, like
OrderID1
.In your case, the foreign key property
Item.OrderID
is used in these two relationships :The navigation property
Order.Items
use the foreign keyItem.OrderID
as defined inOnModelCreating
.The navigation property
Order.Item
isn’t defined inOnModelCreating
, but by convention it’s linked toItem.Order
that use the foreign keyItem.OrderID
.The property
Item.OrderID
is used two times as foreign key and mapped to columnOrderID
andOrderID1
…The solution is to specify the foreign key to all navigation properties like :
I suspect there is a bit more to your Item table, or possibly another entity you have not included. For instance:
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.AS for the dual mapping:
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:
… 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:… 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.