Database Initialization Strategies

Entity Framework offers four approaches for handling database initialization:

Strategy One: Create Database Only If It Doesn't Exist

Database.SetInitializer<ApplicationContext>(new CreateDatabaseIfNotExists<ApplicationContext>());

Strategy Two: Recreate Database on Each Application Startup

Database.SetInitializer<ApplicationContext>(new DropCreateDatabaseAlways<ApplicationContext>());

Strategy Three: Recreate Database When Model Changes

Database.SetInitializer<ApplicationContext>(new DropCreateDatabaseIfModelChanges<ApplicationContext>());

Strategy Four: Never Initialize Database Automatically

Database.SetInitializer<ApplicationContext>(null);

When using DropCreateDatabaseAlways, an error may occur stating "Cannot drop database because it is currently in use." To resolve this, disconnect all active connections to the target database before attempting deletion.

Seeding Initial Data

To populate newly created databases with default values, implement a seeding mechanism. When utilizing the DropCreateDatabaseAlways strategy, create a custom initializer class inheriting from it and specify the DbContext type as a generic parameter. Override the Seed method to insert data into the context:

using System;
using System.Data.Entity;
using Models;

public class DataSeeder : DropCreateDatabaseAlways<ApplicationContext>
{
    protected override void Seed(ApplicationContext context)
    {
        var individual = new Person("0002", "cuiyanwei", SexType.Female, 26);
        var individual2 = new Person("0003", "cuiyanwei1", SexType.Female, 25);
        context.People.Add(individual);
        context.People.Add(individual2);
        base.Seed(context);
    }
}

Note that SaveChanges() is not required within the seed method. Assign this initializer to the context constructor:

public ApplicationContext() : base("MyStrConn")
{
    Database.SetInitializer<ApplicationContext>(new DataSeeder());
}

Avoid setting the initializer again inside OnModelCreating.

Executing Raw SQL Queries

In some cases, direct SQL execution is necessary for complex queries or accessing views.

using (var db = new ApplicationContext())
{
    var individuals = db.Database.SqlQuery<Person>(
        "SELECT * FROM Person WHERE PersonId = @id",
        new SqlParameter("@id", "0002")
    ).ToList();
    
    foreach (var person in individuals)
    {
        Console.WriteLine("{0}  {1}  {2}  {3}", person.PersonId, person.Name, person.Age, person.Sex);
    }
}
Console.ReadLine();

After creating a view named TestView in the database, which selects from the Person table, switch to CreateDatabaseIfNotExists to prevent data loss during initialization. Modify the seeder class accordingly:

public class DataSeeder : CreateDatabaseIfNotExists<ApplicationContext>
{
    protected override void Seed(ApplicationContext context)
    {
        var individuals = db.Database.SqlQuery<Person>(
            "SELECT * FROM TestView WHERE PersonId = @id",
            new SqlParameter("@id", "0002")
        ).ToList();
        
        foreach (var person in individuals)
        {
            Console.WriteLine("{0}  {1}  {2}  {3}", person.PersonId, person.Name, person.Age, person.Sex);
        }
    }
}

Invoking Stored Procedures

For operations that are cumbersome with LINQ, stored procedures provide an alternative. For example, retrievnig hierarchical data like parent or child nodes in a tree structure can be simplified through stored procedures.

CREATE PROCEDURE TestProcedure
    @personId nvarchar(100)
AS
BEGIN
    SELECT * FROM Person WHERE PersonId = @personId;
END
GO

Execute the stored procedure using SqlQuery:

var individuals = db.Database.SqlQuery<Person>(
    "TestProcedure @personId",
    new SqlParameter("@personId", "0003")
).ToList();

foreach (var person in individuals)
{
    Console.WriteLine("{0}  {1}  {2}  {3}", person.PersonId, person.Name, person.Age, person.Sex);
}

For update operations, use ExecuteSqlCommand instead:

int rowsAffected = db.Database.ExecuteSqlCommand(
    "UPDATE Person SET Name = @name WHERE PersonId = @personId",
    new[] 
    { 
        new SqlParameter("@name", "CYW"), 
        new SqlParameter("@personId", "0003") 
    }
);
Console.WriteLine(rowsAffected);

The above operation successfully updates the name of the record with ID '0003' from 'cuiyanwei' to 'CYW'.