Mapping 2 tables to single entity in Entity Framework

Question

I hope you can help me.

I have 2 tables in the db: Bill and BillItem. These tables are configured with one to one relation in the db where bill is the principle table while BillItem is the dependent one

The structure of table Bill :

Int BillId (PK)
Int BillTypeId Not Null
Varchar(5) Usr Not Null
DateTime Tm Not Null

The structure of table BillItem:

Int BillItemId (PK)
Int ItemId Not Null 
Varchar(5) Usr Not Null
DateTime Tm Not Null

I would like to map these 2 table into a single POCO class using Fluent API and Entity Framework 4.1 Code First approach

I also want to configure the tables columns names to use different properties names in the POCO Class (i.e. Id instead of BillId, User instead of Usr)

This is a legacy database, I cannot modify any of its objects.

How can achieve that?

Thank you all.

The resulting class should be (if can be):

public int Id {get;set;}
public int BillTypeId {get;set;}
public int ItemId {get;set;}
public string User {get;set;}
public string User1 {get;set;}
public DateTime Tm {get;set;}
public DateTime Tm1 {get;set;}

Answer 1

For renaming columns, that's easy:

[Table("SomeHorribleTableName")]
    public class MyNiceTableName
    {
        [Column("Usr")]
        public string User { get; set; }
    }

Here I've renamed the entity as well.. no need to keep a horrible table name. About the mapping 2 tables to 1 entity.. I don't think that's possible. see here: Mapping data from 2 tables to 1 entity - Entity Framework 4

Fluid style:

public class Bill
    {
        public int ID { get; set; }
        public int BillTypeID { get; set; }
        public string UserName { get; set; }
        public DateTime Time { get; set; }
    }

    public class BillItem
    {
        public int ID { get; set; }
        public int ItemID { get; set; }
        public string UserName { get; set; }
        public DateTime Time { get; set; }
    }

    internal class BillMap : EntityTypeConfiguration<Bill>
    {
        public BillMap()
        {
            ToTable("Bills");
            HasKey(x => x.ID);
            Property(x => x.ID).HasColumnName("BillId");
            Property(x => x.BillTypeID).IsRequired().HasColumnName("BillTypeId");
            Property(p => p.UserName).IsRequired().HasColumnName("Usr");
            Property(x => x.Time).IsRequired().HasColumnName("Tm");
        }
    }

    internal class BillItemMap : EntityTypeConfiguration<BillItem>
    {
        public BillItemMap()
        {
            ToTable("BillItems");
            HasKey(x => x.ID);
            Property(x => x.ID).HasColumnName("BillItemId");
            Property(x => x.ItemID).IsRequired().HasColumnName("ItemId");
            Property(p => p.UserName).IsRequired().HasColumnName("Usr");
            Property(x => x.Time).IsRequired().HasColumnName("Tm");
        }
    }

That's probably the best I can do. As for handling the Usr column in both tables, that'll be something you should probably deal with in your own controller classes / business logic layer.

One more thing: For the configuration classes above.. call them in your DbContext like so:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Configurations.Add(new BillMap());
    modelBuilder.Configurations.Add(new BillItemMap());
}

Answer 2

I think you can achieve that with the Entity Splitting feature but you will need to use some Data Annotations (no esacpe):
WARNING: I HAVEN'T TRIED THIS METHOD BUT IT MIGHT WORK, CHECK IT OUT IF YOU LIKE First: Annotate the properties that belong to the BillItem table in you single domain model class with the HasColumnName data annotation.
Second: Use this code sample

public class YourSingleModelClassNameConfiguration : EntityTypeConfiguration<YourSingleModelClassName> {
    public YourSingleModelClassNameConfiguration() {

        ToTable("Bill"); Property(param => param.Id).IsRequired().HasColumnName("BillId");

        // NOW REPEAT THAT FOR ALL BILL PROPERTIES WITH YOUR REQUIRED ATTRIBUTES (ISREQUIRED OR NOT, LENGTH ...)

        // NOW THE PROPERTIES YOU NEED TO MAP TO THE BILL ITEMS TABLE GOES INTO THE MAP FUNCTION
        Map(
        param =>
            { param.Properties(d => new {d.ItemId, d.User1}); m.ToTable("BillItem");
            }
        );

        // DON'T FORGET TO MENTION THE REST OF THE PROPERTIES BELONGING TO THE BillItem TABLE INSIDE THE PROPERTIES METHOD IN THE LAST LINE.

    }
}

Answer 3

for this question you can do it very easily and you got 2 options. The first is to map the classes using entity framework (mapping multiple tables to a single entity class in entity framework).

the second option is to do it manually: The restriction the 2 tables must follow is that 1 has to be the strong table and the second needs to be weak one (by doing this, you should have 1 by one only from the strong table to the weak) otherwise, this wont work as the result will be a collection of objects and then you will need to decide whether if the first object is the one that you need and no other.

if you want to use a slightly faster approach i reccomend using your own logic to map the POCO objects. create 2 clases that will map with the database table and from a service object build the object.

this approach is good because the resulting LINQ query wont use INNER JOINS which makes it faster. but the join should be kept from your side.

Example

int i=0;
// this 2 refer to the 2 different mapped tables in your context database
var pbdata = _pbdata.GetSingle(p=>p.StrongTableID == StrongTableID);
var pb = _pb.GetSingle(p=>p.WeakTableID == pbdata.WeakTableID);
// you can see that i am looking for the StrongTableID in order to select the entity value

// this is optional but usefull! you can do the
// "copy" inside a function where in future if the 
// object changes, you can update easily
ObjectComposite.Map(body, ref pb, ref pbdata);

// the following proccess needs to be done like this...
// first save the value in the weak object so 
// the WeakTableID is recorded
// UPDATE: maybe these 2 should go into a transact for more security... but... ok...
Save(pb);
i += this.unitOfWork.Save();
pbdata.ProfessionalBodyID = pb.ProfessionalBodyID;

// once the values for the second objects are set, save it all again.
Save(pbdata);
i += this.unitOfWork.Save();
return i > 0 ? true : false;

This approach is faster but you need to control everything from yourself. the good thing is that here you can map as many tables as you want

hope it helps!