Getting error in Entity Framework when trying to add an item

asp.net-web-api asp.net-web-api2 c# entity-framework entity-framework-6

Question

I'm getting the below error when trying to add an item to the database context. I'm using Entity Framework in a Web API project. The sample code is shown below.

Exception

System.Data.Entity.Infrastructure.DbUpdateException
HResult=0x80131501 Message=An error occurred while updating the entries. See the inner exception for details. Source=EntityFramework StackTrace: at System.Data.Entity.Internal.InternalContext.SaveChanges() at System.Data.Entity.Internal.LazyInternalContext.SaveChanges() at System.Data.Entity.DbContext.SaveChanges() at WebApiSample.Controllers.OrdersController.Orders(Order neworder) in C:\Repository\bitbucket\OtherProjects\WebApiSample\WebApiSample\Controllers\OrdersController.cs:line 173 at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.b__9(Object instance, Object[] methodParameters) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments) at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)

Inner Exception 1: UpdateException: An error occurred while updating the entries. See the inner exception for details.

Inner Exception 2: SqlException: Cannot insert the value NULL into column 'OrderNumber', table 'C:\REPOSITORY\BITBUCKET\OTHERPROJECTS\WEBAPISAMPLE\WEBAPISAMPLE\APP_DATA\DATA.MDF.dbo.Orders'; column does not allow nulls. INSERT fails. The statement has been terminated.

This code is written in a POST method in Web API:

[HttpPost]
public IHttpActionResult Orders(Order neworder)
{
    if (neworder == null)
    {
        return BadRequest();
    }

    string uri = Url.Link("DefaultApi", new { id = neworder.OrderNumber });

    try
    {
        using (OrderContext db = new OrderContext("ConnectionString"))
        {
            if (neworder == null)
            {
                return BadRequest();
            }

            db.Orders.Add(neworder);
            db.SaveChanges();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }

    return Created<Order>(uri, neworder);
}

The Order entity:

namespace WebApiSample.Models
{
    public class Order
    {
        [Key]
        public int OrderNumber { get; set; }

        public Decimal OrderAmount { get; set; }
        public DateTime OrderDate { get; set; }

        public string CustomerName { get; set; }
        public int OrderQty { get; set; }

        [Column("ShippingStatusFlag")]
        public string ShippingStatus { get; set; }

        public virtual ICollection<OrderItems> Items { get; set; }
    }
}

The OrderItems entity:

namespace WebApiSample.Models
{
    [Table("OrderItems")]
    public class OrderItems
    {
        [Key]
        [ForeignKey("Order")]
        [Column(Order =1)]
        public int OrderNumber { get; set; }

        [Key]
        [Column(Order = 2)]
        public int ItemID { get; set; }

        [Key]
        [Column(Order = 3)]
        public int ItemSeq { get; set; }
        public Decimal ItemPrice { get; set; }
        public string ItemDescription { get; set; }
        public int ItemQty { get; set; }

        public virtual Order Order { get; set; }
    }
}

The data is passed in the POST request body is

{
        "OrderNumber": 9000,
        "OrderAmount": 20,
        "OrderDate": "2018-01-15T00:00:00",
        "CustomerName": "TEST",
        "OrderQty": 3,
        "ShippingStatus": "N",
        "Items": [
            {
                "OrderNumber": 9000,
                "ItemID": 535,
                "ItemSeq": 1,
                "ItemPrice": 10,
                "ItemDescription": "Plantronics Bluetooth Headset",
                "ItemQty": 1,
                "order": null
            },
             {
                "OrderNumber": 9000,
                "ItemID": 536,
                "ItemSeq": 2,
                "ItemPrice": 5,
                "ItemDescription": "Yellow StickyNote 100ct",
                "ItemQty": 1,
                "order": null
            },
             {
                "OrderNumber": 9000,
                "ItemID": 601,
                "ItemSeq": 3,
                "ItemPrice": 5,
                "ItemDescription": "Black Think Permanent Marker",
                "ItemQty": 1,
                "order": null
            }
        ]
    }

What could be causing this issue? What am I doing wrong here?

1
2
1/17/2018 3:13:25 PM

Accepted Answer

Modify your OrderNumber property with the DatabaseGeneratedAttribute

public class Order
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int OrderNumber { get; set; }

    ...
}

Background: when a single integer property is marked as Key, the default setting is DatabaseGeneratedOption.Identity. With this setting, EF assumes that the database will take care of assigning a fresh primary key value. So for an entity with Added state, the key column will be ignored and not be transferred to the database.

This explains why the error is about a NULL value for a property that's not even nullable in code.

4
1/17/2018 5:35:53 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow