How to map column and entity propery of different

Question

I am using Entity Framework 5 - Code first.

I have a database that I am connecting to that already exists for some time now (I did not create it). There is a table called T_Customers. It contains a list of all the customers. It has the following structure (only partially shown):

Customer_id | numeric (5, 0) | auto increment | not null (not set as primary key)
FName | varchar(50) | not null
LName | varchar(50) | not null

My Customer class:

public class Customer : IEntity
{
     public int Id { get; set; }

     public string FirstName { get; set; }

     public string LastName { get; set; }
}

My IEntity interface:

public interface IEntity
{
     int Id { get; set; }
}

I have the following in my database context class:

public class DatabaseContext : DbContext
{
     public DatabaseContext(string connectionString)
          : base(connectionString)
     {
     }

     public DbSet<Customer> Customers { get; set; }

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

     public new DbSet<TEntity> Set<TEntity>()
          where TEntity : class, IEntity
     {
          return base.Set<TEntity>();
     }
}

My customer configuration class:

class CustomerConfiguration : EntityTypeConfiguration<Customer>
{
     internal CustomerConfiguration()
     {
          this.ToTable("T_Customers");
          this.Property(x => x.Id).HasColumnName("Customer_id");
          this.Property(x => x.FirstName).HasColumnName("FName");
          this.Property(x => x.LastName).HasColumnName("LName");
     }
}

I'm trying to be consistent in my entity declarations, I need all the IDs to be integers. This customer ID is of type numeric in the database, and now I am running into issues when trying to return a list of all the customers. How do I map from a database numeric type to a C# integer type? I'm not changing my class's ID to nullable or decimal, my IDs are always non nullable and integer. I also cannot change the database.

The error that I am getting is:

The 'Id' property on 'Customer' could not be set to a 'Decimal' value. 
You must set this property to a non-null value of type 'Int32'.

Answer 1

The simplest solution is to use another field that will be mapped to the database field, and the ID property will read/write to this field. Something like this:

public class Customer : IEntity
{
   public decimal CustomerID {get; set;}

   [NotMapped]
   public int Id 
   { 
      get { return (int)CustomerID; }
      set { CustomerID = (int)value; } 
   }

   public string FirstName { get; set; }

   public string LastName { get; set; }
}

Map this new field to the database field, using

this.Property(x => x.CustomerID).HasColumnName("Customer_id");

and the EF will use the customer id field, and your code could happily use the integer id field.

Answer 2

Specify numeric type for column

Property(x => x.Id).HasColumnName("Customer_id").HasColumnType("numeric");

When generating database, it will create numeric column with precision 18,0. But when you are mapping to existing database, it will work fine with 5,0 numeric column.

Answer 3

You can convert it to an int with:

 int myInt = Convert.Int32(numberFromDatabase);

Answer 4

Another option to work out what the code first should be is to the MS EF Powertools http://visualstudiogallery.msdn.microsoft.com/72a60b14-1581-4b9b-89f2-846072eff19d

You can add an empty project, right click, EF Reverse engineer code first from DB.

You often (not always) get a good start to the solution. Did you try using decimal?

public class Customer : IEntity
{
 public decimal Id { get; set; }

 public string FirstName { get; set; }

 public string LastName { get; set; }
}

if you are concerned precision, you should add validation to the POCO.

There are also some interesting Anotation hacks you may like. http://geekswithblogs.net/danemorgridge/archive/2010/12/20/ef4-code-first-control-unicode-and-decimal-precision-scale-with.aspx good luck