All notes
EntityFramework

Walkthrough

MSDN.

Create model classes


public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; }

    public virtual List<Post> Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; }
    public string Content { get; set; }

    public int BlogId { get; set; }
    public virtual Blog Blog { get; set; }
}

The reason why the Navigation Properties must be virtual is given in this statckOverflow article. Navigation properties are marked as virtual for lazy loading and scalar properties are marked as virtual for change tracking.

This SO explains more. The virtual keyword has the same meaning: modifying the meaning (override) of the base class item. In Entity Framework, your POCO classes are created and wrapped into a proxy class, which is a descendant of the class that you declare.

Plain Old CLR Object or POCO is a play on the term POJO, from the Java EE programming world (which was coined by Martin Fowler in 2000). Simply put, a POCO does not have any dependency on an external framework. Wikipedia.

Create a Context


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Entity;

namespace CodeFirstNewDatabaseSample
{
    class Program
    {
        static void Main(string[] args)
        {
        }
    }

    public class Blog
    {
        public int BlogId { get; set; }
        public string Name { get; set; }

        public virtual List<Post> Posts { get; set; }
    }

    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }

        public int BlogId { get; set; }
        public virtual Blog Blog { get; set; }
    }

    public class BloggingContext : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }
    }
}

Reading and writing data


class Program
{
    static void Main(string[] args)
    {
        using (var db = new BloggingContext())
        {
            // Create and save a new Blog
            Console.Write("Enter a name for a new Blog: ");
            var name = Console.ReadLine();

            var blog = new Blog { Name = name };
            db.Blogs.Add(blog);
            db.SaveChanges();

            // Display all Blogs from the database
            var query = from b in db.Blogs
                        orderby b.Name
                        select b;

            Console.WriteLine("All blogs in the database:");
            foreach (var item in query)
            {
                Console.WriteLine(item.Name);
            }

            Console.WriteLine("Press any key to exit...");
            Console.ReadKey();
        }
    }
}

Data annotation


using System.ComponentModel.DataAnnotations;

// Now annotate the Username property to identify that it is the primary key.

public class User
{
    [Key]
    public string Username { get; set; }
    public string DisplayName { get; set; }
}

Primary key

SO: entitytype has no key defined. If there is error "EntityType 'Sth' has no key defined", it means EF can't determine the primary key. Either change the name to SthId or Id, or else put a [Key] attribute on it. wcfNote: if "SthId" and "Id" coexist, the former takes precedence.

That convention is to look for a property named “Id” or one that combines the class name and “Id”, such as “BlogId”. The property will map to a primary key column in the database.

wcfNote: the Id member must be Property. Actually, all members should be Properties, otherwise it will not appear in Database schema! See MSDN: Code First Conventions.


// For DatabaseGenerated.
using System.ComponentModel.DataAnnotations.Schema
// Assembly:  System.ComponentModel.DataAnnotations

public class Department
{
    // This will not work:
    // public Guid DepartmentID;

    // Primary key
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public Guid DepartmentID { get; set; }
}

SO: auto generate GUID.

Dealing with model change

Go to: Tools - Library Package Manager - Package Manager Console.

Run:


// First specify the project containing model definitions. Then run:

Enable-Migrations -EnableAutomaticMigrations
// To use an automatic migration instead, delete the Migrations folder and re-run Enable-Migrations specifying the -EnableAutomaticMigrations parameter.

Add-Migration MigrationName
// For the first migration, we use "init".
// If you make additional changes to your model that you want to include in this migration, then you can re-scaffold it by running the same 'Add-Migration MigrationName' again.

Update-Database
// Specify the '-Verbose' flag to view the SQL statements being applied to the target database.

Automatically update database

SO: Entity framework update database for production.

MSDN: Entity Framework Code First Migrations.

Manual update by script:


Update-Database -Script

Create idempotent script. Available from EF6.


Update-Database –SourceMigration $InitialDatabase

Idempotent script can upgrade a database currently at any version to the latest version, or the specified version if you use –TargetMigration. The generated script includes logic to check the __MigrationsHistory table and only apply changes that haven't been previously applied.

Automatically Upgrading on Application Startup:


using System.Data.Entity;
using MigrationsDemo.Migrations;

namespace MigrationsDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            Database.SetInitializer(new MigrateDatabaseToLatestVersion<BlogContext, Configuration>());
        }
    }
}

// "Database" here is a class defined in System.Data.Entity, which is used to manage databases. The default initializer is CreateDatabaseIfNotExists`1.
// "Configuration" is a class defined in MigrationsDemo.Migrations namespace, from "Migrations" directory, "Configuration.cs".
// wcfNote: The "Configuration" is scoped as "internal" by default. You need to change it to "public".

Check with it

MSDN.

  1. Click View - Server Explorer
  2. Right click on Data Connections and select Add Connection.
  3. If you haven’t connected to a database from Server Explorer before you’ll need to select Microsoft SQL Server as the data source.
  4. Connect to either LocalDb ((localdb)\v11.0) or SQL Express (.\SQLEXPRESS), depending on which one you have installed. wcfNote: I typed ".\sqlexpress".
  5. The database generated by default is named as "NamespaceName.TheContextClassName".
  6. We can now inspect the schema that Code First created by clicking on the triangle.

Update

MSDN.

Attaching an existing but modified entity to the context.

If you have an entity that you know already exists in the database but to which changes may have been made then you can tell the context to attach the entity and set its state to Modified.


var existingBlog = new Blog { BlogId = 1, Name = "ADO.NET Blog" };
 
using (var context = new BloggingContext())
{ 
    context.Entry(existingBlog).State = EntityState.Modified; 
 
    // Do some more work...  
 
    context.SaveChanges(); 
}

Insert or Update


public void InsertOrUpdate(Blog blog) 
{ 
    using (var context = new BloggingContext()) 
    { 
        context.Entry(blog).State = blog.BlogId == 0 ? 
                                   EntityState.Added : 
                                   EntityState.Modified; 
 
        context.SaveChanges(); 
    } 
}

Getting a SQL script

MSDN.


:: We want a script to go from an empty database ($InitialDatabase) to the latest version.
Update-Database -Script -SourceMigration: $InitialDatabase

If you don’t specify a target migration, Migrations will use the latest migration as the target. If you don't specify a source migrations, Migrations will use the current state of the database.

Attribute Annotation

Timestamp, ConcurrencyCheck

They are mainly used in concurrency check. See asp.net: Handling Concurrency with the Entity Framework 6, and cnblogs: Entity Framework 并发处理.

There are two ways to handle with concurrency.

Pessimistic concurrency. For example, before you read a row from a database, you request a lock for read-only or for update access. If you lock a row for update access, no other users are allowed to lock the row either for read-only or update access, because they would get a copy of data that's in the process of being changed. If you lock a row for read-only access, others can also lock it for read-only access but not for update.

Managing locks has disadvantages. It can be complex to program. It requires significant database management resources, and it can cause performance problems as the number of users of an application increases. For these reasons, not all database management systems support pessimistic concurrency, include EF.

Optimistic concurrency. It means allowing concurrency conflicts to happen, and then reacting appropriately if they do. You can resolve conflicts by handling OptimisticConcurrencyException exceptions that the Entity Framework throws. Some options for enabling conflict detection:

Timestamp to detect row-wise

如果要对某一个表做并发处理,就在该表中加一条Timestamp类型的字段。

The data type of the tracking column is typically rowversion. The rowversion value is a sequential number that's incremented each time the row is updated. In an Update or Delete command, the Where clause includes the original value of the tracking column (the original row version). If the row being updated has been changed by another user, the value in the rowversion column is different than the original value, so the Update or Delete statement can't find the row to update because of the Where clause. When the Entity Framework finds that no rows have been updated by the Update or Delete command (that is, when the number of affected rows is zero), it interprets that as a concurrency conflict.

SO.

// Note:
// You can only have one timestamp property in a given class.
// And the type of the property must be byte array.
[Timestamp] 
public byte[] RowVersion { get; set; }

ConcurrencyCheck to detect column-wise


[ConcurrencyCheck]
public string BloggerName { get; set; }

When SaveChanges is called, because of the ConcurrencyCheck annotation on the BloggerName field, the original value of that property will be used in the update. The command will attempt to locate the correct row by filtering not only on the key value but also on the original value of BloggerName. Here are the critical parts of the UPDATE command sent to the database, where you can see the command will update the row that has a PrimaryTrackingKey is 1 and a BloggerName of “Julie” which was the original value when that blog was retrieved from the database.


where (([PrimaryTrackingKey] = @4) and ([BloggerName] = @5)) 
@4=1,@5=N'Julie'

If someone has changed the blogger name for that blog in the meantime, this update will fail and you’ll get a DbUpdateConcurrencyException that you'll need to handle.

wcfNote: some says ConcurrencyCheck is not recommended. I think it is better if it can detect conflict column-wise, i.e. A changes columnX and B changes columnY, then their update can both be accepted and merged. TODO: I need to give this a try and update on it here later.

FAQ

Configure in app.config

MSDN.


public class BloggingContext : DbContext 
{ 
    public BloggingContext() 
        : base("BloggingCompactDatabase") 
    { 
    } 
}

And in app.config the "name" should be the same, here "BloggingCompactDatabase":


<configuration> 
  <connectionStrings> 
    <add name="BloggingCompactDatabase" 
         providerName="System.Data.SqlServerCe.4.0" 
         connectionString="Data Source=Blogging.sdf"/> 
  </connectionStrings> 
</configuration>

Exception: The ObjectContext instance has been disposed

When the browser can't parse the Nancy response which is supposed to be JSON, use the browser to pick up the string that is returned from Nancy.

Otherwise, manually Jsonize the object with Json.Net as follows. See MSDN: lazy, eagerly loading.


// Change the last line to (using NewtonSoft.Json):
return JsonConvert.SerializeObject(new Models.PatientAllModel { patient = patient, studies = studies, result = result.ToString() });

// Now Json.Net complains: "{"The ObjectContext instance has been disposed and can no longer be used for operations that require a connection."}"

// Look into my class definition:
[Serializable]
public class Patient : Base
{
    public Patient()
    {
        BirthDate = Defaults.datetime;
        Gender = Defaults.gender;
    }

    public enum GenderType {
        Male=0,
        Female=1,
        Unspecified=-1
    };

    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public long Id { get; set; }

    [Required]
    public string PatientId { get; set; } // Required.
    public string Name { get; set; }
    public GenderType Gender { get; set; }
    public string Cellphone { get; set; }
    public DateTime BirthDate { get; set; }

    public virtual Hospital Hospital { get; set; }
    public virtual WechatAccout WechatAccout { get; set; }
    public virtual List<Study> Studies { get; set; }
}

// To make the lazy-loading references loaded, use Include():
var patientQuery = from p in context.Patients.Include("Hospital").Include("WechatAccount")
   where p.PatientId == patientId
   select p;
var patient = patientQuery.First();

Exception: No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SqlClient'

SO.

Install EF for the console project. If you've added EF to a class library project, you also need to add it to the project that references it (your console app, website or whatever).


Install-Package EntityFramework

If you don't want to install again EF in console project:

Class Library application:
    Install Entity Framework
    Write your data layer code
    app.config file has all the configuration related to Entity Framework except for the connection string.
Create a Console, web or desktop application:
    Add a reference to the first project.
    Add a reference to EntityFramework.SqlServer.dll.
    app.config/web.config has the connection string (remember that the name of the configuration entry has to be the same as the name of the DbContext class.

Show SQL

SO: how do I view the SQL generated by EF.


using (MyDatabaseEntities context = new MyDatabaseEntities())
{
    // Spit out the generated SQL (along with additional execution-related details) in the Visual Studio output panel:
    context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
    // query the database using EF here.
}

Works on Entity Framework 6 and up.

Note: Make sure you are running your project in DEBUG mode.

Recreate Database

SO.

Follow below steps:

  1. First go to Server Explorer in Visual Studio, check if the ".mdf" Data Connections for this project are connected, if so, right click and delete.
  2. Go to Solution Explorer, click show All Files icon.
  3. Go to App_Data, right click and delete all ".mdf" files for this project. wcfNote: I didn't find this one.
  4. Delete Migrations folder by right click and delete.
  5. Go to SQL Server Management Studio, make sure the DB for this project is not there, otherwise delete it.
  6. Go to Package Manager Console in Visual Studio and type "Enable-Migrations" etc.
  7. Run your application.