EF Code First: Add a Foreign Key relationship Linq-to-Entity projection

Question

I'm have an issue in regards to creating a database Foreign Key from code first using entity framework. The tables shown below are queried and the data is projected into another model, shown below. Everything appears to be correct although when I query the data the joined columns by AddressId don't appear to be working and my "Street" column returns blank data.

namespace Mvc4Connect.ViewModels
{

[Table("PersonEntity")]
public class PersonEntity : EntityTypeConfiguration<PersonEntity>
    {
    //Constructor
    public PersonEntity()
    {
        this.ToTable("TB008555", schemaName: "SchemaUser");
        this.Property(c => c.PersonId).HasColumnName("CL005500");
        this.Property(c => c.FirstName).HasColumnName("CL005545");
        this.Property(c => c.LastName).HasColumnName("CL005550");
        this.Property(c => c.AddressId).HasColumnName("CL044760");
    }


    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int PersonId { get; set; }
    public String FirstName { get; set; } //firstname
    public String LastName { get; set; } //lastname

    [ForeignKey("AddressEntity")]
    public int AddressId { get; set; }

    public virtual ICollection<AddressEntity> AddressEntity { get; set; } 
    }

}

My second table containing the joined AddressId

namespace Mvc4Connect.ViewModels
{

[Table("AddressEntity")]
public class AddressEntity : EntityTypeConfiguration<AddressEntity>
{
    //Constructor
    public AddressEntity()
    {

        this.ToTable("TB047697", schemaName: "SchemaUser");
        this.Property(c => c.AddressId).HasColumnName("CL045695");
        this.Property(c => c.Street).HasColumnName("CL042710");
        this.Property(c => c.City).HasColumnName("CL044747");
        this.Property(c => c.State).HasColumnName("CL047460");
    }

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int AddressId { get; set; }
    public string Street { get; set; }        
    public string City { get; set; }
    public string State { get; set; }

}

}

My Entity Context

public class EntityContext : DbContext
{
public DbSet<PersonEntity> Persons { get; set; }
public DbSet<AddressEntity> Address { get; set; }

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
        modelBuilder.Configurations.Add(new PersonEntity());
        modelBuilder.Configurations.Add(new AddressEntity());
}
}

This is the query I'm trying to use in my API controller.

var dbv = new EntityContext(); 

var query = (from vt in dbv.Persons
                   from ad in dbv.Address
                   where vt.LastName.StartsWith(ln)
                   where vt.FirstName.StartsWith(fn) 
                   where ad.AddressId == vt.AddressId
                   select new PersonVM() // Create new object for projection
                   {
                       PersonId = vt.PersonId,
                       FirstName = vt.FirstName,
                       LastName = vt.LastName,
                       Street = ad.Street, // Blank Street data
                       AddressId = vt.AddressId // Debug to assure Id is assigned
                   });

        return query.ToList();

Answer 1

I think that you made mistake when determine relationship between tables: combination of:

public class PersonEntity : EntityTypeConfiguration<PersonEntity>
{
    [ForeignKey("AddressEntity")]
    public int AddressId { get; set; }    
    public virtual ICollection<AddressEntity> AddressEntity { get; set; } 
    //other properties...
}

is not correct! Instead of, if relation between Person and Address is one-to-many you should write that way:

public class PersonEntity : EntityTypeConfiguration<PersonEntity>
{
    //[ForeignKey("AddressEntity")]
    //public int AddressId { get; set; }    
    public virtual ICollection<AddressEntity> AddressEntity { get; set; } 
    //other properties...
}

And

public class AddressEntity : EntityTypeConfiguration<AddressEntity>
{
     [ForeignKey("Person")]
     public int PersonId { get; set; }
     public virtual PersonEntity Person { get; set; }
     //other properties...
}

Final query will be look like this:

var query = (from vt in dbv.Persons
             from ad in dbv.Address
             where vt.LastName.StartsWith(ln)
             where vt.FirstName.StartsWith(fn) 
             where vt.PersonId == ad.PersonId//this line was changed!
             select new PersonVM
             {
                 vt.PersonId,
                 vt.FirstName,
                 vt.LastName,
                 ad.Street,
                 ad.AddressId //this line was changed!
             });

This query will return not Persons, but addresses with info about it Person. If you want to take Persons with their addresses you should perform group by operation and also left outer join to save persons without addresses:

var query = (from vt in dbv.Persons                     
             where vt.LastName.StartsWith(ln)
             where vt.FirstName.StartsWith(fn)
             join ad in dbv.Address on vt.PersonId equals ad.PersonId into subAddresses
             from subAdr in subAddresses.DefaultIfEmpty()
             group subAdr by new { vt.PersonId, vt.FirstName, vt.LastName } into groupPers                                          
             select new
             {
                  groupPers.Key.PersonId,
                  groupPers.Key.FirstName,
                  groupPers.Key.LastName,
                  addresses = groupPers
              });