locked
Questions on EF4 CTP4 code-first #1: Supported sql datatypes RRS feed

  • Question

  • How to implement the following sql datatypes in code-first:

    -Bit

    -money

    -image

    -text/ntext

    -date

    -time

    -tinyint

    If any of them is not supported then please provide any workarounds.

    Tuesday, September 21, 2010 10:09 AM

Answers

  • Hi,

    All of these data types are supported, for some it is just a case of declaring the corresponding property in your object model as a particular type. For others you will need to use the Fluent API to specify the particular type.·         -bit: Define the property in your object model as ‘bool’

      -money: Define the property in your object model as ‘decimal’ use the Fluent API to specify a store type of ‘money’

      -image: Define the property in your object model as ‘byte[]’ use the Fluent API to specify a store type of ‘image’

      -text/ntext: Define the property in your object model as ‘string’ use the Fluent API to specify a store type of ‘text’

      -date: Define the property in your object model as ‘DateTime’

      -time: Define the property in your object model as ‘TimeSpan’

      -tinyint: Define the property in your object model as ‘Byte’ use the Fluent API to specify a store type of ‘tinyint’

    Here is an example of using the Fluent API to get the ‘money’ store type:

    public class BlogContext : DbContext
    {
     public DbSet<Transaction> Transactions { get; set; }
    
     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
      modelBuilder.Entity<Transaction>()
       .Property(p => p.Amount)
       .HasStoreType("money");
     }
    }
    
    public class Transaction
    {
     public int TransactionId { get; set; }
     public decimal Amount { get; set; }
    }
    ~Rowan
    • Proposed as answer by Rowan MillerModerator Tuesday, September 21, 2010 10:49 PM
    • Marked as answer by alaa9jo Wednesday, September 22, 2010 10:13 AM
    Tuesday, September 21, 2010 10:24 PM
    Moderator

All replies

  • Hi,

    All of these data types are supported, for some it is just a case of declaring the corresponding property in your object model as a particular type. For others you will need to use the Fluent API to specify the particular type.·         -bit: Define the property in your object model as ‘bool’

      -money: Define the property in your object model as ‘decimal’ use the Fluent API to specify a store type of ‘money’

      -image: Define the property in your object model as ‘byte[]’ use the Fluent API to specify a store type of ‘image’

      -text/ntext: Define the property in your object model as ‘string’ use the Fluent API to specify a store type of ‘text’

      -date: Define the property in your object model as ‘DateTime’

      -time: Define the property in your object model as ‘TimeSpan’

      -tinyint: Define the property in your object model as ‘Byte’ use the Fluent API to specify a store type of ‘tinyint’

    Here is an example of using the Fluent API to get the ‘money’ store type:

    public class BlogContext : DbContext
    {
     public DbSet<Transaction> Transactions { get; set; }
    
     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
      modelBuilder.Entity<Transaction>()
       .Property(p => p.Amount)
       .HasStoreType("money");
     }
    }
    
    public class Transaction
    {
     public int TransactionId { get; set; }
     public decimal Amount { get; set; }
    }
    ~Rowan
    • Proposed as answer by Rowan MillerModerator Tuesday, September 21, 2010 10:49 PM
    • Marked as answer by alaa9jo Wednesday, September 22, 2010 10:13 AM
    Tuesday, September 21, 2010 10:24 PM
    Moderator
  • Thank you Rowan,I saw HasStoreType before but I didn't know it's use till now. :)

    Is it case sensitive? i.e.Can I write "MONEY" or "Money" without any issues? Can I specify a store type for any property using DataAnnotations or Fluent API is the only way?

    P.S.  Is there any references/documents with examples on code-first ? specially on fluent api ? I want to learn more about Fluent API and how to be used in code-first.

    Sorry for asking a lot of questions

    Thanks again

    Wednesday, September 22, 2010 10:13 AM
  • Hi,

    The case sensitivity depends on the provider you are using, our SQL Client provider is case sensitive so you need to use lower case.

    In CTP4 there isn't a data annotation for store type but there will be in future releases. So at the moment the Fluent API is the only way to change it.

    Because Code First is still in preview and we are evolving the API surface there isn't a single exhaustive set of documentation for it, here are a few links:

    ~Rowan

    Monday, September 27, 2010 4:45 PM
    Moderator
  • Hi Rowan, I posted a similar question on mapping that is more real-world - hopefully you can help me because we're seriously targeting EF4 for code-first to our existing database.

    see below configuration mapping, how can I map or convert a CHAR(1) field called IS_ACTIVE to my bool property.  We cannot change the database to use BIT and most enterprise shops use CHAR(1) fields for storing boolean. 

    Hopefully there is an easy solution?

    thanks

    Marty

     [Serializable]
        public partial class ProposalConfiguration : EntityConfiguration<Proposal>
        {
            public ProposalConfiguration()
            {
                HasKey(e => e.ProposalId);

                MapSingleType(e => new
                {

                    PROPSL_ID = e.ProposalId,               

                    IS_ACTIVE = e.IsActive,  // How can we map/convert char(1) to bool - this needs a converter function like Automapper does...

                }

     

    Thursday, October 21, 2010 6:09 PM
  • Copying answer to Marty's question for anyone reading this thread...

    Hi Marty,

    This isn't possible in EF at this stage, we do plan to provide some extensibility for mapping between your model and CLR objects in a future release (which would support this scenario among others). For the moment the best workaround is to add a second property that does the read/write conversion.

    ~Rowan

    Saturday, October 30, 2010 10:28 PM
    Moderator
  • I am trying this with CTP5 using this code

    modelBuilder.Entity<Accommodation>()
         .Property(p => p.Information)
         .HasColumnType("text");
    

    but SQL Server express is still showing the column  as nvarchar(4000)

     

    Thursday, January 27, 2011 10:49 AM
  • Ahh I just figured it out, adding the fluent mapping was not sufficient to force the table to dropAndCreate itself again, I had to force it to update manually.
    Thursday, January 27, 2011 11:31 AM

  • protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Transaction>() .Property(p => p.Amount) .HasStoreType("money"); }
    
    

    The version of POCO I'm using doesn't have "HasStoreType". I does have
    "HasColumnType", is that what is to be used now?
    Monday, January 31, 2011 11:31 PM
  • Hi,

    Yes, that is the CTP5 version of the same API.

    ~Rowan

    Friday, February 4, 2011 6:05 PM
    Moderator
  • edit: just saw MS SQL considers tinyint to be 0 to 255, so my first part of the reply is wrong.

    Mapping a 'byte' to 'tinyint' is dangerous, since 'tinyint' is signed (-128 to +127), whereas 'byte' is not (0 to 255).  Wouldn't it make more sense to map 'tinyint' to 'sbyte'?

     

    Also, I know it's not a serious consideration for you guys, but MySQL supports Unsigned data types.  With the same line of reasoning as earlier, I generally like to make my primary keys UNSIGNED and AUTO_INCREMENT, starting from zero.  Since I won't have negative types that way, it makes sense to map them in my code to uint, ulong, and ushort.  Right now EF throws exceptions if you try to use unsigned integer types as primary keys.

    Thursday, February 24, 2011 8:09 PM
  • Hello *,

    One of the ways you can can accomplish that is by mapping the entity to QueryView. QueryView is basically an esql view where you query the ssdl model and you define how the data is mapped to the conceptual model. You will have to map the entities to your stored procedures for crud operations. 

    You can still use your DbContext api its just that you will have to use it with an existing model.


    Zeeshan Hirani Entity Framework 4.0 Recipes by Apress
    http://weblogs.asp.net/zeeshanhirani
    Friday, February 25, 2011 3:57 PM