Including the Foreign Key Id in Code First to save queries?

J. Doe

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?

Bassam Alugili

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.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

mvc 5 code first userid as a Foreign Key - How to save data

From Dev

Circular foreign key code first

From Dev

Entity Framework Code First Foreign Key issue

From Dev

Disable Foreign Key Constraint Code First EF

From Dev

Code first foreign key association MVC Entity

From Dev

EF Code First Foreign Key Same Table

From Dev

Entity Framework Code First Foreign Key issue

From Dev

EF Code First foreign key with multiple keys

From Dev

Foreign Key in Code First Entity Framework

From Dev

Foreign Key with Fluent API - Code First

From Dev

Entity Framework: Foreign Key in code first

From Dev

Code first approach not creating foreign key

From Dev

EF Code First Foreign Key Relationship

From Dev

Do I need to add a foreign key Id property on mvc models code first

From Dev

Create a Foreign Key Using "Code First" but actually have the DB First

From Dev

Save object with Foreign key

From Dev

EF 6 Code First, changing a Foreign Key Id with an Include on navigation property causes "A referential integrity constraint violation occurred" error

From Dev

Django foreign key count queries

From Dev

Defining multiple Foreign Key for the Same table in Entity Framework Code First

From Dev

Entity Framework Code First Foreign Key adding Index as well

From Dev

Entity Framework 6 Code First Foreign Key Without Corresponding Properties

From Java

EF Code First foreign key without navigation property

From Dev

EF Code First The INSERT statement conflicted with the FOREIGN KEY constraint

From Dev

DataAnnotation and Code First to Create Foreign Key to ApplicationUser using Identity 2.2.1

From Dev

Entity Framework Code First and Firebird - Foreign Key name issue

From Dev

Foreign Key issue with Code first from Existing Database

From Dev

EF Code First Single foreign key to multiple parents

From Dev

EF Code First Migrations creating extra foreign key

From Dev

EF Code First Fluent API specifying the Foreign Key property

Related Related

  1. 1

    mvc 5 code first userid as a Foreign Key - How to save data

  2. 2

    Circular foreign key code first

  3. 3

    Entity Framework Code First Foreign Key issue

  4. 4

    Disable Foreign Key Constraint Code First EF

  5. 5

    Code first foreign key association MVC Entity

  6. 6

    EF Code First Foreign Key Same Table

  7. 7

    Entity Framework Code First Foreign Key issue

  8. 8

    EF Code First foreign key with multiple keys

  9. 9

    Foreign Key in Code First Entity Framework

  10. 10

    Foreign Key with Fluent API - Code First

  11. 11

    Entity Framework: Foreign Key in code first

  12. 12

    Code first approach not creating foreign key

  13. 13

    EF Code First Foreign Key Relationship

  14. 14

    Do I need to add a foreign key Id property on mvc models code first

  15. 15

    Create a Foreign Key Using "Code First" but actually have the DB First

  16. 16

    Save object with Foreign key

  17. 17

    EF 6 Code First, changing a Foreign Key Id with an Include on navigation property causes "A referential integrity constraint violation occurred" error

  18. 18

    Django foreign key count queries

  19. 19

    Defining multiple Foreign Key for the Same table in Entity Framework Code First

  20. 20

    Entity Framework Code First Foreign Key adding Index as well

  21. 21

    Entity Framework 6 Code First Foreign Key Without Corresponding Properties

  22. 22

    EF Code First foreign key without navigation property

  23. 23

    EF Code First The INSERT statement conflicted with the FOREIGN KEY constraint

  24. 24

    DataAnnotation and Code First to Create Foreign Key to ApplicationUser using Identity 2.2.1

  25. 25

    Entity Framework Code First and Firebird - Foreign Key name issue

  26. 26

    Foreign Key issue with Code first from Existing Database

  27. 27

    EF Code First Single foreign key to multiple parents

  28. 28

    EF Code First Migrations creating extra foreign key

  29. 29

    EF Code First Fluent API specifying the Foreign Key property

HotTag

Archive