How to model users and post addresses to find the city by zip code?

Question

I want to be able to load the city as related data on a user, based on the user's zip code. I understand that it not recommended to use the zip code itself as a key, as it might change in the future.

I have some models:

public class ApplicationUser : IdentityUser<Guid>
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string PostAddress { get; set; } // Street, c/o., apartment number, etc.
    public string Zip { get; set; }
    // More properties ...
}

public class City
{
    public Guid Id { get; set; }
    public string Zip { get; set; }
    public string Name { get; set; }
}

At the moment, I'm getting the city in a separate query, after obtaining the user's zip code.

In the end, I want to make queries like this:

var User = await db.Users.Where(u => u.Id == id)
    .Include(c => c.City)
    .FirstOrDefaultAsync();

... but I don't know how I should link the two tables.

Answer 1

Try to apply the [ForeignKey] attribute to the navigation property and [Key] attribute to Id property in the City class to make it a key property:

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public class ApplicationUser : IdentityUser<Guid>
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string PostAddress { get; set; } // Street, c/o., apartment number, etc.

    public int CityId { get; set; }
    [ForeignKey("CityId")]
    public City City { get; set; }
}

public class City
{
    [Key]
    public Guid Id { get; set; }
    public string Zip { get; set; }
    public string Name { get; set; }
}

For addition information see Data Annotations - ForeignKey Attribute in EF 6 & EF Core