How map single entity with multiple table

问题:

I want to get data from two tables with single Entity class. How??

public class HomeViewModel  
{ 
    [Key] 
    [Column("candidate_ID")] 
    public int candidateID { get; set; } 
    [Column("first_name")] 
    public string firstName { get; set; } 
    [Column("last_name")] 
    public string lastName { get; set; } 

    public string emailID { get; set; } 
    public string mb_country_code { get; set; } 
    public int mobile_no { get; set; } 
}

Above entity class holds 6 property where 3 property represents one table1 and 3 represents table2. At database table 1 holds candidate_id as primary key and table two holds candidate_id as foreign key

Update: What i did is added DBContext class

public class EmployeeMonitoring : DbContext
{
    public DbSet<HomeViewModel> homeViewModel { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<HomeViewModel>().Map(m =>
            {
                m.Properties(a => new { a.candidateID, a.firstName, a.lastName,a.status });
                m.ToTable("table1");
            }).Map(m =>
            {
                m.Properties(c => new { c.candidateID,c.emailID, c.mobile_no, c.mb_country_code });
                m.ToTable("table2");
        });
     }
}`

and at Controller Action i used following Linq to Entity Query

var data = db.homeViewModel.ToList();

But it returns nothing, i.e 0 count.

回答1:

If you are suggesting that Table2 merely holds a foreign key to table1, but has a different primary key, then you can't really do what you're asking. Simply holding a foriegn key means that's a one to many relationship, and there's no way to map a single entity across a one to many relationship like that (even if your data only contains one record, the model relationship type is still one to many)

If you mean that Table2 has a primary and foreign key of candidate_id (and thus it's a 1 to 1 mapping) then you can map them into a single entity fairly easily using Inheritence described here:

http://weblogs.asp.net/manavi/archive/2010/12/28/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-2-table-per-type-tpt.aspx

If all you want is to create a single object that contains data from two tables, then that's a relatively simple linq query, which i won't get into because I really don't know exactly what you're looking for here.

回答2:

Check data in daatbase table first.
It is because you might not have related data in table2. i.e table1 primary key value (which is candidate_id) is not present in table2 foreign key candidateID...

回答3:

This is called entity splitting. To make it work your CandidateID must be primary key in both tables and foreign key in the second table (entity splitting works only with one-to-one relations in a database).

Edit: There is another limitation. Entity splitting doesn't allow optional relations. Records in both tables must exists to make this work so if your table2 records are optional you must map both tables separately and construct your view model in the application from loaded records.