Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to map column and entity propery of different datatypes in entity framework code first

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'.
like image 903
Brendan Vogt Avatar asked Feb 11 '13 13:02

Brendan Vogt


2 Answers

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.

like image 196
Sergey Berezovskiy Avatar answered Nov 15 '22 21:11

Sergey Berezovskiy


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.

like image 4
SWeko Avatar answered Nov 15 '22 23:11

SWeko