Foreign Key in Entity Framework in C#

Question

I have been working with java and hibernate for many years, I am now starting working with C#, so I would like to do things more or less as I did in java.

I like annotations because I like to keep all configuration together with the entity.

I have two classes, city and country, city has a foreign key to country.

public class City {
    [Key]
    public long id { get; set; }
    public long countryId { get; set; }
    [ForeignKey("countryId")]
    public virtual Country country { get; set; }
    public string city { get; set; }
}

public class Country {
    [Key]
    public long id { get; set; }
    public string country { get; set; }
    public ICollection<City> Cities { get; set; }
}

I do not know if it is really needed to have to properties in city related to country (countryId and country) and if there is a way to have only the reference to the class

public Country country { get; set; }

Another question is that when I creates a new city

public class CityService:ICityService {
    public City getCity(string cityTxt, Country country) {
        City city = null;
        using (var ctx = new Context()) {
            city = ctx.Cities.Where(it => it.city == cityTxt && it.country.id == country.id).FirstOrDefault();
            if (city == null) {
                city = ctx.Cities.Add(new City { city = cityTxt, countryId = country.id });
                ctx.SaveChanges();
            }
        }
        return city;
    }
}

I set the countryId, after saving the new city, the city.country is null, is there a way to populate the city.country property? And if I set the countryId and country property

city = ctx.Cities.Add(new City { city = cityTxt, countryId = country.id , country = country });

After the ctx.SaveChanges() a repeated instance of the country is created on database.

I am still migrating my java concepts to C#, so any good tutorial reference would be very appreciated (better if working with annotations instaid of Fluent API).

Thanks in advance.

Answer 1

Having a foreign key property is not strictly needed. It's good to have one though (as noted by @GertArnold in the comments, this answer gives more information about this).

For the country property to be loaded, you need to actually load it. This can be done in several ways:

1. Have it eager loaded when you query for it, using Include:

   myContext.Cities.Include(x => x.country).Where(...);
   

2. If your context has proxies and lazy loading enabled (it does by default), you can just access the property for reading (while the context is not disposed) to load it:

   using(var myContext = new Context())
   {
     var city = myContext.Cities.FirstOrDefault(x => x.city == "foo");
     Console.WriteLine(city.country.country); 
     // if lazy loading is enabled, `city.country` will not be null after this
   }
   

   Notice that I'm accesing city.country within the using block. If you try to access it after the context has been disposed (outside the using block), it will not work (since it won't have a context and db connection from where to retrieve the data).

3. Load the reference afterwards:

   var city = myContext.Cities.FirstOrDefault(x => x.city == "foo");
   myContext.Entry(city).Reference(x => x.country).Load();
   

As for why a repeated instance is created in your example... if you set the navigation property (country) to an object which is not attached to the context where you are saving, it'll duplicate it into the database (it'll save it as if it was a new entity, and assign it a new key). You can either:

1. Attach your country to the context before saving (setting the foreign key is not needed then):

   ctx.Countries.Attach(country); // Attach the country to the context
   var city = new City { city = cityTxt, country = country };
   ctx.Cities.Add(city);
   

2. Just set the foreign key and let country be null (this is why it's useful to have the foreign key property):

   var city = new City { city = cityTxt, countryId = country.id /* don't set country */ };
   ctx.Cities.Add(city);
   

3. Load the country from the database (or cache) beforehand:

   country = ctx.Countries.Find(country.id);
   var city = new City { city = cityTxt, country = country };
   city = ctx.Cities.Add(city);
   

Graph management in Entity Framework 6 is definitely behind Hibernate's. It's supposed to have a finer control in Entity Framework Core, but despite the "Release Candidate" name, EF Core is just not ready for production yet (and honestly, taking they are changing everything back and forth on every RC release, I'd say not worth the investment on real apps for now till it settles... this is oppinionated though, your mileage may vary).

If you are used to Hibernate's way to do things, you may want to look at NHibernate which you'll find way more familiar.

PS: your casing is confusing, C# properties use TitleCase by convention. Of course you can use whatever, but you'll find most code hard to read if you don't get used to this (I definitely found your code hard to read, and it was hard for me to write the examples using your casing)