static void

Entity Framework Database/Model First

See EF Code First

Links

General

Add New Item - ADO.NET Entity Data Model

Uses a EDM (Entity Data Model) comprising conceptional (csdl), store (ssdl) and mapping (msl) xml.

ObjectContext

ObjectContext is the equivalent of Linq2Sql DataContext/ NHibernate Session. The default generated class ends in "Entities" (e.g. NorthwindEntities). The collections are ObjectSet<Entity> (NB: fk collections on an entity are EntityCollections).

Connection strings use the provider System.Data.EntityClient with res://*/model.csdl values (res://*/ wildcard uses the calling assembly & bin dlls; otherwise specify the assembly fullname including public key etc). The actual ADO connection string is embedded as a value inside the metadata.

Mapping

You can code functions into the EDMX xml (in edmx:ConceptualModels/Schema - Function /Parameter+DefiningExpression) and a class method with a [EdmFunctionAttribute] (which is not implemented - throw an exception). When the function is called in a Linq query, it's transformed into sql and executed server side.

Complex Types

Eg an Address object on a Person/Company etc (no inheritance. no navigation). Select the properties and "Refactor Into New Complex Type" or in Model Browser "Create New Complex Type". Complex type properties can't be null (SaveChanges() throws an InvalidOperationException)

Inheritance

Entities

There are many sorts:

Lazy and Eager Loading

Lazy loading is enabled on EDMX level.

var orders = context.Orders
    .Include("Order_Details")
    .Where("it.OrderDate > @startDate", new ObjectParameter("startDate", startDate));
 

Or load a fk collection: order.Order_Details.Load();

Object Queries

NHibernate has a confusing range of query methods (HQL, Criteria, QueryOver, Query/Linq). Entity Framework has linq to entities, Query builder and Entity SQL.

The ObjectContext collection properties are ObjectSet<Entity> (extends ObjectQuery) which allows you to build Object Queries (you can use IQueryable as in Linq2Sql). ObjectQueries have standard methods including Where, Select, OrderBy, GroupBy etc - note that standard Linq versions may throw exceptions (eg using a IComparer in OrderBy) unless you turn into client side objects (AsEnumerable) first.

//Option 1: IQueryable works but you lose the ObjectSet
var orders = context.Orders
    .Where(x => x.OrderDate > startDate);
 
//Option 2: Chaining the ObjectSet
var orders2 = context.Orders
    //entitySql clause as string with @prefixed parameter.
    //orders2.Parameters is merged, so be wary of name collisions when building queries
    .Where("it.OrderDate > @startDate", new ObjectParameter("startDate", startDate));
//default alias: orders2.Name == "it";
//with ObjectSet you get a few other nice things
var sql = orders2.ToTraceString(); //tracing
var results = orders2.Execute(MergeOption.AppendOnly); //execute immediately

Entity SQL

You can use an EntityCommand with EntitySQL(ESQL- EF's version of HQL).

//reference a defined connection string with name = x
using (var conn = new EntityConnection("name = NorthwindEntities"))
{
    conn.Open();
    const string esql = "SELECT c.CategoryId, c.CategoryName FROM NorthwindEntities.Categories AS c";
    using (var cmd = new EntityCommand(esql, conn))
    {
        //When getting a stream of rows, include CommandBehavior.SequentialAccess.
        var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
        while (reader.Read())
        {
            var id = reader.GetInt32(0);
            var name = reader.GetString(1);
            list.Add(id, name);
        }
    }
}
//or
using (var context = new ObjectContext("name = NorthwindEntities"))
{
    context.Connection.Open();
    const string esql = "NorthwindEntities.Categories";
    var result = new ObjectQuery<Category>(esql, context);
    var first = result.First();
    var id = first.CategoryID;
    var name = first.CategoryName;
}

Or use an ObjectContext with an ObjectQuery

using (var context = new ObjectContext("name = NorthwindEntities"))
{
    context.Connection.Open();
    const string esql = "SELECT c.CategoryId, c.CategoryName FROM NorthwindEntities.Categories AS c";
    var result = new ObjectQuery<DbDataRecord>(esql, context);
    var first = result.First();
    var id = first.GetInt32(0);
    var name = first.GetString(1);
}

For complex properties, the DbDataRecord field is actually a DbDataRecord itself.

ESQL Explanation
SELECT p.Cost
FROM NorthwindEntities.Products AS p		 Always use aliases (or the implicitly use the table name)
There's no SELECT p.* either.
SELECT ROW(p.Cost AS ProdCost, p.Name AS ProdName)
FROM NorthwindEntities.Products AS p		 Create collection of anonymous types (ProdCost, ProdName).
SELECT VALUE p
FROM NorthwindEntities.Products AS p
[WHERE...]		 Selects an entity (or primitive) value into the result collection.
(SQL Select * from Products returns a DbRow of columns; "SELECT VALUE" returns an ICollection<T>).
You can only "SELECT VALUE" one thing. (SELECT VALUE AVG(p.Cost) FROM...)
SELECT VALUE CAST(p.Cost AS Edm.Int32)
FROM NorthwindEntities.Products AS p		 Casting to different types
SELECT VALUE Edm.Count(0)
FROM NorthwindEntities.Products AS p		 There are various cross platform canonical functions such as AVG, Count, Sum, CurrentDateTime(), Contains, EndsWith, Length, Replace, Trim, ToUpper...
SELECT VALUE p
FROM NorthwindEntities.Products AS p
ORDER BY o.Cost SKIP 20 LIMIT 10		 Paging- ORDER BY x SKIP y LIMIT z
SELECT REF(p)
FROM NorthwindEntities.Products AS p		 Gets an EnityKey (just the key) instead of the full object. You can DEREF() to go back to the full object.

Native SQL

MSDN

Updates

See here

var context = new NorthwindEntities();

var choc = new Category { CategoryName = "Chocolate", Description = "Choco" };
//choc.EntityState == EntityState.Detached
context.Categories.AddObject(choc);
//choc.EntityKey.IsTemporary == true
//choc.EntityState == EntityState.Added

context.SaveChanges();
//choc.EntityKey.IsTemporary == false
//choc.EntityState == EntityState.Unchanged

choc.Description = "Chocolate related products";
//choc.EntityState == EntityState.Modified
context.SaveChanges();

context.Categories.DeleteObject(choc);
//choc.EntityState == EntityState.Deleted
context.SaveChanges();
//choc.EntityState == EntityState.Detached

You can find the status of an object via context.ObjectStateManager.GetObjectStateEntry(entity).State. Non-proxied POCOs may not have state so there's a ObjectStateManager.TryGetObjectStateEntry - you can call context.DetectChanges() (context.SaveChanges() does it automatically) for snapshot checking.

Concurrency

Use context.Refresh(RefreshMode.ClientWins, entity) (or StoreWins) to update. You can use this when trapping a SaveChanges() which throws an OptimisticConcurrencyException.

Cascading Deletes

Set the entity EDMX Delete property to Cascade. NOTE: You must .Load or .Include the foreign keys before the DeleteObject(entity) (and sub-dependencies!)

Disconnected entities

You can context.Detach(entity) and attach as follows. The attached entity has status Unchanged

For POCOs, call context.DetectChanges().

To copy scalar properties context[.ObjectSet].ApplyCurrentValues(detached)

Self-tracking entities

It's a T4 item template, intended for disconnected entities (WCF).

They start tracking automatically when deserialized from WCF, or connected to another tracking entity, or StartTracking() is called (also MarkAsAdded() etc). Call StopTracking() to stop.

When receiving the object with changes, add it with the extension method context.Orders.ApplyChanges(order)

Stored procedures

MSDN / MSDN.

In your ObjectContext, a sproc in the conceptual model can be called by context.ExecuteFunction("name", pars). To add it to the generated context, from Model Browser/ "Function Imports" - "Add Function Import" (you can map to existing entities).

Entity updates can be done by sprocs (but you must have insert, update and delete) - in designer, click entity and Stored Procedure Mapping.