Entity Framework外键理解与多表查询组合对象技术问询

Hey there! Let's get your EF foreign key and query problem sorted out step by step so you can fetch that combined Parking data you need. First, we'll tweak your entity classes to add proper navigation properties (right now you only have foreign key fields, which EF uses to link tables, but navigation properties make queries much cleaner).

Step 1: Update Entity Classes with Navigation Properties #

Add these navigation properties to define the relationships explicitly:

ParkDB #

[Table("Parking")]
public class ParkDB {
    [Key] [Column("ParkID")]
    public int Id { get; set; }
    [Required]
    public string ParkStartDate { get; set; }
    [Required]
    public string ParkEndDate { get; set; }
    
    [Required] [ForeignKey("City")]
    public int ParkCityID { get; set; }
    // Navigation property for related City
    public virtual CitiesDB City { get; set; }
    
    [Required] [ForeignKey("Street")]
    public int ParkStreetID { get; set; }
    // Navigation property for related Street
    public virtual StreetsDB Street { get; set; }
}

StreetsDB #

[Table("Streets")]
public class StreetsDB {
    [Key]
    public int StreetID { get; set; }
    [Required] [ForeignKey("City")]
    public int CityID { get; set; }
    // Navigation property for related City
    public virtual CitiesDB City { get; set; }
    
    [Required]
    public string StreetDesc { get; set; }
}

Your CitiesDB class is good to go as-is since it's the parent table in these relationships.

Step 2: Query to Fetch Combined Data #

Now you can use LINQ to query and project only the specific fields you need. Here are three common, easy-to-use approaches:

Option 1: Explicit Joins with LINQ Query Syntax (Readable) #

using (var context = new YourDbContext()) {
    var parkingData = from park in context.ParkDB
                      join city in context.CitiesDB on park.ParkCityID equals city.CityID
                      join street in context.StreetsDB on park.ParkStreetID equals street.StreetID
                      select new {
                          ParkID = park.Id,
                          park.ParkStartDate,
                          park.ParkEndDate,
                          city.CityDesc,
                          street.StreetDesc
                      };
    
    // Materialize results to a list (or use directly in your app)
    var resultList = parkingData.ToList();
}

Option 2: Fluent Method Syntax #

using (var context = new YourDbContext()) {
    var parkingData = context.ParkDB
        .Join(context.CitiesDB, 
              park => park.ParkCityID, 
              city => city.CityID, 
              (park, city) => new { park, city })
        .Join(context.StreetsDB, 
              combined => combined.park.ParkStreetID, 
              street => street.StreetID, 
              (combined, street) => new {
                  ParkID = combined.park.Id,
                  combined.park.ParkStartDate,
                  combined.park.ParkEndDate,
                  combined.city.CityDesc,
                  street.StreetDesc
              });
    
    var resultList = parkingData.ToList();
}

Option 3: Using Navigation Properties (Cleanest) #

With the navigation properties we added, EF handles the joins automatically—no need to write explicit join logic:

using (var context = new YourDbContext()) {
    var parkingData = context.ParkDB
        .Include(p => p.City) // Load related City data upfront
        .Include(p => p.Street) // Load related Street data upfront
        .Select(p => new {
            ParkID = p.Id,
            p.ParkStartDate,
            p.ParkEndDate,
            p.City.CityDesc,
            p.Street.StreetDesc
        })
        .ToList();
}

Key Tips to Keep in Mind: #

• Projection: We're using anonymous types here to grab only the fields you need. If you want to reuse this data structure across your app, create a custom DTO (like ParkingDetailsDto) and project into that instead of an anonymous type.
• Avoid N+1 Queries: Always use Include() when accessing navigation properties in a query—this loads all related data in a single query instead of making separate calls for each record's city/street.
• DbContext Setup: Replace YourDbContext with the actual name of your EF DbContext class, which should include DbSet<ParkDB> ParkDB, DbSet<CitiesDB> CitiesDB, and DbSet<StreetsDB> StreetsDB.

内容的提问来源于stack exchange，提问作者Aa Yy