skip to Main Content

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

Screenshot of Error

2

Answers


  1. 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 :

    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.

    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.

    The property Item.OrderID is used two times as foreign key and mapped to column OrderID and 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");
    }
    
    Login or Signup to reply.
  2. 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.

    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:

    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:

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search