AngularJS and Breeze – A simple crud app – Part 2 – Adding create, delete, reset and is dirty checking.

 

In my previous post I created a simple [AngularJS – Breeze] edit view. In this post I will add the create, delete, reset and “is dirty” (entity change state tracking) to the simple view.

 

Before create

image

 

Create

The user clicked on the create button, so an “is dirty” icon is shown and a new row is added to the grid.

image

 

Save

When the user fills the fields of the new row and clicks save, the “is dirty” icon will disappear and the id field will automatically be filled by the id generated on the server in the database.

 

image

 

Reset

When the user create, updated and deleted some records and pressed save, the original state of the database can be restored by pressing the reset button.

image

 

 

index.html

<!DOCTYPE html>
<html data-ng-app="app">
<head>
<title data-ng-bind="title">Angular and Breeze</title>
<meta charset="utf-8" />
<meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, minimum-scale=1, user-scalable=no" />
<!-- Libraries -->
<link rel="stylesheet" type="text/css" href="../../../Libraries/FontAwesome/css/font-awesome.min.css" />
<link rel="stylesheet" type="text/css" href="../../../Libraries/Toastr/toastr.min.css" />

<!-- App -->
<link rel="stylesheet" type="text/css" href="app.css" />
</head>
<body>
<div class="spa-page" data-ng-controller="admin as vm">
<div class="spa-grid-toolbar">
<a class="spa-action-link" ng-click="vm.save()">save</a> |
<a class="spa-action-link" ng-click="vm.reset()">reset</a> |
<a class="spa-action-link" ng-click="vm.create()">create</a>
<i class="fa fa-exclamation-circle" 
title="Some data has change. Press save to save the changes to the server!" 
ng-show="vm.isDirty"></i>
</div>
<table class="spa-grid">
<thead>
<tr>
<th> </th>
<th ng-repeat='(key, prop) in vm.entityFields'>{{ prop.name }}</th>
</tr>
</thead>
<tbody>
<tr ng-repeat="entity in vm.entities">
<td><a class="spa-action-link" ng-click="vm.delete(entity)">delete</a></td>
<td ng-repeat='(key, prop) in vm.entityFields'>
<input ng-disabled="{{vm.isReadOnlyField(prop.name)}}" 
type='text' 
ng-model='entity[prop.name]'>
</td>
</tr>
</tbody>
</table>
</div>
<!-- Libraries -->
<script type="text/javascript" src="../../../Libraries/Angular/angular.js"></script>
<script type="text/javascript" src="../../../Libraries/Breeze/breeze.debug.js"></script>
<script type="text/javascript" src="../../../Libraries/Breeze/breeze.angular.js"></script>
<!-- Add toastr which needs jQuery (Breeze does not need jQuery) -->
<script type="text/javascript" src="../../../Libraries/jQuery/jquery-2.1.1.js"></script>
<script type="text/javascript" src="../../../Libraries/Toastr/toastr.js"></script>

<!-- Add breeze.savequeuing which needs Q (Breeze does not need Q)--> <script type="text/javascript" src="../../../Libraries/Q/q.min.js"></script> <script type="text/javascript" src="../../../Libraries/Breeze/breeze.savequeuing.js"></script> <!-- App --> <script type="text/javascript" src="app.js"></script> </body> </html>

 

 

app.js

// Use namespaces to prevent pollution of the global namespace.
var spa = spa || {};
spa.controllers = spa.controllers || {};
// Angular module [app].
spa.app = (function () {
'use strict';
var app = angular.module('app', [
'breeze.angular' // The breeze service module.
]);
})();
// Angular controller [admin].
spa.controllers.admin = (function () {
'use strict';
var controllerId = 'admin';
angular.module('app').controller(controllerId, ['$http', 'breeze', admin]);
function admin($http, breeze) {
var entityChangedToken = null;
var entityTypeName = "Employee";
var manager = null;
var vm = this;
vm.create = function () {
// Create entity by breeze.
var entity = manager.createEntity(entityTypeName);
// Show entity to user.
vm.entities.push(entity);
};
vm.delete = function (entity) {
// Delete from UI
vm.entities.pop(entity);
// Mark for deletion.
entity.entityAspect.setDeleted();
};
vm.entities = [];
vm.entityFields = null;
vm.isDirty = false;
vm.isReadOnlyField = function (name) {
// Make 'id' fields read-only.
return (name === 'id');
};
vm.reset = function () {
// Re-seed database and refetch data.
$http.get('/breeze/breeze/ReSeed').then(getData).then(handleResetResult).catch(showError);
};
vm.save = function () {
manager.saveChanges().then(handleSaveResult).catch(showError);
};
function handleStateChange(args) {
vm.isDirty = true;
}
function getData() {
// Get entities from the server.
var query = new breeze.EntityQuery().from(entityTypeName);
manager.executeQuery(query).then(handleGetDataResult).catch(showError);
}
function initialize() {
// Use camel case for entity properties.
breeze.NamingConvention.camelCase.setAsDefault();
// Configure and create EntityManager (double breeze is needed, because of .
manager = new breeze.EntityManager('/breeze/breeze');
manager.enableSaveQueuing(true);
registerForStateChange();
getData();
}
function handleGetDataResult(data) {
// Get entity fields from metadata.
var entityMetaData = manager.metadataStore.getEntityType(entityTypeName);
vm.entityFields = entityMetaData.dataProperties;
// Show the enties from the server.
vm.entities = data.results;
vm.isDirty = false;
}
function handleResetResult() {
vm.isDirty = false;
toastr.info("Database re-seeded.");
}
function handleSaveResult() {
vm.isDirty = false;
toastr.info("Changes saved to the server.");
}
function registerForStateChange() {
// Make sure to only subscribe once.
if (entityChangedToken) { return; }
// Register for state change.
entityChangedToken = manager.entityChanged.subscribe(handleStateChange);
}
function showError(e) {
// Show xhr error.
toastr.error(e);
}
initialize();
}
})();

 

app.css

/* Resets */
html, body, div, span, object, iframe, h1, h2, h3, h4, h5, h6, p, blockquote, pre,abbr, address, cite, code,
del, dfn, em, img, ins, kbd, q, samp, small, strong, sub, sup, var, b, i, dl, dt, dd, ol, ul, li, fieldset,
form, label, legend, table, caption, tbody, tfoot, thead, tr, th, td, article, aside, canvas, details, 
figcaption, figure, footer, header, hgroup, menu, nav, section, summary, time, mark, audio, video {
border: 0;              /* Prevent unnecessary white space. */
-moz-box-sizing: border-box;
-webkit-box-sizing: border-box;
box-sizing: border-box; /*  Border boxing is used, so the padding, margin and borders are within 
the width and height of the element. */
margin: 0;              /* Prevent unnecessary white space. */
outline: 0;             /* Prevent unnecessary white space. */
padding: 0;             /* Prevent unnecessary white space. */
font-size: 100%;
vertical-align: baseline;
}
html, body {
height: 100%;       /* Full screen single page app. */
max-height: 100%;   /* Full screen single page app. */
}
body {
padding: 20px;
}
a {
cursor: pointer;
}
a.spa-action-link {
color: #428bca;
text-decoration: none;
}
a.spa-action-link:hover, a.spa-action-link:focus {
color: #2a6496;
text-decoration: underline;
}
div.spa-page {
border: 1px solid rgb(212, 212, 212);
height: 100%;       /* Full screen single page app. */
max-height: 100%;   /* Full screen single page app. */
padding: 20px;
position: relative;
}
div.spa-page > a {
margin-bottom: 10px;
}
div.spa-grid-toolbar i.fa-exclamation-circle {
margin-left: 10px;
}
.spa-grid input[type="text"] {
padding-left: 2px;
}

 

BreezeController.cs

namespace Research.UI.Web.Server.Controllers
{
using Breeze.ContextProvider;
using Breeze.ContextProvider.EF6;
using Breeze.WebApi2;
using Newtonsoft.Json.Linq;
using Research.UI.Web.Server.Model;
using System.Data.Entity.Migrations;
using System.Linq;
using System.Web.Http;
[BreezeController]
public class BreezeController : ApiController
{
private readonly EFContextProvider<ResearchDbContext> _contextProvider;
public BreezeController(): this(null)
{
}
public BreezeController(EFContextProvider<ResearchDbContext> contextProvider)
{
_contextProvider = contextProvider ??  new EFContextProvider<ResearchDbContext>();
}
[HttpGet]
public IQueryable<Employee> Employee()
{
return _contextProvider.Context.Employees;
}
[HttpGet]
public string Metadata()
{
string result = _contextProvider.Metadata();
return result;
}
[HttpGet]
public void ReSeed()
{
// Remove all records from the "Employees" table.
_contextProvider.Context.Database.ExecuteSqlCommand("truncate table Employees");
// Run an "Update-Database" EF migrations command, this will update the database schema 
// to the latest state and run the Seed() method.
var configuration = new Research.UI.Web.Migrations.Configuration(); configuration.ContextType = typeof(ResearchDbContext); var migrator = new DbMigrator(configuration); migrator.Update(); } [HttpPost] public SaveResult SaveChanges(JObject saveBundle) { return _contextProvider.SaveChanges(saveBundle); } } }

 

 

For the complete code, see:

https://github.com/roelvanlisdonk/Research/tree/master/Research/Research.UI.Web/Client/Features/AngularJS_and_Breeze/Part2

How to make Entity Framework 5.0 use an in-memory persistence DbContext instead of accessing the database.

If you want to switch between in-memory stub data and a database during runtime with Entity Framework 5.0, you have several options, some of them are:

– Using a second level cache mechanism like http://www.codeproject.com/Articles/435142/Entity-Framework-Second-Level-Caching-with-DbConte , filling the cache before use and setting the expiration time to infinite.

– Creating a extension method on the DbSet class that uses only the DbSet (for direct database access) or DbSet.Local for in-memory stub data, based on some parameter.

– Implement a MemoryPersistenceDbContext and MemoryPersistenceDbSet.

 

This post will focus on the last option.

Create a new MVC4 project in Microsoft Visual Studio 2010

File > New > Project

image

 

image

image

 

Add Entity Framework 5.0 NuGet package

Rightclick solution > Manage NuGet Packages for Solution…

image

image

image

image

 

I added 2 tables to a Research database on a LocalDb SQL Server 2012 instance

image

Add *.edmx model

Right click on the models folder:

image

 

image

ModelName = ResearchModel.edmx

image

DbContext name = ResearchUow

(UOW = Unit of work)

image

image

Model namespace = ResearchModel

Check all tables

image

Add code generation item

Open the ResearchUow.edmx > right click > Add Code Generation Item…

image

Code generation item name = ResearchModel.tt

image

Now the project looks like

image

 

Add an IEntity interface

This interface will be used to make the Find function work.

image

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace Mvc4Application.Models
{
public interface IEntity
{
int Id { get; }
}
}

Add a MemoryPersistenceDbSet.cs in the Models folder

This will be used to store the data in-memory instead of in the database.

image

using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
namespace Mvc4Application.Models
{
public class MemoryPersistenceDbSet<T> : IDbSet<T> where T : class
{
ObservableCollection<T> _data;
IQueryable _query;
public MemoryPersistenceDbSet()
{
_data = new ObservableCollection<T>();
_query = _data.AsQueryable();
}
public virtual T Find(params object[] keyValues)
{
if (!(typeof(T) is IEntity)) { throw new ArgumentException(string.Format("Entity [{0}] does not contain a property [Id], so it could not be converted to the IEntity interface, used in this function.", typeof(T).ToString())); }
return this.SingleOrDefault(e => (e as IEntity).Id == (int)keyValues.Single());
}
public T Add(T item)
{
_data.Add(item);
return item;
}
public T Remove(T item)
{
_data.Remove(item);
return item;
}
public T Attach(T item)
{
_data.Add(item);
return item;
}
public T Detach(T item)
{
_data.Remove(item);
return item;
}
public T Create()
{
return Activator.CreateInstance<T>();
}
public TDerivedEntity Create<TDerivedEntity>() where TDerivedEntity : class, T
{
return Activator.CreateInstance<TDerivedEntity>();
}
public ObservableCollection<T> Local
{
get { return _data; }
}
Type IQueryable.ElementType
{
get { return _query.ElementType; }
}
Expression IQueryable.Expression
{
get { return _query.Expression; }
}
IQueryProvider IQueryable.Provider
{
get { return _query.Provider; }
}
IEnumerator IEnumerable.GetEnumerator()
{
return _data.GetEnumerator();
}
IEnumerator<T> IEnumerable<T>.GetEnumerator()
{
return _data.GetEnumerator();
}
}
}

Change the ResearchModel.tt file, so all generated POCO entities derive from IEntity

Open the ResearchModel.tt files and change the line

<#=codeStringGenerator.EntityClassOpening(entity)#>

to

<#=codeStringGenerator.EntityClassOpening(entity)#> : IEntity

and click save, on save of the ResearchModel.tt file, the POCO entities will be regenerated and will now all implement the IEntity interface:

image

 

Change the ResearchModel.Context.tt file, so the IResearchUow interface, the ResearchUow class and the MemoryPersistenceResearchUow will be created.

 

Open the ResearchModel.Context.tt file and find the code:


<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext

{

    public <#=code.Escape(container)#>()

        : base("name=<#=container.Name#>")

    {

<#

if (!loader.IsLazyLoadingEnabled(container))

{

#>

        this.Configuration.LazyLoadingEnabled = false;

<#

}

#>

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)

    {

        throw new UnintentionalCodeFirstException();

    }

<#

    foreach (var entitySet in container.BaseEntitySets.OfType<EntitySet>())

    {

#>

    <#=codeStringGenerator.DbSet(entitySet)#>

<#

    }

    foreach (var edmFunction in container.FunctionImports)

    {

        WriteFunctionImport(typeMapper, codeStringGenerator, edmFunction, modelNamespace, includeMergeOption: false);

    }

#>

}

<#

if (!String.IsNullOrEmpty(codeNamespace))

{

    PopIndent();

#>

}

<#

}

#>

 

 

Replace by

 


<#=Accessibility.ForType(container)#> partial interface IResearchlUow

{

<#

    foreach (var entitySet in container.BaseEntitySets.OfType<EntitySet>())

    {

#>

    <#=codeStringGenerator.IDbSet(entitySet)#>

<#

    }

    foreach (var edmFunction in container.FunctionImports)

    {

        WriteFunctionImport(typeMapper, codeStringGenerator, edmFunction, modelNamespace, includeMergeOption: false);

    }

#>

    int SaveChanges();

}

<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext, IResearchUow

{

<#

    foreach (var entitySet in container.BaseEntitySets.OfType<EntitySet>())

    {

#>

    <#=codeStringGenerator.DbSet(entitySet)#>

<#

    }

    foreach (var edmFunction in container.FunctionImports)

    {

        WriteFunctionImport(typeMapper, codeStringGenerator, edmFunction, modelNamespace, includeMergeOption: false);

    }

#>

    public <#=code.Escape(container)#>() : base("name=<#=container.Name#>")

    {

<#

if (!loader.IsLazyLoadingEnabled(container))

{

#>

        this.Configuration.LazyLoadingEnabled = false;

<#

}

#>

    }

    public <#=code.Escape(container)#>(string connection) : base(connection)

    {

<#

if (!loader.IsLazyLoadingEnabled(container))

{

#>

        this.Configuration.LazyLoadingEnabled = false;

<#

}

#>

    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)

    {

        throw new UnintentionalCodeFirstException();

    }

}

<#=Accessibility.ForType(container)#> partial class MemoryPersistenceResearchUow : <#=code.Escape(container)#>

{

    public MemoryPersistenceResearchUow()

    {

        Seed();

    }

    public void ClearAll()

    {

<#

    foreach (var entitySet in container.BaseEntitySets.OfType<EntitySet>())

    {

#>

        <#=codeStringGenerator.DbSetInConstructor(entitySet)#>

<#

    }

#>

    }

    public override int SaveChanges()

    {

        return 0;

    }

}

<#

if (!String.IsNullOrEmpty(codeNamespace))

{

    PopIndent();

#>

}

<#

}

#>

 

and find

public string DbSet(EntitySet entitySet)

{

    return string.Format(

        CultureInfo.InvariantCulture,

        "{0} DbSet<{1}> {2} {{ get; set; }}",

        Accessibility.ForReadOnlyProperty(entitySet),

        _typeMapper.GetTypeName(entitySet.ElementType),

        _code.Escape(entitySet));

}

 

Replace by

    public string IDbSet(EntitySet entitySet)

    {

        return string.Format(

            CultureInfo.InvariantCulture,

            "IDbSet<{0}> {1} {{ get; set; }}",

            _typeMapper.GetTypeName(entitySet.ElementType),

            _code.Escape(entitySet));

    }

    public string DbSet(EntitySet entitySet)

    {

        return string.Format(

            CultureInfo.InvariantCulture,

            "{0} IDbSet<{1}> {2} {{ get; set; }}",

            Accessibility.ForReadOnlyProperty(entitySet),

            _typeMapper.GetTypeName(entitySet.ElementType),

            _code.Escape(entitySet));

    }

    public string DbSetInConstructor(EntitySet entitySet)

    {

        return string.Format(

            CultureInfo.InvariantCulture,

            "this.{0} = new MemoryPersistenceDbSet<{0}>();",

            _typeMapper.GetTypeName(entitySet.ElementType));

    }

 

Now on save of the ResearchModel.Context.tt  T4 template will generate the following code:

namespace Mvc4Application.Models
{
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
public partial interface IResearchUow
{
IDbSet<Car> Car { get; set; }
IDbSet<Person> Person { get; set; }
int SaveChanges();
}
public partial class ResearchUow : DbContext, IResearchUow
{
public IDbSet<Car> Car { get; set; }
public IDbSet<Person> Person { get; set; }
public ResearchUow() : base("name=ResearchUow")
{
}
public ResearchUow(string connection) : base(connection)
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
}
public partial class MemoryPersistenceResearchUow : ResearchUow
{
public MemoryPersistenceResearchUow()
{
Seed();
}
public void ClearAll()
{
this.Car = new MemoryPersistenceDbSet<Car>();
this.Person = new MemoryPersistenceDbSet<Person>();
}
public override int SaveChanges()
{
return 0;
}
}
}

 

Add a partial class file for the MemoryPersistenceResearchUow

Every time you update the ReserachModel.edmx from the database or save the T4 templates ResearchModel.tt and ResearchModel.Context.tt, the T4 templates will execute and regenerate all POCO entities and the IResearchUow interface, ResearchUow class and the MemoryPersistenceResearchUow.  To prevent the code that seeds the in-memory UOW to be overwritten a partial class MemoryPersistenceResearchUow is created.

image

public partial class MemoryPersistenceResearchUow
{
public void Seed()
{
ClearAll();
// TODO Add seed logic here, like.....
this.Person.Add(new Person { Id = 1, Name = "Roel van Lisdonk" });
this.Car.Add(new Car { Id = 1, NumberPlate = "8-KJA-00", PersonId = 1 });
}
}

Add a IResearchUowFactory and ResearchUowFactory that will contain the logic to create a ResearchUow or an MemoryPersistenceResearchUow.

namespace Mvc4Application.Models
{
public interface IResearchUowFactory
{
IResearchUow GetResearchUow();
}
}
using System;
using System.Web.Configuration;
namespace Mvc4Application.Models
{
public class ResearchUowFactory : IResearchUowFactory
{
public IResearchUow GetResearchUow()
{
string key = "UseStubs";
string result = WebConfigurationManager.AppSettings[key];
if (result == null) { throw new ApplicationException(string.Format("Could not find AppSetting[{0}].", key)); }
bool useStubs = bool.Parse(result);
return useStubs ? new MemoryPersistenceResearchUow() : new ResearchUow();
}
}
}

 

The project will no look like:

image

 

Add appSetting "UseStubs" to the Web.config

  <appSettings>
<add key="UseStubs" value="true" />

Install an IoC container by using NuGet, in this case I will use ninject:

Install Ninject and Ninject.MVC3 (no MVC4 available yet, but works just fine) this will also install Ninject.Web.Common.

image

In the App_Start folder change the NinjectWebCommon.cs

Fill the RegisterService function:

/// <summary>
/// Load your modules or register your services here!
/// </summary>
/// <param name="kernel">The kernel.</param>
private static void RegisterServices(IKernel kernel)
{
kernel.Bind<IResearchUowFactory>().To<ResearchUowFactory>();
} 

In the Controllers folder change the HomeControler, add:

private readonly IResearchUowFactory _researchUowFactory;
private readonly IResearchUow _researchUow;
public HomeController(IResearchUowFactory researchUowFactory)
{
_researchUowFactory = researchUowFactory;
_researchUow = _researchUowFactory.GetResearchUow();
}
public ActionResult Index()
{
Person firstPerson = _researchUow.Person.First();
ViewBag.Message = string.Format("First person name [{0}].", firstPerson.Name);
return View();
}

Result

This results in:

image

 

The text "First person name [Roel van Lisdonk]" is shown. This was the data from the seed method:

this.Person.Add(new Person { Id = 1, Name = "Roel van Lisdonk" });

and not from the real database, because the database at this point is empty.

This proves we can switch using in-memory stub data or the real database by changing a appSetting in the web.config.

 

 

 

Don’t forget to fix the unit tests.

In the unit tests for the HomeController, change the lines:

HomeController controller = new HomeController();

to

HomeController controller = new HomeController(new ResearchUowFactory());

 

 

Now you are able to use the ResearchUow within your HomeController and switch between the MemoryPersistenceResearchUow and the ResearchUow by changing the appSetting UseStubs.

How to update an entity in the database, when state tracking is disabled in Entity Framework 4.3

When you disable state tracking in Entity Framework 4.3, for performance improvements (and you know what you’re doing Winking smile), by setting: DbContext.Configuration.AutoDetectChangesEnabled = false , then you are responsible for your own state tracking on entities.

 

The process of updating an entity, involves 3 steps:

1. Setting the EntityState to Modified.

2. Save changes to database.

3. Setting the EntityState to Unchanged.

 

Code

// Set entity to modified, so the entity will be updated in the database.
_dbContext.Entry(imageInfo).State = System.Data.EntityState.Modified;
// Save changes to database.
_dbContext.SaveChanges();
// Set entity to unchanged, so the entity will be not be updated in future calls to _dbContext.SaveChanges().
_dbContext.Entry(imageInfo).State = System.Data.EntityState.Unchanged;    

Notes

1. Where _dbConext is an instance of the DbContext class.

2. If you don’t set the Entity.State to Modified, no changes will be persisted to the database.

3. If you don’t set the Entity.State to Unchanged after SaveChanges(), future calls to SaveChanges() will repeat the update for this entity.

How to use SaveOptions.None with DbContext in EF 4.2

If you want to use the SaveOptions.None, when calling the method DbContext.SaveChanges, you must override de function in a partial class, like:

 

/// <summary> /// </summary> public partial class MyEntities : IMyEntities { /// <summary> /// Dynamically set connection string and configure Entity Framework. /// </summary> /// <param name="connection"></param> public Myntities(string connection) : base(connection) { // Set Configuration.AutoDetectChangesEnabled = false to improve performance (100x). Configuration.AutoDetectChangesEnabled = false; // By default use lazyloading. Configuration.LazyLoadingEnabled = true; // Don't valid entities on save. Configuration.ValidateOnSaveEnabled = false; }

/// <summary> /// Override the default save changes so, because we have disabled "AutoDetectChangesEnabled". /// </summary> /// <returns></returns> public override int SaveChanges() { return (this as IObjectContextAdapter).ObjectContext.SaveChanges(System.Data.Objects.SaveOptions.None); }

 

}

How to update a entity in EF 4.2, when using Configuration.AutoDetectChangesEnabled = false.

 

When you use DbContext.Configuration.AutoDetectChangesEnabled = false to improve performance, then there will be no automatically state tracking (this is what’s causing the performance penalty). To update a entity in the database, including it’s releated entities (navgiation properties), you should use the Entry function on the DbContext:

using System.Data.Entity;
using System.Linq;
[TestMethod]
public void TestChangeTrackingEF()
{
using (var entities = new MyEntities())
{
// Set Configuration.AutoDetectChangesEnabled = false to improve performance (100x).
entities.Configuration.AutoDetectChangesEnabled = false;
// By default use lazyloading.
entities.Configuration.LazyLoadingEnabled = true; 
// Get a specific entity from the database
var query = from i in entities.ImageInfo
where i.Id == 41
select i;
ImageInfo info = query.First();
// Update the entity
info.ProcessStatusId = 9;
// Set state to Modified
entities.Entry(info).State = System.Data.EntityState.Modified;
// Save changes to the database.
entities.SaveChanges();
}
}

NOTE

Setting the state to EntityState.Modified will update all columns of the object (because change tracking is disabled).

If you only want to update a specific column, for example you know which columns are dirty, then you can use the IsModified property of a DbPropertyEntry object, this object can be obtained by using the Entity.Property() method:

entities.Entry(info).Property("Description").IsModified = true;

How to bulk delete data based on strings in a generic list in C# and EF 4.2

Let’s assume you have a table Customer in a Microsoft SQL Server database:

 

image

 

And want to delete Customer_008, Customer_009, Customer_010, Customer_011 based on a generic list in C#, you can join this generic list with a entity dbset in entityframework 4.2:

NOTE: For best performance start the query with the generic list and then join on the Customer table.

 

[TestMethod]
public void RunNewCodeTest()
{
TestBulkDelete();
}
private void TestBulkDelete()
{
// Define customers to delete in a generic list of strings.
var names = new List<string>();
names.Add("Customer_038");
names.Add("Customer_039");
names.Add("Customer_040");
names.Add("Customer_041");
using (var entities = new Rvl.NewCode.Model.NewCodeEntities())
{
// To improve performance by a factor 1000, set Configuration.AutoDetectChangesEnabled = false.
entities.Configuration.AutoDetectChangesEnabled = false;
// Get customers to delete.
var query = from name in names
join customer in entities.Customer on name equals customer.Name
select customer;
List<Customer> customersToDelete = query.ToList();
// Delete customers from context.
foreach (Customer customer in customersToDelete)
{
entities.Customer.Remove(customer);
}
// Delete customers from database.
entities.SaveChanges();
}
}

 

 


How to use SqlBulkInsert for a many to many relationship in a Entity Framework 4.2 model with C# 4.0

Let’s say your product has a many to many relation between products en customers, then the the Entity Framework 4.2 model would look like:

 

image

 

A night process imports customers and the products they bought into a reporting database. During the night process the reporting database is closed, so we can lock the tables, then you can use the following C# code to insert data:

 

Result

Inserting 1.000 products, 1.000 customers and 1.000.000 sales records takes about 42 seconds on my laptop

Laptop cpu utilization:

image

 

Laptop disk utilization:

image

 

If you would use Entity Framework to insert, then this would take fore ever Winking smile

 

 

Code

The code could use the entity framework 4.2 entities and the data annotations applied to those entities, to validate the data to insert. That will be explained in an other post. The code uses one transaction to put all the data in the database and it will lock the tables to get a continuous identity on the tables. When an exceptions occurs the transaction will be roll backed.

 

[TestMethod]
public void TestMethod1()
{
BulkInsertManyToManyRelationship();
}
public void BulkInsertManyToManyRelationship()
{
// Connect to the database.
using (var connection = new SqlConnection("data source=.;initial catalog=Research;integrated security=True;multipleactiveresultsets=True;"))
{
connection.Open();
SqlTransaction transaction = null;
DataTable productTable = null;
DataTable customerTable = null;
DataTable saleTable = null;
try
{
transaction = connection.BeginTransaction(); // Use one transaction to put all the data in the database
// Create 1.000 products.
int lastProductIdentity = 0;
int productCount = 1000;
string productTableName = "Product";
productTable = GetSchemaInfo(productTableName, connection, transaction);
for (int i = 0; i < productCount; i++) 
{
DataRow row = productTable.NewRow();
row[1] = string.Format("{0}_{1}", productTableName, i.ToString().PadLeft(3, '0'));
productTable.Rows.Add(row);
}
BulkInsertTable(productTableName, productTable, connection, transaction);
lastProductIdentity = GetIdentity(productTableName, connection, transaction);
// Create 1.000 customers.
int lastCustomerIdentity = 0;
int customerCount = 1000;
string customerTableName = "Customer";
customerTable = GetSchemaInfo(customerTableName, connection, transaction);
for (int i = 0; i < customerCount; i++) 
{
DataRow row = customerTable.NewRow();
row[1] = string.Format("{0}_{1}", customerTableName, i.ToString().PadLeft(3, '0'));
customerTable.Rows.Add(row);
}
BulkInsertTable(customerTableName, customerTable, connection, transaction);
lastCustomerIdentity = GetIdentity(customerTableName, connection, transaction);
// Add all products to all customers, this will create 1.000.000 records
string saleTableName = "Sale";
saleTable = GetSchemaInfo(saleTableName, connection, transaction);
for (int c = lastCustomerIdentity - customerCount; c < lastCustomerIdentity; c++)
{
for (int p = lastProductIdentity - productCount; p < lastProductIdentity; p++)
{
DataRow row = saleTable.NewRow();
row[1] = p;
row[2] = c;
saleTable.Rows.Add(row);
}
}
BulkInsertTable(saleTableName, saleTable, connection, transaction);
transaction.Commit();
}
catch(Exception ex)
{
transaction.Rollback(); // This will not reset IDENT_CURRENT
}
finally
{
if (productTable != null) { productTable.Dispose(); }
if (customerTable != null) { customerTable.Dispose(); }
if (saleTable != null) { saleTable.Dispose(); }
if (transaction != null) { transaction.Dispose(); }
}
}
}
public DataTable GetSchemaInfo(string tableName, SqlConnection connection, SqlTransaction transaction)
{
DataTable dataTable = new DataTable();
using (SqlCommand selectSchemaCommand = connection.CreateCommand())
{
selectSchemaCommand.CommandText = string.Format("set fmtonly on; select * from {0}", tableName);
selectSchemaCommand.Transaction = transaction;
using (var adapter = new SqlDataAdapter(selectSchemaCommand)) // Get only the schema information for table [Sale]
{
adapter.FillSchema(dataTable, SchemaType.Source);
}
}
return dataTable;
}
public int GetIdentity(string tableName, SqlConnection connection, SqlTransaction transaction)
{
int identity = 0;
// Get the last customer identity
using (SqlCommand sqlCommand = connection.CreateCommand())
{
sqlCommand.CommandText = string.Format("SELECT IDENT_CURRENT('{0}')", tableName);
sqlCommand.Transaction = transaction;
identity = Convert.ToInt32(sqlCommand.ExecuteScalar());
}
return identity;
}
public void BulkInsertTable(string tableName, DataTable dataTable, SqlConnection connection, SqlTransaction transaction)
{
using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction)) // Lock the table
{
sqlBulkCopy.DestinationTableName = tableName;
sqlBulkCopy.WriteToServer(dataTable);
}
}

How to set the connectionstring at runtime of a dbcontext in Entity Framework 4.2

If you want to set the connectionstring for a dbcontext, when using EntityFramework 4.2, just create a partial class for the specific dbcontext and add a constructor that call’s de dbcontext constructor with connectionstring. Now you can instantiate MyDatabaseEntities with a connectionstring.

 

public partial class MyDatabaseEntities
{
public MyDatabaseEntities(string connection)
: base(connection)
{
}
}

The generated partial class looks something like:

 

public partial class MyDatabaseEntities : DbContext
{
public MyDatabaseEntities()
: base("name=MyDatabaseEntities")
{
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
throw new UnintentionalCodeFirstException();
}
public DbSet<Products> Products { get; set; }
}

How to list all entity names / table names from your Entity Framework model in Silverlight 4 and RIA services

If you want to list all entity names / table names from your Entity Framework model (*.edmx) in Silverlight 4, use a RIA Invoke operation:

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Metadata.Edm;
using System.ServiceModel.DomainServices.Server;
namespace Research.Web
{
public partial class ResearchDomainService
{
/// <summary>
/// Get the entity names from the entity framework model on which this DomainService is generated.
/// </summary>
/// <returns>
/// A list of entity / table names
/// </returns>
[Invoke]
public List<string> GetEntityTypeNames()
{
EntityContainer container = ObjectContext.MetadataWorkspace.GetEntityContainer(ObjectContext.DefaultContainerName, DataSpace.CSpace);
List<string> result = (from meta in container.BaseEntitySets
where meta.BuiltInTypeKind == BuiltInTypeKind.EntitySet
select meta.ElementType.ToString()).ToList<string>();
return result;
}
}
}

If the database contains the following tables:

 

image

 

The list will contain the strings:

ResearchModel.Customer

ResearchModel.Person

ResearchModel.Product

ResearchModel.ProductTag

ResearchModel.Tag