CODEWITHSUNDEEP
c#.netlinq-to-sqlforeign-keysunique-constraint
Kris Harper
Kris Harper

Reputation: 5862

How do I insert in LINQ to SQL when I have a foreign key and a unique constraint?

Suppose I have a simple database schema with a customers and orders table with a foreign key from orders to customers, and a unique key on customer name.

Customers
-----------
[CustomerID] INT NOT NULL
[Name] VARCHAR(50) NOT NULL    

Orders
-----------
[OrderID] INT NOT NULL
[CustomerID] INT NOT NULL

+------------+------+    +---------+------------+
| CustomerID | Name |    | OrderID | CustomerID |
|------------+------+    +---------+------------+
|          1 | John |    |       1 |          1 |
+------------+------+    +---------+------------+

I've set up LINQ to SQL entities in the obvious way, with a Customer entity and an Order entity with a one-to-many association from Customer to Order.

The issue I'm having comes when I want to enter a new order for John using my LINQ entities. I thought the way to do this would be to get the Customer entity for John and assign it to my Order entity, then submit that, but it's not working. Here's my C# code to do this.

using (var db = new DataClassesDataContext())
{
    Order order = new Order();
    Customer existingCustomer = db.Customers.FirstOrDefault(c => c.Name == "John");

    if (existingCustomer == default(Customer))
    {
         Customer customer = new Customer();
         customer.Name = "John";
         order.Customer = customer;
    }
    else
    {
        order.Customer = existingCustomer;
    }

    db.Orders.InsertOnSubmit(order);
    db.SubmitChanges();
}

I have verified that I am indeed assigning existingCustomer to my Order before submitting it, but despite this I still get a unique key constraint error when I attempt to submit.

I'm very new to LINQ to SQL, so I think I'm just going about this incorrectly. How should I implement my code to account for a unique constraint?

Upvotes: 4

Views: 9786

Answers (2)

Pleun
Pleun

Reputation: 8920

I think your code should be fine. Somehow it tries to insert a new customer but that should not happen.

Can you try it with

 Customer existingCustomer = db.Customers.Single(c => c.Name == "John");

and see if you get an exception? In that case there might be something wrong with the DBML for the customer table.

Not helping you with the question but vsince you have a unique constraint on the name Single / SingleOrDefault is better than First/FirstOrDefault.

Upvotes: 0

Gert Arnold
Gert Arnold

Reputation: 109185

The line

db.Orders.InsertOnSubmit(order);

marks the whole object graph for insert. So if you move the line to just below Order order = new Order(); only the order is marked for insert. That means that for a new customer you must call InsertOnSubmit too.

Upvotes: 4

Related Questions