2

I am building a Windows Phone Application using Mango with SQLCE 4.0 (I think).

I get this error when I am trying to submit a new OrderItem, with context.SubmitChanges() command.

A duplicate value cannot be inserted into a unique index. [Table name = Order,Constraint name = PK_Order]

Here is some code:

[Table]
public partial class Order : BCSDataContextBase //Base only icludes NotifyProperty....
{
    private int _id;
    private int _orderId;
    private EntitySet<OrderItem> _items;
    private DateTime _dateCreated = DateTime.Now;
    private DateTime _dateModified = DateTime.Now;

    [Column(IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
    public int Id
    {
        get { return _id; }
        set {
            if (_id != value) {
                NotifyPropertyChanging("Id");
                _id = value;
                NotifyPropertyChanged("Id");
            }
        }
    }

    [Column(IsPrimaryKey = true, DbType = "INT NOT NULL", CanBeNull = false)]
    public int OrderId
    {
        get { return _orderId; }
        set 
        {
            if (_orderId != value) {
                NotifyPropertyChanging("OrderId");
                _orderId = value;
                NotifyPropertyChanged("OrderId");
            }
        }
    }

    [Association(Storage = "_items", ThisKey="OrderId", OtherKey = "OrderId")]
    public EntitySet<OrderItem> Items
    {
        get { return this._items; }
        set { this._items.Assign(value); }
    }                

    // Version column aids update performance.
    [Column(IsVersion = true)]
    private Binary _version;

    public Order()
    {
        this._items = new EntitySet<OrderItem>(
            new Action<OrderItem>(this.attach_Item),
            new Action<OrderItem>(this.detach_Item)
            );
    }

    private void attach_Item(OrderItem entity)
    {
        NotifyPropertyChanging("OrderItem");
        entity.Order = this;
    }

    private void detach_Item(OrderItem entity)
    {
        NotifyPropertyChanging("OrderItem");
        entity.Order = null;
    }
}

[Table]
public partial class OrderItem : BCSDataContextBase
{
    private int _id;
    private int _orderId;
    private string _productId;
    private int _quantity;        
    private EntityRef<Order> _order;

    public OrderItem() 
    {
        this._order = default(EntityRef<Order>);
    }

    [Column(IsDbGenerated = true, DbType = "INT NOT NULL Identity", CanBeNull = false, AutoSync = AutoSync.OnInsert)]
    public int Id
    {
        get { return _id; }
        set
        {
            if (_id != value) {
                NotifyPropertyChanging("Id");
                _id = value;
                NotifyPropertyChanged("Id");
            }
        }
    }

    [Column(Storage = "_orderId", DbType = "Int NOT NULL", AutoSync = AutoSync.OnInsert)]        
    public int OrderId
    {
        get { return this._orderId; }
        set
        {
            if (this._orderId != value)
            {
                NotifyPropertyChanging("OrderId");
                this._orderId = value;
                NotifyPropertyChanged("OrderId");
            }
        }
    }

    [Column(Storage = "_productId", IsPrimaryKey = true)]
    public string ProductId
    {
        get { return _productId; }
        set
        {
            if (_productId != value)
            {
                NotifyPropertyChanging("ProductId");
                _productId = value;
                NotifyPropertyChanged("ProductId");
            }
        }
    }

    [Column(Storage = "_quantity", DbType = "Int NOT NULL")]
    public int Quantity
    {
        get { return this._quantity; }
        set
        {
            if (this._quantity != value)
            {
                NotifyPropertyChanging("Quantity");
                this._quantity = value;
                NotifyPropertyChanged("Quantity");
            }
        }
    }                       

    // Entity reference, to identity the Order "storage" table
    [Association(Name = "Order_Order_Item", Storage = "_order", ThisKey = "OrderId", OtherKey = "OrderId", IsForeignKey = true)]
    public Order Order
    {
        get { return this._order.Entity; }
        set
        {
            NotifyPropertyChanging("Order");                
            this._order.Entity = value;

            if (value != null)
            {
                value.Items.Add(this);
                this._orderId = value.OrderId;
            }
            else { this._orderId = default(int); }

            NotifyPropertyChanging("Order");
        }
    }
}    

I cant see what I am doing wrong.

Here's the L2SQL code:

public bool SaveOrderItemByOrder(OrderItem newItem)
    {
        bool successfullySaved = false;
        using (var context = DataObjectFactory.CreateContext())            
        {
            var item = db.OrderItems.Where(i => i.ProductId == newItem.ProductId).FirstOrDefault();

            try
            {
                if (item != null)
                {
                    item.Quantity = newItem.Quantity;                        
                    db.SubmitChanges();
                }
                else
                {                        
                    db.OrderItems.InsertOnSubmit(newItem);
                    db.SubmitChanges();                    
                }

                successfullySaved = true;
            }
            catch (Exception ex)
            {
                throw ex;
                successfullySaved = false;
            }

        }

        return successfullySaved;
    }

I Hope someone can help me out, i have already put to many hours on this problem!

EDIT:

I tried this code:

context.OrderItems.InsertOnSubmit(new OrderItem { Order = (new Order { OrderId = 1234567 }), ProductId = "sdfsdf3dsf", Quantity = Quantity });
        context.SubmitChanges();

And i got same error, but when I changed the OrderId to whatever it works. The problem is that if I submitting an orderitem on that order, it will be that orderId, so I guess there are some problem in the association ?

n3tx
  • 429
  • 2
  • 10
  • 23
  • You are setting the PK column to a value that is already in the DB. If Identity is set to true on that column, don't set the pk key at all. – Magnus Dec 04 '11 at 16:19
  • The thing is, there are no value in OrderItem table at all when i am trying to insert the item. The database is deleted and created everytime the application starts. I dont set the Id field at all, only orderId and productId in the OrderItem table. – n3tx Dec 04 '11 at 16:39
  • 2
    @n3tx - Maybe you are accidentally inserting the same item twice in succession. There IS a value in the table or you wouldn't get the exception. – Steve Wellens Dec 04 '11 at 17:33
  • Yea.. that is the problem .. Some where the value gets set, but I only use the SubmitChanges() command in the SaveOrderItemByOrder method. But both the Order and OrderItem are entities in the database, can it be that when I make a "= new OrderItem()" it gets set directly in the database, but not submitted ? In the AddItemViewModel i got this: bool IsAdded = _orderDataSource.SaveOrderItemByOrder(new OrderItem { Order = CurrentOrder, ProductId = BarCodeText, Quantity = Quantity }); do you think that can be the problem ? – n3tx Dec 04 '11 at 18:09
  • No the values are only added to the DB when `SubmitChanges()` is called. – Magnus Dec 04 '11 at 18:18
  • but how come I dont get any value when i am making the check, "var item = db.OrderItems.Where(i => i.ProductId == newItem.ProductId).FirstOrDefault();" And then when i am submitting, the value already exists. – n3tx Dec 04 '11 at 18:26
  • Why is it trying to insert a value to Order table ? I only want it to add a value to OrderItem. – n3tx Dec 04 '11 at 20:57
  • 1
    From the OrderId column in your OrderItem class try to remove the 'AutoSync = AutoSync.OnInsert' maybe that will solve your problem. – BigL Jan 14 '12 at 19:51

0 Answers0