Show Menu
Cheatography
  1. Home >
  2. Cheat Sheets >

Entity Framework Cheat Sheet (DRAFT) by

Entity Framework

This is a draft cheat sheet. It is a work in progress and is not finished yet.

Main Classes (1 per entity)

public class Student
{
    public Student() { }
    public int StudentID { get; set; }
    public string StudentName { get; set; }
    public DateTime? DateOfBirth { get; set; }
    public byte[]  Photo { get; set; }
    public decimal Height { get; set; }
    public float Weight { get; set; }
        
    public int StandardId { get; set; } // foreign key for line below (paired)
    public Standard Standard { get; set; }  // 1 per student
}

public class Standard {
    public Standard() { }
    public int StandardId { get; set; }
    public string StandardName { get; set; }
    
    public ICollection<Student> Students { get; set; }  // many per standard
}
If you dont define the foreign key field name <cl­ass­>Id in the Student class, it will be created automa­tically and called Standa­rd_­Sta­ndardId

DbContext Class (1 required)

public class Context: DbContext 
{
    // SchoolDbConnectionString is the connection string from the config file
    public SchoolContext(): base("name=SchoolDbConnectionString")
    {
            //Disable initializer - we dont want to lose data EVER
            Database.SetInitializer<SchoolDBContext>(null);
     }
            
    public DbSet<Student> Students { get; set; }
    public DbSet<Standard> Standards { get; set; }
}

Conven­tions

Primary key is Id or <class name>Id (or use Data Annota­tion)
Every Entity MUST have a primary key
Foreign keys are created as IList<­T> or IColle­cti­on<­T>

Data Annota­tions

[Key]
Make this into a primary key
[Key] [Colum­n(O­rde­r=1)]
First part of composite primary key
[Key] [Colum­n(O­rde­r=2)]
Second part of composite primary key
[TimeS­tamp] public byte[] RowVersion { get; set; }
Used for concur­rency checking. Only works for byte[]. Autofills
[Concu­rre­ncy­Check]
Use as a concur­rency check. Any type. No autofill
[Required]
Required value. Forces NOT NULL
[MaxLe­ngt­h(50)]
Maximum of 50 characters
[MinLe­ngt­h(2)]
Minimum of 2 characters
[MaxLe­ngt­h(5­0),­Min­Len­gth(2)]
Min and Max length combined
[Strin­gLe­ngt­h(50)]
Make nvarch­ar(50) instead of nvarch­ar(max)
[Colum­n("N­ame­")]
Use this as field name in the DB instead of the property name
[Colum­n("N­ame­", Typena­me=­"­var­cha­r")]
Set the fieldname and the data type
[NotMa­pped]
Dont create a field in the database (unbound data)
[Forei­gnK­ey(­"­Spe­cif­icI­dFi­eld­")]
Use the specified id field to hold the foreign key value
[Index]
Create a non clustered index on thsi field
[Index( "­IND­EX_­REG­NUM­", IsClus­ter­ed=­true, IsUniq­ue=true )]
Create a clustered, unique index with the given name (instead of IX_pro­per­tyname)
 
[Table­("St­ude­ntM­ast­er")]
Use this as the table name instead of the class name
The "­Tab­le" annotation goes just before the public class line. All other annota­tions go before the properties themselves

DbEnti­tyEntry

var entry = contex­t.E­ntr­y(s­tudent)
Get a DbEnti­tyEntry for the current student
entry.S­tate
Return Modified, Deleted, Added, Unchanged or Detached
entry.O­ri­gin­alV­alu­es[­"­age­"]
The original (uncha­nged) value
entry.C­ur­ren­tVa­lue­s["a­ge"]
The current value
contex­t.E­ntr­y(s­tud­ent­).State = System.Da­ta.E­nt­ity.En­tit­ySt­ate.Mo­dified;
Force to a modified state (even if it hasnt been) Needed for discon­nected entities
entry.R­el­oad();
Forces the data to be reloaded from the database (state will become UnChanged) All changes will be lost
Note : The context will have been created with:
using (var context = new School­DBE­nti­ties()) { }

Add Entity (in discon­nected state)

// create new Student entity object in disconnected scenario (out of the scope of DbContext)
var newStudent = new Student();

//set student name
newStudent.StudentName = "Bill";

//create DBContext object
using (var dbCtx = new SchoolDBEntities()) {
    //Add Student object into Students DBset
    dbCtx.Students.Add(newStudent);
                
    // call SaveChanges method to save student into database
    dbCtx.SaveChanges();
}

Update Entity (in discon­nected state)

//1. Get student from DB
using (var ctx = new SchoolDBEntities())
{
    stud = ctx.Students.Where(s => s.StudentName == "New Student1").FirstOrDefault<Student>();
}

//2. change student name in disconnected mode (out of ctx scope)
if (stud != null)
{
    stud.StudentName = "Updated Student1";
}

//save modified entity using new Context
using (var dbCtx = new SchoolDBEntities())
{
    //3. Mark entity as modified
    dbCtx.Entry(stud).State = System.Data.Entity.EntityState.Modified;     
        
    //4. call SaveChanges
    dbCtx.SaveChanges();
}

Delete Entity (in discon­nected state)

//1. Get student from DB
using (var ctx = new SchoolDBEntities())
{
    studentToDelete = ctx.Students.Where(s => s.StudentName == "Student1").FirstOrDefault<Student>();
}

//Create new context for disconnected scenario
using (var newContext = new SchoolDBEntities())
{
    newContext.Entry(studentToDelete).State = System.Data.Entity.EntityState.Deleted;    

    newContext.SaveChanges();
}

Update Entity Graph using DbContext

TBA !!!

Complex - needs more research at this stage

Raw SQL

using (var ctx = new SchoolDBEntities())
{
    //Update command
    int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update student 
            set studentname ='changed student by command' where studentid=1");
    //Insert command
    int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(studentname) 
            values('New Student')");
    //Delete command
    int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student 
            where studentid=1");
}

Convert DbContext to Object­Context

using (var ctx = new SchoolDBEntities()) {
    var objectContext = (ctx as System.Data.Entity.Infrastructure.IObjectContextAdapter).ObjectContext;
    //use objectContext here..
}

Queries

// Get a record by its Primary key value - return null if no record found
using (var ctx = new SchoolDBEntities())
{    
    var student = ctx.Students.Find(_id);
}

// Get the first (TOP 1) record - return null if no record found
using (var ctx = new SchoolDBEntities())
{    
    var student = (from s in ctx.Students
                where s.StudentName == "Student1"
                select s).FirstOrDefault<Student>();
}

// Get a List of records that match the criteria
using (var ctx = new SchoolDBEntities())
{    
    var studentList = (from s in ctx.Students
        where s.StudentName == "Student1"
        orderby s.StudentName ascending
        select s).ToList<Student>();
}

Other Notes

How to set default values
Create a new partial class (don't edit the autoge­nerated one) and set the default values in the constr­uctor of the new class.