Entity Framework: Help Creating Database using Code first Approach

Question

OK 2 questions: 1. I am new to Databases and I want to learn the code first approach to creating a Database. I have used the Model-first approach a few times. Can anyone help me with coding this DB using Code-first approach (I also would like to add an Employee table if possible)? Here is the link to the DB Diagram:

http://databaseanswers.org/data_models/customers_and_orders/images/customers_and_orders_ref_data_model.gif

Also, how would I insert into lets say the customer table / customer address address all in one go, using entity framework of course?

Thank you in advance for anyone willing to help.

Oshadha · Accepted Answer · 2015-08-02 12:04:41Z

1

You can do it like follows:

Please note the this solution has done as a console application.

Please add the following class to do this as code first:

        public class Customer
            {
                [Key]
                public int CustomerId { get; set; }
                public string FirstName { get; set; }
                public string MiddleName { get; set; }
                public string LastName { get; set; }
                public string Phone { get; set; }
                public string Email { get; set; }
                public string CustomerOtherDetails { get; set; }
            }

            public class CustomerAddress
            {
                [ForeignKey("Customer")]
                public int CustomerId { get; set; }
                [ForeignKey("AddressType")]
                public int AddressTypeId { get; set; }
                [ForeignKey("Address")]
                public int AddressId { get; set; }
                [Key]
                public DateTime DateFrom { get; set; }
                public DateTime DateTo { get; set; }
                public virtual Customer Customer { get; set; }
                public virtual AddressType AddressType { get; set; }
                public virtual Address Address { get; set; }
            }

            public class AddressType
            {
                [Key]
                public int AddressTypeId { get; set; }
                public string AddressTypeDescriptiom { get; set; }
            }

            public class Address
            {
                [Key]
                public int AddressId { get; set; }
                public string Line1 { get; set; }
                public string Line2 { get; set; }
                public string Line3 { get; set; }
                public string City { get; set; }

            }

When you do it Code First approach you need to create the model out of the class you created and it can be done as follows:

Context class should be like follows:

    public class CustomerContext : DbContext
    {
        public CustomerContext()
            : base("DBConnectionString")
        {
            //If model change, It will re-create new database.
            Database.SetInitializer(new DropCreateDatabaseIfModelChanges<CustomerContext>());
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            //Set primary key to Customer table
            modelBuilder.Entity<Customer>().HasKey(m => m.CustomerId);


            //set primary key to Address table
            modelBuilder.Entity<CustomerAddress>().HasKey(m => m.DateFrom);

            modelBuilder.Entity<AddressType>().HasKey(m => m.AddressTypeId);
            modelBuilder.Entity<Address>().HasKey(m => m.AddressId);

            //Set foreign key property
            modelBuilder.Entity<CustomerAddress>().HasRequired(t => t.Customer)
                .WithMany().HasForeignKey(t => t.CustomerId);

            modelBuilder.Entity<CustomerAddress>().HasRequired(t => t.AddressType)
                .WithMany().HasForeignKey(t => t.AddressTypeId);

            modelBuilder.Entity<CustomerAddress>()
                .HasRequired(t => t.Address)
                .WithMany()
                .HasForeignKey(t => t.AddressId);
        }

Database creating and the inserting address with a customer should be like below:

    static void Main(string[] args)
            {
                using (var ctx = new CustomerContext())
                {
                    //ctx.Database.Create(); // This command can be used to create the database using the code first class
                    ctx.CustomerAddresses.Add(new CustomerAddress
                    {
                        AddressType = new AddressType
                        {
                            AddressTypeId = 1,
                            AddressTypeDescriptiom = "Test"
                        },
                        Customer = new Customer
                        {
                            CustomerId = 1,
                            FirstName = "Customer 1"
                        },
                        Address = new Address
                        {
                            Line1 = "Line 1",
                            City = "USA"
                        },
                        DateFrom = DateTime.Now,
                        DateTo = DateTime.Now
                    });

                    ctx.SaveChanges();
                }
            }

Connection string should like below:

    <connectionStrings>
        <add name="DBConnectionString"
            connectionString="Data Source=(local);Initial Catalog=CustomerDB;Integrated Security=true"
            providerName="System.Data.SqlClient"/>
      </connectionStrings>

Please note following note the above code need following references.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;

I tried this and it works as you expected. Please let me know whether you need a sample project on this.

Hope this helps you.

EDITED:

To configure self reference in code first please do as below:

public class Product
    {
        [Key]
        public int ProductId { get; set; }

        public int? ParentProductId { get; set; }

        public virtual Product ParentProduct { get; set; }
    }

Add the following code lines in OnModelCreating method:

modelBuilder.Entity<Product>().HasKey(m => m.ProductId);
modelBuilder.Entity<Product>().
                HasOptional(e => e.ParentProduct).
                WithMany().
                HasForeignKey(m => m.ParentProductId);

edited Aug 2, 2015 at 12:04

answered Aug 2, 2015 at 10:14

Oshadha

54810 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

BezzPezz Over a year ago

Firstly thank you very much Oshadha for your response. This partly answers my questions, however there are a few things further to your response that I need to know such as the circular relationships on the products table -> how would I implement this in code first??

Oshadha Over a year ago

Let me know the things that I haven't answered in above answer.

BezzPezz Over a year ago

Oshadha thank you once again your help is much appreciated, now I just need to know where to place this code. Will it lie in the models folder, in one of my classes? where would you put it ? (I'm using MVC5 asp.net application) and is there a way to add this DB to the existing ASPIdent DBContext that comes out of the box in a new MVC5 project? therefore kind of extending it?

Oshadha Over a year ago

You can keep your code first code in a separate project called Company.Project.Model and I'm not getting the second part of your question. Could you please elaborate it?

BezzPezz Over a year ago

OK.. when you start a new project is VS201* you get the option to use MVC and so forth. Then once the project is created automatically with an application db context called applicationDBContext. DO you know how I would extend this premade context model and insert the DB above?

|

Alex Fajardo · Accepted Answer · 2015-08-02 08:08:16Z

0

For the Employee Table, You Can create a class (Employee.cs):

public class Employees {

public string FName {get;set;} public string LName {get;set;} public string Position {get;set;} public string Email {get;set;} [Display(Name = "Full Name")] public string FullName { get { return LName + ", " + FName; } }

}

For the insert, You can do :

 var users = new List<User>
    {
        new User{FName ="Chris", LName ="Fajardo",Position=@"Dev",Email="[email protected]"}
    };
    users.ForEach(s => context.User.Add(s));
    context.SaveChanges();

edited Aug 2, 2015 at 8:08

answered Aug 2, 2015 at 8:02

Alex Fajardo

113 bronze badges

Collectives™ on Stack Overflow

Entity Framework: Help Creating Database using Code first Approach

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related