I'm using Entity Framework 6 Code First.
Right now my model looks the following:
public class Region
{
public int Id { get; set; }
public string Name { get; set; }
public virtual List<City> Cities { get; set; }
}
public class City
{
public int Id { get; set; }
public string Name { get; set; }
[Required]
public virtual Region Region { get; set; }
}
The thing is, I got a list of cities. I need to lookup the region of each city (I have a local list of regions as well).
Now, I suppose I could do the following:
foreach (var c in cities)
{
if (regions.Any(x => x.Id == c.Region.Id))
}
Here I will have to look up a region from the database for each city (lazy loading). However, I only need the Id of the region, therefore it seems wasteful to me to look up the region row at every loop.
If I changed my City model to be the following:
public class City
{
public int Id { get; set; }
public string Name { get; set; }
[ForeignKey("Region")]
public int RegionId { get; set; }
[Required]
public virtual Region Region { get; set; }
}
I could do the following instead:
foreach (var c in cities)
{
if (regions.Any(x => x.Id == c.RegionId)) //no region lookup at Im using the foreign id key
}
Is this correct? I mean it will save me a query for every city right?
If so, is there any reason NOT to include the foreign id keys in the model when doing Code First at all?
Is this correct? I mean it will save me a query for every city right?
It will save a join to the Regions table. You have to check it out with the SQL Profiler! For example:
List of cities with Id City1, City2 (Region not loaded City1= has RegionId 5) Now you are looking for any City has a Region with Id 5.
// EF does not have to join the tables because you have the RegionId
if (myDbContext.Cities.Any(c => c.RegionId == 5))
{
}
If so, is there any reason NOT to include the foreign id keys in the model when doing Code First at all?
No for me this it is a good practice! Just keep it consistent and do it for all relationships of the type 1..n/1..0or1.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments