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

When I attempt to add a piece of information to the database context, I get the following error. In a Web API project, I'm working with Entity Framework. Here is an example of the code.

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 the Web API's POST method:

[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 information sent in the body of the POST request 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 may be the root of this problem? Why am I doing this wrong?

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

Accepted Answer

Adjust yourOrderNumber possession of theDatabaseGeneratedAttribute

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

    ...
}

Historically, when a single integer attribute isKey the standard setting isDatabaseGeneratedOption.Identity . By selecting this option, EF anticipates that the database will take care of creating a new primary key value. hence, for a body withAdded state, the key column won't be taken into account or stored in the database.

This explains why the mistake concerns aNULL value for a property that in code isn't even nullable.

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