Why should I manually enter the values of the foreign key properties (in this situation)?
When I have a model like this ...
public class Order
{
[Key, Column(Order = 1)]
public int CustomerId { get; set; }
[Key, Column(Order = 2)]
public int OrderId { get; set; }
public ICollection<OrderItem> Items { get; set; }
}
public class OrderItem
{
[Key, Column(Order = 1)]
public int CustomerId { get; set; }
[Key, Column(Order = 2)]
public int OrderId { get; set; }
[Key, Column(Order = 3)]
public int OrderItemId { get; set; }
}
... I don't have to manually enter foreign key values OrderItem.CustomerId
and OrderItem.OrderId
manually when I add to the database Order - OrderItems
:
var order = new Order
{
CustomerId = 5,
OrderId = 1,
Items = new List<OrderItem>
{
// I don't need to set CustomerId and OrderId here
new OrderItem { OrderItemId = 12 }
}
};
context.Orders.Add(order);
context.SaveChanges();
SaveChanges
will generate SQL statements for insert Order
and OrderItem
. For OrderItem
this:
exec sp_executesql N'insert
[dbo].[OrderItems]([CustomerId], [OrderId], [OrderItemId])
values (@0, @1, @2)
',N'@0 int,@1 int,@2 int',@0=5,@1=1,@2=12
So, FKs CustomerId
and OrderId
properly installed ( @0=5
and @1=1
).
However, if I add another link to the object Customer
to the model by simply adding a navigation property to Order
...
public Customer Customer { get; set; }
... with a class Customer
like this ...
public class Customer
{
[DatabaseGenerated(DatabaseGeneratedOption.None)]
public int CustomerId { get; set; }
}
... and call the same code as above (assuming a Customer
c CustomerId
= 5
exists in the DB) I get the following SQL:
exec sp_executesql N'insert
[dbo].[OrderItems]([CustomerId], [OrderId], [OrderItemId])
values (@0, @1, @2)
',N'@0 int,@1 int,@2 int',@0=0,@1=1,@2=12
Almost the same, but CustomerId
0
in this case ( @0=0
), which results in a violation of the foreign key constraint on the relationship Order - OrderItem
. If Customer
c CustomerId
= existed 0
and this client had Order
c OrderId
= 1
, the exception would not have been thrown and OrderItem
added in the wrong order, which might be worse than the exception.
The problem can be fixed by installing CustomerId
(which is part of FK) in a new one OrderItem
:
Items = new List<OrderItem>
{
// I don't need to set OrderId here, BUT CustomerId
new OrderItem { CustomerId = 5, OrderItemId = 12 }
}
Or - it can be fixed by associating the client with an Id 5
with a context (I don't know why it works then).
Is there a reasonable explanation as to why I have to provide the FK (or part of the FK in this example) manually, or is this a bug?
(I used EF 5.0 with .NET 4.0 for this model.)
source to share
Your model looks strange to me, and I suspect this is causing your confusion.
Based on what I see, I am assuming that you have clients that can have many orders, and each order should only have one of each OrderItem type, but can have many OrderItems. One order cannot belong to several Clients. One OrderItem can belong to several orders.
If this is true, I think you need a model, for example:
public class Customer
{
//Is the key by naming convention or you could use [Key]
public Guid CustomerId { get; set; }
public ICollection<Order> Orders { get; set; }
}
public class Order
{
//Is the key by naming convention or you could use [Key]
public Guid OrderId { get; set; }
public Guid CustomerId { get; set; } //This doesn't need to be part of the key
public Customer Customer { get; set; }
public ICollection<OrderItem> OrderItems { get; set; }
}
public class OrderItem
{
[Key, Column(Order = 0)]
public Guid OrderItemId { get; set; }
[Key, Column(Order=1)]
public Guid OrderId { get; set; }
public ICollection<Order> Orders { get; set; }
}
Then when you need to inquire if you need to know the OrderItems from the Client, you can do it with Include or with a data projection like
OrderItems.Select(x=>new {
CustomerId = x.Order.Customer.Id,
//this rest of your properties
});
or if you want the whole object
OrderItems.Include(x=>x.Order.Customer)
source to share