Getting error in Entity Framework when trying to add an item

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?

You have just shown the stack trace. Import the complete exception message in the question — Marcus Höglund
– Marcus Höglund, Commented Jan 17, 2018 at 6:53
Maybe you should set the attribute DatabaseGenerated(DatabaseGeneratedOption.None) for the OrderNumber property in class Order. There might be a mismatch between your model (expecting auto-increment keys) and your database/usage, expecting explicit key settings... or could the OrderNumber entry refer to an existing database row? — grek40
– grek40, Commented Jan 17, 2018 at 15:20
@grek40: your solution of adding [DatabaseGenerated(DatabaseGeneratedOption.None)] to the OrderNumber propety in class Order resolved my issue. Thank you! — BipinR
– BipinR, Commented Jan 17, 2018 at 16:39

grek40 · Accepted Answer · 2018-01-17 17:35:53Z

6

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.

answered Jan 17, 2018 at 17:35

grek40

13.5k1 gold badge29 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting error in Entity Framework when trying to add an item

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related