格仔 Blog: For Code-First Approach in Entity Framework 6.0:

1 Setup. Entity Framework is more or less an analogue of Knex.js that accesses the database with "sequel like" methods. In knex we have yarn knex migrate:make, yarn knex migrate:latest, etc, and they all have a counterpart in .net framework. If our application is chosen to be "NO AUTHETICATION", we need to add a class file written as:

using System.Data.Entity
 
public class MyDbContext : DbContext
{
    public MyDbContext()
    {
    }
}

With that we are able to use

1	`enable-migrations`

in package manager console (note that the console is not case-sensitive), resulting in

1 2	`Checking if the context targets an existing database...` `Code First Migrations enabled for project ReallyTrue.`

Next if our application is set to be "AUTHENTICATED INDIVIDUALLY", then an identity model will be generated automatically accompanied by DbContext definition, therefore directly typing "enable-migrations" in the package console will do. However, we need to change the following in web.config in order to customize which SQL server we are connected to:

< add name="TestMigration5" connectionString="Data Source=DESKTOP-LNOHRJR;
Initial Catalog=aspnet-TestMigration5-20190818112608;
Integrated Security=True; 
user ID=YOURID;password =YOURPW"
providerName="System.Data.SqlClient" />

Since web.config will save our private information, without .env approach we should always use a hashed password and save it in notepad++.

2 Setup a Table with Auto-Incremental Primary key. Now our database just consists of tables of asp.users information, in order to add additional entity table (for example, we add our "Customers", "Product", etc, tables) we need to refer them in identiyModel.cs by adding:

public class ApplicationDbContext : IdentityDbContext<Applicationuser>
    {
        public DbSet<Customer> Customers { get; set; }
 
        public ApplicationDbContext()
            : base("DefaultConnection", throwIfV1Schema: false)
        {
        }
 
        public static ApplicationDbContext Create()
        {
            return new ApplicationDbContext();
        }
    }

Visual studio will detect and record our changes, by typing add-migration AddCustomer in package console, we get

using System;
using System.Data.Entity.Migrations;
 
namespace ReallyTrue.Migrations
{
    public partial class AddCustomer : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Customers",
                c => new
                    {
                        Id = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                    })
                .PrimaryKey(t => t.Id);       
        }
         
        public override void Down()
        {
            DropTable("dbo.Customers");
        }
    }
}

note that any property of a class named with Id (with type of byte, int, ... etc) will be identified as a primary key. The migration will take effect if we now input update-database in the console. A natural question: is the id set to be auto-increment? The answer is positive:

3 Foreign Key. Next for each customer we want to add a property called membership. memberships have different prices, each membership has a duration, and each membership also has a discount rate. Moreover, customer will have just one membership type. We construct a membership type as follows:

namespace ReallyTrue.Models
{
    public class MembershipType
    {
        public int Id { get; set; }
        public short SignUpFee { get; set; }
        public byte DurationInMonths { get; set; }
        public byte DiscountRate { get; set; }
    }
}

And now we "add the membership" to each customer. By standard database design pattern we will refer to a membership by referring to its id as follows:

using ReallyTrue.Models;
 
namespace ReallyTrue.Models
{
    public class Customer
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int MembershipTypeId { get; set; }
    }
}

The Type + Id naming convention will be automatically identified as a foreign key.

By the way, since a customer may not subscribe to any membership, I have to alter the table as follows:

and out of expected, by typing add-migration ChangeMembershipIdTobeNullable, instead of giving me an error, entity framework do all the subtle work for me!!!!

using System.Data.Entity.Migrations;
 
namespace ReallyTrue.Migrations
{
    public partial class ChangeMembershipIdTobeNullable : DbMigration
    {
        public override void Up()
        {
            DropForeignKey("dbo.Customers", "MembershipTypeId", "dbo.MembershipTypes");
            DropIndex("dbo.Customers", new[] { "MembershipTypeId" });
            AlterColumn("dbo.Customers", "MembershipTypeId", c => c.Int());
            CreateIndex("dbo.Customers", "MembershipTypeId");
            AddForeignKey("dbo.Customers", "MembershipTypeId", "dbo.MembershipTypes", "Id");
        }
         
        public override void Down()
        {
            DropForeignKey("dbo.Customers", "MembershipTypeId", "dbo.MembershipTypes");
            DropIndex("dbo.Customers", new[] { "MembershipTypeId" });
            AlterColumn("dbo.Customers", "MembershipTypeId", c => c.Int(nullable: false));
            CreateIndex("dbo.Customers", "MembershipTypeId");
            AddForeignKey("dbo.Customers", "MembershipTypeId", "dbo.MembershipTypes", "Id", cascadeDelete: true);
        }
    }
} 

4 Seeding. Unlike knex.js we add a seed file and run yarn knex seed:run, we directly insert value via migration as follows: first we run add-migration PopulateMembershipTypes and in that migration file we write

using System;
using System.Data.Entity.Migrations;
 
namespace ReallyTrue.Migrations
{
    public partial class PopulateMembershipTypes : DbMigration
    {
        public override void Up()
        {
            Sql("INSERT INTO MembershipTypes (SignUpFee, DurationInMonths, DiscountRate) VALUES (0,0,0)");
            Sql("INSERT INTO MembershipTypes (SignUpFee, DurationInMonths, DiscountRate) VALUES (30,1,10)");
            Sql("INSERT INTO MembershipTypes (SignUpFee, DurationInMonths, DiscountRate) VALUES (90,3,15)");
            Sql("INSERT INTO MembershipTypes (SignUpFee, DurationInMonths, DiscountRate) VALUES (300,12,20)");
        }
 
        public override void Down()
        {
        }
    }
}

Now update-database, done!

5 DataAnnotation. Unforturnately some data types are nullable by default, we need annotation (writing attribute) to enforce restriction to these data type:

using ReallyTrue.Models;
using System.ComponentModel.DataAnnotations;
 
namespace ReallyTrue.Models
{
    public class Customer
    {
        public int Id { get; set; }
        [Required]
        [StringLength(255)]
        public string Name { get; set; }
        public bool IsSubscribedToNewsletter { get; set; }
        public MembershipType MembershipType { get; set; }
        public int? MembershipTypeId { get; set; }
    }
} 

6 Eager Loading (Essentially Inner Join). To inner join two tables we don't explicitly do any sequel having INNER JOIN clause. Let's for example in any controller instansciate the database as follows

public class HomeController : Controller
    {
        private ApplicationDbContext _context;
 
        public HomeController()
        {
            _context = new ApplicationDbContext();
        }
 
        protected override void Dispose(bool disposing)
        {
            _context.Dispose();
        }
 
        public ActionResult Customer()
        {
            ViewBag.title = "Customer";
            ViewBag.Message = "All Customer";
            var customers = _context.Customers.Include("MembershipType").ToList();
 
            return View(customers);
        }
    }
}

The Include method will append the associated MembershipType (a list) to customer as a property. For joining multiple tables, see

https://stackoverflow.com/questions/14748559/linq-include-a-join-on-the-allready-joined-tables

Finally the following .cshtml

@{
    ViewBag.Title = "About";
}
@model List<reallytrue.models.customer>
<h2>@ViewBag.Title.</h2>
<h3>@ViewBag.Message</h3>
<ul>
    @foreach (var customer in Model)
    {
        <li >@customer.Name with membership of discount rate
        customer.MembershipType.DiscountRate% </li >
    }
</ul>

generates the following view:

格仔 Blog

Pages

Saturday, August 17, 2019

For Code-First Approach in Entity Framework 6.0:

No comments:

Post a Comment