none
How to create a class for this table? RRS feed

  • Question

  • Hi everybody,

    I already have my tables and database designed. I am working on the ASP.NET MVC 4 application and I want to define EF classes for my database. I have this table

    CREATE TABLE [dbo].[Clients](
    	[ClientID] [int] IDENTITY(1,1) NOT NULL,
    	[client_no] [smallint] NOT NULL,
    	[client_name] [varchar](30) NULL,
    	[Contact1] [varchar](100) NULL,
    	[C1_Email] [varchar](100) NULL,
    	[Contact2] [varchar](100) NULL,
    	[C2_Email] [varchar](100) NULL,
    	[C1_Phone] [varchar](10) NULL,
    	[C1_Ext] [varchar](5) NULL,
    	[C2_Phone] [varchar](10) NULL,
    	[C2_Ext] [varchar](5) NULL,
    	[Address] [varchar](max) NULL,
    	[EnteredBy] [char](6) NOT NULL,
    	[EnteredOn] [smalldatetime] NULL,
    	[ModifiedBy] [char](6) NULL,
    	[ModifiedOn] [smalldatetime] NULL,
     CONSTRAINT [PK_Clients_ClientID] PRIMARY KEY CLUSTERED 
    (
    	[ClientID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[Clients]  WITH CHECK ADD  CONSTRAINT [FK_Clients_Operators_EnteredBy] FOREIGN KEY([EnteredBy])
    REFERENCES [dbo].[Operators] ([op_code])
    GO
    
    ALTER TABLE [dbo].[Clients] CHECK CONSTRAINT [FK_Clients_Operators_EnteredBy]
    GO
    
    ALTER TABLE [dbo].[Clients]  WITH CHECK ADD  CONSTRAINT [FK_Clients_Operators_ModifiedBy] FOREIGN KEY([ModifiedBy])
    REFERENCES [dbo].[Operators] ([op_code])
    GO
    
    ALTER TABLE [dbo].[Clients] CHECK CONSTRAINT [FK_Clients_Operators_ModifiedBy]
    GO
    
    ALTER TABLE [dbo].[Clients]  WITH CHECK ADD  CONSTRAINT [CK_Clients_C1_Phone] CHECK  (([C1_Phone] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
    GO
    
    ALTER TABLE [dbo].[Clients] CHECK CONSTRAINT [CK_Clients_C1_Phone]
    GO
    
    ALTER TABLE [dbo].[Clients]  WITH CHECK ADD  CONSTRAINT [CK_Clients_C2_Phone] CHECK  (([C2_Phone] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))
    GO
    
    ALTER TABLE [dbo].[Clients] CHECK CONSTRAINT [CK_Clients_C2_Phone]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_EnteredBy]  DEFAULT ('ADMIN') FOR [EnteredBy]
    GO
    
    ALTER TABLE [dbo].[Clients] ADD  CONSTRAINT [DF_Clients_EnteredOn]  DEFAULT (getdate()) FOR [EnteredOn]
    GO
    

    I need to translate it into Clients class I will use in the Entity Framework.

    1) My columns are varchar and I know that Entity Framework works with nvarchar. Should I change all my columns to be nvarchar or is there a better solution?

    EnteredBy and ModifiedBy columns refer to Operators table. How should I properly code that clients class?

    Also, what is the right way to map names I chose for the Properties into the actual column names?

    Thanks a lot in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, September 4, 2012 7:30 PM

Answers

  • Hi,

    The annotations mapping you have appears to be fine, if it is working for you. I cannot see anything obvious that is wrong but I have not tried to create your table and run it either.

    To answer some of your earlier questions that I missed.

    You can configure EF to not use nvarchar if you like. If you use the IsUnicode method to specify that a column should be of type varchar in the fluent api. Something like:

    modelBuilder.Entity<Entity>().Property(x=>x.Name).IsUnicode(false)

    The two ways to map column names are via annotations and the fluent api. The power tools would have created a bunch of fluent api mappings, so you will have seen them there, and the annotaitons are the way you are doing it in the sample you have. The fluent api can do more, there is functionality that is not exposed via annotations, but either is a legitimate choice. It depends on what you prefer and your scenario. Some information on both can be found on the EF msdn site http://msdn.microsoft.com/en-us/data/ee712907.

    Does that help some more?


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.


    Monday, September 10, 2012 11:45 PM
    Moderator

All replies

  • Hi,

    The easiest way to do this would be to generate an EF Model from the database that you have already created.

    If you add a new ADO.NET Entity Data Model to your solution then you should get the option to generate that model from an existing database. This will create all the entities for you, and map them to the table.

    Once you have a model you can change the property names on the designer surface, but EF will still map them back to your database columns for you.

    If you prefer a code-first approach instead of having a model generated then you could use the EF Power Tools to generate a bunch of Code First classes for your application, it will also generate the mapping code. You can then customize the mapping code and classes as you need. I think this approach would be more work than the model approach though.

    If you go with one of those two options to generate the entities from the existing database then the resulting code, or model, should provide you with answers to your other questions. But let us know if not, or if I am misunderstanding something.


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.


    Wednesday, September 5, 2012 8:00 PM
    Moderator
  • What is EF Power Tools?

    I've created Client already by hand and I'm up to the operator. This is how I coded it manually, do you see any problems?

    using System;
    
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel;
    
    using DataAnnotationsExtensions;
    using System.ComponentModel.DataAnnotations.Schema;
    
    namespace CardNumbers.Objects
    {
        public class Client
        {
            [Key]
            [Column("ClientId",TypeName = "int")]
            public virtual int Id { get; set; }
            [Required]
            [DisplayName("Client No")]
            [Column("client_no", TypeName = "smallint")]
            public virtual Int16 Number { get; set; }
            
            [Required]
            [Column("client_name", TypeName = "varchar(30)")]
            [DisplayName("Client Name")]
            [MaxLength(30, ErrorMessage = "Client Name should not be longer than 30 characters" ), MinLength((3))]
            public virtual string Name { get; set; }
    
            [StringLength(100)]
            [DisplayName("First Contact")]
            [Column("Contact1",TypeName =  "varchar(100)")]
            public virtual string Contact1 { get; set; }
    
            [Email]
            [StringLength(100)]
            [Column("c1_email", TypeName = "varchar(100)")]
            public virtual string Email1 { get; set; }
    
            [DataType(DataType.PhoneNumber)]
            [Column("C1_Phone", TypeName = "varchar(10)")]
            [StringLength(10)]
            [DisplayName("Phone")]
            public virtual string Phone1 { get; set; }
    
            [StringLength(5)]
            [Column("C1_Ext", TypeName = "varchar(5)")]
            [DisplayName("Ext")]
            public virtual string Ext1 { get; set; }
    
            [StringLength(100)]
            [DisplayName("Second Contact")]
            [Column("Contact2", TypeName = "varchar(100)")]
            public virtual string Contact2 { get; set; }
    
            [Email]
            [StringLength(100)]
            [Column("C2_Email", TypeName = "varchar(100)")]
            public virtual string Email2 { get; set; }
    
            [DataType(DataType.PhoneNumber)]
            [StringLength(10)]
            [DisplayName("Phone")]
            [Column("C2_Phone", TypeName = "varchar(10)")]
            public virtual string Phone2 { get; set; }
    
            [StringLength(5)]
            [DisplayName("Ext")]
            [Column("C2_Ext",TypeName = "varchar(5)")]
            public virtual string Ext2 { get; set; }
    
            [DataType(DataType.MultilineText)]
            public virtual string Address { get; set; }
    
            public virtual Operator EnteredOperator { get; set; }
    
            [ForeignKey("EnteredBy")]
            public virtual string EnteredBy { get; set; }
    
            public virtual Operator ModifiedOperator { get; set; }
    
            [ForeignKey("ModifiedBy")]
            public virtual string ModifiedBy { get; set; }
    
            [DataType(DataType.DateTime)]
            [DisplayName("Created on")]
            public DateTime EnteredOn { get; set; }
    
            [DataType(DataType.DateTime)]
            [DisplayName("Modified on")]
            public DateTime ModifiedOn { get; set; }
        }
    }


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, September 5, 2012 9:25 PM
  • Please look at here. EF Power Tool is really fantastic!

    Go go Doraemon!

    Friday, September 7, 2012 2:33 AM
  • Hi Naomi N,

    Do @Glenn's and @Dorado999's solutions can help you to solve the issue? If you need further help, please feel free to let me know.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, September 10, 2012 2:58 AM
    Moderator
  • I looked into EF Beta Tools and I did generate the classes, but it used my current column names so I still have to do some work manually as I wanted to use better names.

    I'm wondering if someone can look into what I posted and let me know if this is OK model?

    Thanks.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, September 10, 2012 11:13 PM
  • Hi,

    The annotations mapping you have appears to be fine, if it is working for you. I cannot see anything obvious that is wrong but I have not tried to create your table and run it either.

    To answer some of your earlier questions that I missed.

    You can configure EF to not use nvarchar if you like. If you use the IsUnicode method to specify that a column should be of type varchar in the fluent api. Something like:

    modelBuilder.Entity<Entity>().Property(x=>x.Name).IsUnicode(false)

    The two ways to map column names are via annotations and the fluent api. The power tools would have created a bunch of fluent api mappings, so you will have seen them there, and the annotaitons are the way you are doing it in the sample you have. The fluent api can do more, there is functionality that is not exposed via annotations, but either is a legitimate choice. It depends on what you prefer and your scenario. Some information on both can be found on the EF msdn site http://msdn.microsoft.com/en-us/data/ee712907.

    Does that help some more?


    We are seeing a lot of great Entity Framework questions (and answers) from the community on Stack Overflow. As a result, our team is going to spend more time reading and answering questions posted on Stack Overflow. We would encourage you to post questions on Stack Overflow using the entity-framework tag. We will also continue to monitor the Entity Framework forum.


    Monday, September 10, 2012 11:45 PM
    Moderator
  • Thanks. BTW, can typename in Data Annotations do the same or it needs to be done through fluent API?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, September 11, 2012 12:30 AM