Why does Entity framework add foreign keys at the foreign entity, and what does it mean?-CodePudding

Hi I have a question about how entity framwework manages foreign keys.

If I have the following model, called the customerModel which can have a relationship to 0 or more purchases:

public class CustomerModel
{
        public int Id{get;set; }
        [Required]
        public string Name {get;set; }
        [Required]
        public string Surname { get; set; }
        [Required]
        [Range(18,110)]
        public uint Age { get; set; }

        [Required]
        public virtual List<PurchaseModel> purchases { get; set; }
}

And then I have a model for the puchase, which do not contain any navigational properties for the customer.

But when I now create a database migration, and in the database, the purchase gets a field called CustomerId which references the customer.

Thus the sql database and the entity framework abstraction have "opposite" approaches to representing relationships.

My question is whether this does'nt introduce a slight performance hit?

say I was to a make a query like this:

CustomersQuery.Where( x => x.purchases.Any(p => ...))

In my mind the query here would introduce a lookup in the database on the purchases table, and then correlate it with the customer I'm currently working with.

It seems a bit like this is overcomplicating things to me, rather than just having the Custoemr store a list of purchase Id's, and then make the query "directly".

Can someone elaborate on why the entity framework relations are translated like this, and if this is important to keep in mind when constructing queries like the one shown above?

Or should I just trust that entity framwork is handling it in the most effecient way possible?

CodePudding user response：

It is the standard pattern to store a 1:n relation as a foreign key on the n-end (so a customer id in the purchase in your case)

Normally you should also have an index on that, so the query that's happening (select count(*) from purchase where customerId = 123) and the one you describe (select count(*) from purchase where Id in (..., ..., ...)) should not be dramatically different in performance.

Additionally: "store a list of purchase Id's" for this to be efficient, you must impose a maximum number of purchases per customer, otherwise you hve an arbitrary big blob for each customer with all its purchases.

All in all, trust EF here, the approach is industry standard. And add the reverse navigation property if you like.