When creating one to many relationships one might bump into the following problem:
Consider the following database design:
With a many to one relation from Customers.City to Cities.Id
While this is easy to do in SQL, the LINQ to Entities syntax might seem a bit different.
Consider the following piece of code:
City c = (from city in ctx.CitySet where city.Name == cityName && city.Zip == cityZip select city).FirstOrDefault();
This selects the city (if it exists) or null.
Now the problem is: IF the city exists, how do I point the current customer’s city to that particular city.
I tried this:
//customer is a customer object customer.City = c;
But that resulted in duplicate cities in the database.
The solution was actually quite easy, I browsed through all the properties (Intellisense!) and found this:
customer.CityReference.EntityKey = c.EntityKey;
This resulted in the one to many relationship I had in mind. No duplicate cities in my database.
I love database normalization!