Tag Archives: Code first

Implementing Repository Pattern with Entity Framework – Code First Model Approach

Posted on by
4

When working with Entity Framework – Code First model approach, developer creates POCO entities for Database tables. The benefit of using Code First model is to have POCO entity for each table that can be used as either WCF Data Contracts or you can apply your own custom attributes to handle Security, Logging, etc. and there is no mapping needed as we used to do in Entity Framework (Model First) approach if the application architecture is n-tier based.

Considering the Data Access layer, we will implement a repository pattern that encapsulates the persistence logic in a separate class. This class will be responsible to perform database operations. Let’s suppose the application is based on n-tier architecture and having 3 tiers namely Presentation, Business and Data Access. Common library contains all our POCO entities that will be used by all the layers.

Presentation Layer: Contains Views, Forms

Business Layer: Managers that handle logic functionality

Data Access Layer: Contains Repository class that handles CRUD operations

Common Library: Contain POCO entities.

We will implement an interface named “IRepository” that defines the signature of all the appropriate generic methods needed to perform CRUD operation and then implement the Repository class that defines the actual implementation of each method. We can also instantiate Repository object using Dependency Injection or apply Factory pattern.

Code Snippet: IRepository


public
interface
IRepository : IDisposable

{


///
<summary>


/// Gets all objects from database


///
</summary>


///
<returns></returns>


IQueryable<T> All<T>() where T : class;


///
<summary>


/// Gets objects from database by filter.


///
</summary>


///
<param name=”predicate”>Specified a filter</param>


///
<returns></returns>


IQueryable<T> Filter<T>(Expression<Func<T, bool>> predicate) where T : class;


///
<summary>


/// Gets objects from database with filting and paging.


///
</summary>


///
<typeparam name=”Key”>


///
<param name=”filter”>Specified a filter</param>


///
<param name=”total”>Returns the total records count of the filter.</param>


///
<param name=”index”>Specified the page index.</param>


///
<param name=”size”>Specified the page size</param>


///
<returns></returns>


IQueryable<T> Filter<T>(Expression<Func<T, bool>> filter, out
int total, int index = 0, int size = 50) where T : class;


///
<summary>


/// Gets the object(s) is exists in database by specified filter.


///
</summary>


///
<param name=”predicate”>Specified the filter expression</param>


///
<returns></returns>


bool Contains<T>(Expression<Func<T, bool>> predicate) where T : class;


///
<summary>


/// Find object by keys.


///
</summary>


///
<param name=”keys”>Specified the search keys.</param>


///
<returns></returns>

T Find<T>(params
object[] keys) where T : class;


///
<summary>


/// Find object by specified expression.


///
</summary>


///
<param name=”predicate”></param>


///
<returns></returns>

T Find<T>(Expression<Func<T, bool>> predicate) where T : class;


///
<summary>


/// Create a new object to database.


///
</summary>


///
<param name=”t”>Specified a new object to create.</param>


///
<returns></returns>

T Create<T>(T t) where T : class;


///
<summary>


/// Delete the object from database.


///
</summary>


///
<param name=”t”>Specified a existing object to delete.</param>


int Delete<T>(T t) where T : class;


///
<summary>


/// Delete objects from database by specified filter expression.


///
</summary>


///
<param name=”predicate”></param>


///
<returns></returns>


int Delete<T>(Expression<Func<T, bool>> predicate) where T : class;


///
<summary>


/// Update object changes and save to database.


///
</summary>


///
<param name=”t”>Specified the object to save.</param>


///
<returns></returns>


int Update<T>(T t) where T : class;


///
<summary>


/// Select Single Item by specified expression.


///
</summary>


///
<typeparam name=”T”></typeparam>


///
<param name=”expression”></param>


///
<returns></returns>

T Single<T>(Expression<Func<T, bool>> expression) where T : class;


void SaveChanges();


void ExecuteProcedure(String procedureCommand, params
SqlParameter[] sqlParams);

}

Code Snippet: Repository

public
class
Repository : IRepository

{


DbContext Context;


public Repository()

{

Context = new
DBContext();

}


public Repository(DBContext context)

{

Context = context;

}


public
void CommitChanges()

{

 Context.SaveChanges();

}


public T Single<T>(Expression<Func<T, bool>> expression) where T : class

{


return All().FirstOrDefault(expression);

}


public
IQueryable<T> All<T>() where T : class

{


return Context.Set().AsQueryable();

}


public
virtual
IQueryable<T> Filter<T>(Expression<Func<T, bool>> predicate) where T : class

{


return Context.Set<T>().Where<T>(predicate).AsQueryable<T>();

}


public
virtual
IQueryable<T> Filter<T>(Expression<Func<T, bool>> filter, out
int total, int index = 0, int size = 50) where T : class

{


int skipCount = index * size;


var _resetSet = filter != null ? Context.Set().Where(filter).AsQueryable() : Context.Set().AsQueryable();

 _resetSet = skipCount == 0 ? _resetSet.Take(size) : _resetSet.Skip(skipCount).Take(size);

total = _resetSet.Count();


return _resetSet.AsQueryable();

}


public
virtual T Create(T TObject) where T : class

{


var newEntry = Context.Set().Add(TObject);

Context.SaveChanges();


return newEntry;

}


public
virtual
int Delete(T TObject) where T : class

{

 Context.Set().Remove(TObject);


return Context.SaveChanges();

}


public
virtual
int Update(T TObject) where T : class

{


try

{


var entry = Context.Entry(TObject);

 Context.Set().Attach(TObject);

entry.State = EntityState.Modified;


return Context.SaveChanges();

}


catch (OptimisticConcurrencyException ex)

{


throw ex;

}

}


public
virtual
int Delete<T>(Expression<Func<T, bool>> predicate) where T : class

{


var objects = Filter<T>(predicate);


foreach (var obj in objects)

Context.Set<T>().Remove(obj);


return Context.SaveChanges();

}


public
bool Contains<T>(Expression<Func<T, bool>> predicate) where T : class

{


return Context.Set<T>().Count<T>(predicate) > 0;

}


public
virtual T Find<T>(params
object[] keys) where T : class

{


return (T)Context.Set<T>().Find(keys);

}


public
virtual T Find<T>(Expression<Func<T, bool>> predicate) where T : class

{


return Context.Set<T>().FirstOrDefault<T>(predicate);

}


public
virtual
void ExecuteProcedure(String procedureCommand, params
SqlParameter[] sqlParams){

 Context.Database.ExecuteSqlCommand(procedureCommand, sqlParams);

}


public
virtual
void SaveChanges()

{

Context.SaveChanges();

}


public
void Dispose()

{


if (Context != null)

Context.Dispose();

}

}

The benefit of using Repository pattern is that all the database operations will be managed centrally and in future if you want to change the underlying database connector you can add another Repository class and defines its own implementation or change the existing one.

Using Code First in Entity Framework

Posted on by
104

Introduction

Generally, entity framework is majorly used by adding an edmx file into your .Net solution and creating tables or dropping tables from existing SQL database. But in certain cases when we are working on large scale applications and having layered architecture with different layers (presentation, service, business and data access) it becomes quite difficult for the developer to make it loosely coupled.

In order to explain, let’s take a simple scenario. Let suppose you have a Web Application with following layers

  1. Web Front End ( ASP.Net MVC)
  2. Service Layer (WCF, Web Services)
  3. Business Layer (Business Managers)
  4. Data Access Layer (Entity Framework)

Web Front End use some models that are bean classes to store data and transmit them the Service layer, service layers are expecting Data Contracts to be passed as parameters and Business Layers actually processes that data and passes it to Data Access Layer to perform to and fro CRUD operations.

Problem

We can make common library project that contains single bean which can be travel from presentation layer to Services and Services to Business layer. Problem arises when we need to pass it to the entity framework. As if we are not using a code first approach we need to map that class to appropriate entity framework entity class. This will increase performance decline and also increase extra effort for developer to provide mappings for all the entities.

Resolution

When you use Code First model you really don’t need to include edmx file into your solution, and you just have to define bean classes or entity classes for each table creating or created already. This helps to place all the classes in one common library that will be referenced by all and will support loosely coupling of layers.

Steps

Steps to Create Entity Framework Code First Model

  1. Create a new Project named as CommonEntities
  2. Add a Library Package Reference of EFCodeFirst using NuGet

  3. Create a new folder named as “Entities” inside your project and define all the entities. Entities contain setter getter properties for all the columns in a table.

For e.g. here are the sample classes


public
class
DocumentColumns

{

[Key]


public
Int32 ColumnId { get; set; }


public
String ColumnName { get; set; }


public
String ColumnType { get; set; }


public
Int32 TypeSize { get; set; }


public
Boolean IsPrimary { get; set; }


public
Boolean AllowNulls { get; set; }


public
Boolean IsMeasure { get; set; }


public
Int32 DocumentId { get; set; }


public
String ColumnSheets { get; set; }

}


public
class
Document

{

[Key]


public
Int32 DocumentId { get; set; }


public
String DocumentPath { get; set; }


public
String DocumentName { get; set; }


public
String DocumentDescription { get; set; }


public
String TableName { get; set; }


public
Boolean IsActive { get; set; }


public
Boolean IsStarSchema { get; set; }


public
String DatabaseName { get; set; }


public
ICollection<DocumentColumns> DocumentColumns { get; set; }

}

Attribute [Key] denotes that particular property is a primary key.

  1. Create a custom context class named as “MyDbContext” which should be derived from System.Data.Entity.DbContext

    public
    class
    SchedulerContext : System.Data.Entity.DbContext

    {


    public
    DbSet<Document> Documents { get; set; }


    public
    DbSet<DocumentColumns> DocumentColumns { get; set; }


    protected
    override
    void OnModelCreating(System.Data.Entity.ModelConfiguration.ModelBuilder modelBuilder)

    {

    modelBuilder.IncludeMetadataInDatabase = false;

    }

    }

Here in the above snippet I have overridden the OnModelCreating method and set IncludeMetadataInDatabase = false, this basically not create a new database. This is normally used when you have already an existing database created and you don’t want Entity Framework to create a new database for you.

  1. Add the App.config file in your project and specify the Connectionstring as follows

<connectionStrings>

<add
name=“ExcelScheduler.Database.SchedulerContext”


connectionString=“Data Source=.\sqlexpress;Initial Catalog=SchedulerDB;Integrated Security=True”


providerName=“System.Data.SqlClient“ />

</connectionStrings>

This is all done!

Now in order the treat any entity class as WCF data contract you just need to annotate your entity class with [DataContract] attribute and properties with [DataMember] attribute. And same goes if you want to use it with Asp.Net MVC models.