Introduction

In this session, we will learn about:

Creating Generic Repository with Entity Framework.
Taking advantage of .NET generics
Creating ASP.NET Core custom API controller CRUD operations with AJAX calls.
Consuming Entity Framework Core 2.0, in-memory database.
Interface
Class
Generic
async/await
DataContext
Overriding onModelCreating
Overriding SaveChanges
Overriding Update
Overriding UpdateAsync
Overriding SaveChangesAsync
Creating audit structure
Dispose
Creating Abstract class
Getting WindowsIdentity Current User
Creating a new insert item
Updating an Item
Deleting an Item
Writing HttpPost Create API method.
Writing HttpPut Update API method.
Writing HttpPost Delete API method.
Route table
Setting start controller Route to Home Index method.
jQuery/HTML pass table row value to a JavaScript function
jQuery Ajax HttpGet
jQuery Ajax HttpPut
jQuery Ajax HttpPost
Editable HTML table

Advantage of generic repository is that you can inherit from it, pass an entity type, and you will have CRUD operations. It will save you a lot of coding time when your domain objects are likely to grow over 50+. Another advantage is a change history functionality; you can inherit your entities from change history interface and you have the functionality.

The repository pattern creates an abstract layer between the Data Access Layer and the Service Layer of an application.

Entity Framework

There is more than one way to implement the unit of work and repository patterns. You need to pick which one works best for your project specific needs.

Entity Framework is an object-relational mapper(ORM), which enables the developer to work with relational data using domain-specific object and allows the use of LINQ or Lambda expressions to search or filer data in the database.

NOTE

Our generic repository is not bullet proof with all the functionalities but rather flexible enough that you can easily extend per your own needs.

Pre-Requirements

To be able to run the example from the download or build it from scratch, you need to have the following tools,

Visual Studio 2017 or above
.NET Core 2.0 or above

Generic repository Interface and Generic repository abstract class

GenericRepository<T> : IGenericRepository<T> where T : class
Entity Framework
We start with EF action methods and put them in a generic repository interface IGenericRepository.

//Copyright 2017 (c) SmartIT. All rights reserved. By John Kocer
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
namespace EfCoreGenericRepository.DataAccess
{
public interface IGenericRepository<T> where T : class
{
T Add(T t);
Task<T> AddAsyn(T t);
int Count();
Task<int> CountAsync();
void Delete(T entity);
Task<int> DeleteAsyn(T entity);
void Dispose();
T Find(Expression<Func<T, bool>> match);
ICollection<T> FindAll(Expression<Func<T, bool>> match);
Task<ICollection<T>> FindAllAsync(Expression<Func<T, bool>> match);
Task<T> FindAsync(Expression<Func<T, bool>> match);
IQueryable<T> FindBy(Expression<Func<T, bool>> predicate);
Task<ICollection<T>> FindByAsyn(Expression<Func<T, bool>> predicate);
T Get(int id);
IQueryable<T> GetAll();
Task<ICollection<T>> GetAllAsyn();
IQueryable<T> GetAllIncluding(params Expression<Func<T, object>>[] includeProperties);
Task<T> GetAsync(int id);
void Save();
Task<int> SaveAsync();
T Update(T t, object key);
Task<T> UpdateAsyn(T t, object key);
}
}

We implement GenericRepository with Generic T. You can add your future methods here. We added dispose method which can be called on the Controller’s override of the dispose method.

//Copyright 2017 (c) SmartIT. All rights reserved. By John Kocer
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
namespace EfCoreGenericRepository.DataAccess
{
public abstract class GenericRepository<T> : IGenericRepository<T> where T : class
{
protected DataContext _context;
public GenericRepository(DataContext context)
{
_context = context;
}
public IQueryable<T> GetAll()
{
return _context.Set<T>();
}
public virtual async Task<ICollection<T>> GetAllAsyn()
{
return await _context.Set<T>().ToListAsync();
}
public virtual T Get(int id)
{
return _context.Set<T>().Find(id);
}
public virtual async Task<T> GetAsync(int id)
{
return await _context.Set<T>().FindAsync(id);
}
public virtual T Add(T t)
{
_context.Set<T>().Add(t);
_context.SaveChanges();
return t;
}
public virtual async Task<T> AddAsyn(T t)
{
_context.Set<T>().Add(t);
await _context.SaveChangesAsync();
return t;
}
public virtual T Find(Expression<Func<T, bool>> match)
{
return _context.Set<T>().SingleOrDefault(match);
}
public virtual async Task<T> FindAsync(Expression<Func<T, bool>> match)
{
return await _context.Set<T>().SingleOrDefaultAsync(match);
}
public ICollection<T> FindAll(Expression<Func<T, bool>> match)
{
return _context.Set<T>().Where(match).ToList();
}
public async Task<ICollection<T>> FindAllAsync(Expression<Func<T, bool>> match)
{
return await _context.Set<T>().Where(match).ToListAsync();
}
public virtual void Delete(T entity)
{
_context.Set<T>().Remove(entity);
_context.SaveChanges();
}
public virtual async Task<int> DeleteAsyn(T entity)
{
_context.Set<T>().Remove(entity);
return await _context.SaveChangesAsync();
}
public virtual T Update(T t, object key)
{
if (t == null)
return null;
T exist = _context.Set<T>().Find(key);
if (exist != null) {
_context.Entry(exist).CurrentValues.SetValues(t);
_context.SaveChanges();
}
return exist;
}
public virtual async Task<T> UpdateAsyn(T t, object key)
{
if (t == null)
return null;
T exist =await _context.Set<T>().FindAsync(key);
if (exist != null)
{
_context.Entry(exist).CurrentValues.SetValues(t);
await _context.SaveChangesAsync();
}
return exist;
}
public int Count()
{
return _context.Set<T>().Count();
}
public async Task<int> CountAsync()
{
return await _context.Set<T>().CountAsync();
}
public virtual void Save()
{
_context.SaveChanges();
}
public async virtual Task<int> SaveAsync()
{
return await _context.SaveChangesAsync();
}
public virtual IQueryable<T> FindBy(Expression<Func<T, bool>> predicate)
{
IQueryable<T> query = _context.Set<T>().Where(predicate);
return query;
}
public virtual async Task<ICollection<T>> FindByAsyn(Expression<Func<T, bool>> predicate)
{
return await _context.Set<T>().Where(predicate).ToListAsync();
}
public IQueryable<T> GetAllIncluding(params Expression<Func<T, object>>[] includeProperties)
{
IQueryable<T> queryable = GetAll();
foreach (Expression<Func<T, object>> includeProperty in includeProperties)
{
queryable = queryable.Include<T, object>(includeProperty);
}
return queryable;
}
private bool disposed = false;
protected virtual void Dispose(bool disposing)
{
if (!this.disposed)
{
if (disposing)
{
_context.Dispose();
}
this.disposed = true;
}
}
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
}
}

Custom entity interface inherits from generic repository interface

IBlogRepository : IGenericRepository<Blog>

If you need any specific action for an entity such as id type we can put it here.

using EfCoreGenericRepository.Models;
namespace EfCoreGenericRepository.DataAccess
{
public interface IBlogRepository : IGenericRepository<Blog>
{
// If you need to customize your entity actions you can put here
Blog Get(int blogId);
}
}

Custom entity CRUD operation can be accomplish inheriting from

GenericRepository<Blog>, IBlogRepository

BlogRepository inherits from abstract class GenericRepository and Interface IBlogRepository. We override Update and UpdateAsync methods because CreateBy and CreatedOn values do not need to be changed after initial creation.

//Copyright 2017 (c) SmartIT. All rights reserved. By John Kocer
using System.Linq;
using System.Threading.Tasks;
using EfCoreGenericRepository.Models;
namespace EfCoreGenericRepository.DataAccess
{
public class BlogRepository : GenericRepository<Blog>, IBlogRepository
{
public BlogRepository(DataContext context) : base(context)
{
}
public Blog Get(int blogId)
{
var query = GetAll().FirstOrDefault(b => b.BlogId == blogId);
return query;
}
public async Task<Blog> GetSingleAsyn(int blogId)
{
return await _context.Set<Blog>().FindAsync(blogId);
}
public override Blog Update(Blog t, object key)
{
Blog exist = _context.Set<Blog>().Find(key);
if (exist != null)
{
t.CreatedBy = exist.CreatedBy;
t.CreatedOn = exist.CreatedOn;
}
return base.Update(t, key);
}
public async override Task<Blog> UpdateAsyn(Blog t, object key)
{
Blog exist =await _context.Set<Blog>().FindAsync(key);
if (exist != null)
{
t.CreatedBy = exist.CreatedBy;
t.CreatedOn = exist.CreatedOn;
}
return await base.UpdateAsyn(t, key);
}
}
}

Change Tracking with inheritance

IAuditable

Change tracking for audit purposes can be accomplished using IAudible. Any entity that needs to be change tracked inherits from IAudible interface. We override DataContext class SaveChanges and SaveChangesAsync methods to get CreatedBy, CreatedOn, UpdatedBy and UpdatedOn values.

using System;
namespace EfCoreGenericRepository.Models
{
public interface IAuditable
{
string CreatedBy { get; set; }
DateTime? CreatedOn { get; set; }
string UpdatedBy { get; set; }
DateTime? UpdatedOn { get; set; }
}
}

Blog entity inherits from IAudiable to have change tracking member implemented.

//Copyright 2017 (c) SmartIT. All rights reserved. By John Kocer
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
namespace EfCoreGenericRepository.Models
{
public class Blog:IAuditable
{
[Key]
public int BlogId { get; set; }
public string Title { get; set; }
public ICollection<Post> Posts { get; set; }=new HashSet<Post>();
public string CreatedBy { get; set; }
public DateTime? CreatedOn { get; set; }
public string UpdatedBy { get; set; }
public DateTime? UpdatedOn { get; set; }
}
}

DataContext class

DataContect class inherits from EF DbCotext class as we can see from below inheritance diagram.
Entity Framework

DataContect class has DataSet, overridden SaveChanges and SaveChangesAsync method, and UserProvider property to get current user in windows system, and TimeStampProvider to add Created and updated times. You can convert time to Universal Time (UTC) if you need to.

//Copyright 2017 (c) SmartIT. All rights reserved. By John Kocer
using System;
using System.Linq;
using System.Security.Principal;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using EfCoreGenericRepository.Models;
namespace EfCoreGenericRepository.DataAccess
{
public class DataContext : DbContext
{
public DataContext(DbContextOptions<DataContext> options) : base(options){}
protected override void OnModelCreating(ModelBuilder builder)
{
builder.Entity<Post>()
.HasOne(b => b.Blog)
.WithMany(p => p.Posts)
.IsRequired();
base.OnModelCreating(builder);
}
public virtual void Save()
{
base.SaveChanges();
}
public string UserProvider
{
get
{
if (!string.IsNullOrEmpty(WindowsIdentity.GetCurrent().Name))
return WindowsIdentity.GetCurrent().Name.Split('\\')[1];
return string.Empty;
}
}
public Func<DateTime> TimestampProvider { get; set; } = ()
=> DateTime.UtcNow;
public override int SaveChanges()
{
TrackChanges();
return base.SaveChanges();
}
public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = new CancellationToken())
{
TrackChanges();
return await base.SaveChangesAsync(cancellationToken);
}
private void TrackChanges()
{
foreach (var entry in this.ChangeTracker.Entries().Where(e => e.State == EntityState.Added || e.State == EntityState.Modified))
{
if (entry.Entity is IAuditable)
{
var auditable = entry.Entity as IAuditable;
if (entry.State == EntityState.Added)
{
auditable.CreatedBy = UserProvider;//
auditable.CreatedOn = TimestampProvider();
auditable.UpdatedOn = TimestampProvider();
}
else
{
auditable.UpdatedBy = UserProvider;
auditable.UpdatedOn = TimestampProvider();
}
}
}
}
public DbSet<Blog> Blog { get; set; }
public DbSet<BlogDetail> BlogDetail { get; set; }
public DbSet<Post> Post { get; set; }
}
}

Consuming the Generic Repository class

API Controller, MVC Controller or Service layer.

On WebAPI controller we have repository specific Database calls through our generic repository. It is a good idea to have service layer between repository and API controller.

We dispose the Context object here calling overridden dispose method.

//Copyright 2017 (c) SmartIT. All rights reserved. By John Kocer
using Microsoft.AspNetCore.Mvc;
using System.Collections.Generic;
using System.Threading.Tasks;
using EfCoreGenericRepository.DataAccess;
namespace Blog.Ui.Controllers
{
[Produces("application/json")]
[Route("api/Blog")]
public class BlogsApiController : Controller
{
private readonly IBlogRepository _blogRepository;
public BlogsApiController(IBlogRepository blogRepository)
{
_blogRepository = blogRepository;
}
[Route("~/api/GetBlogs")]
[HttpGet]
public async Task<IEnumerable<EfCoreGenericRepository.Models.Blog>> Index()
{
return await _blogRepository.GetAllAsyn();
}
[Route("~/api/AddBlog")]
[HttpPost]
public async Task<EfCoreGenericRepository.Models.Blog> AddBlog([FromBody]EfCoreGenericRepository.Models.Blog blog)
{
await _blogRepository.AddAsyn(blog);
await _blogRepository.SaveAsync();
return blog;
}
[Route("~/api/UpdateBlog")]
[HttpPut]
//public Blog UpdateBlog([FromBody] Blog blog)
//{
// var updated = _blogRepository.Update(blog, blog.BlogId);
// return updated;
//}
public async Task<EfCoreGenericRepository.Models.Blog> UpdateBlog([FromBody]EfCoreGenericRepository.Models.Blog blog)
{
var updated =await _blogRepository.UpdateAsyn(blog, blog.BlogId);
return updated;
}
[Route("~/api/DeleteBlog/{id}")]
[HttpDelete]
public string Delete(int id)
{
_blogRepository.Delete(_blogRepository.Get(id));
return "Employee deleted successfully!";
}
protected override void Dispose(bool disposing)
{
_blogRepository.Dispose();
base.Dispose(disposing);
}
}
}

Dependency Injection

We add generic repository dependency injection in the Startup configure services method.

//in the Startup.cs
public void ConfigureServices(IServiceCollection services)
{
services.AddMvc();
services.AddDbContext<DataContext>(options =>
options.UseInMemoryDatabase());
services.AddTransient<IBlogRepository, BlogRepository>();
}

When we run our demo application, we will see the below page which does Blog CRUD operations using our generic repository.

Entity Framework

Summary

In this article, we have learned how to write generic repository pattern. In Part 2, we will learn repository and Unit of work pattern implementation.

Tasks leaned from this article,

Creating Generic Repository with Entity Framework.
Taking advantage of .NET generics
Creating ASP.NET Core custom API controller CRUD operations with AJAX calls.
Consuming Entity Framework Core 2.0, in-memory database.
Interface
Class
Generic
async/await
DataContext
Overriding onModelCreating
Overriding SaveChanges
Overriding Update
Overriding UpdateAsync
Overriding SaveChangesAsync
Creating audit structure
Dispose
Creating Abstract class
Getting WindowsIdentity Current User
Creating a new insert item
Updating an Item
Deleting an Item
Writing HttpPost Create API method.
Writing HttpPut Update API method.
Writing HttpPost Delete API method.
Route table
Setting start controller Route to Home Index method.
jQuery/HTML pass table row value to a JavaScript function
jQuery Ajax HttpGet
jQuery Ajax HttpPut
jQuery Ajax HttpPost
Editable HTML table

Download source code from GitHub.

Resources

Books

https://www.amazon.com/s/ref=dp_byline_sr_ebooks_1?ie=UTF8&field-author=John+Kocer&search-alias=digital-text&text=John+Kocer&sort=relevancerank

Visual Studio Download Site

https://www.visualstudio.com/downloads/

Node.js installation page

https://nodejs.org/en/download/

Postman Download

https://www.getpostman.com/

SmartIT.Employee.MockDB Download

https://www.nuget.org/packages/SmartIT.Employee.MockDB/

SmartIT.Payment.MockDB Download https://www.nuget.org/packages/SmartIT.Payment.MockDB/

20 Comments

Join the conversation! Your thoughts help the community grow.

Ali8y ago
Hi @John Kocer if I want to do projection in my controller API and select only some of the fields from a table and return a DTO how I can do it with your repository? I want to do it before accessing the database to prevent fetching unused data
Omkar Mhaiskar8y ago
Great article and neat explanation. I have one more query regarding integration on SQL stored procedure. Coz in our application, there were multiple scenarios where we have used the stored procedure. Is it possible to include a generic function for Calling Stored procedure? It will help to other scenarios such as my case. Can you provide more details about integrating such generic function in your code?
Nico Blidaru8y ago
Great article! Where can I find part 2??
Abishek Sharma8y ago
Hey John. Really nice article. I have a question about the service layer recommended between Data access and controller. How do we handle DI in service layer without Startup class as normally the service layer project will be a class library project and they don't have the startup class. Do we manually add the class?
- Edinio DESIR7y ago
  Hello, I have the same concern. How did you handle this?
- RAJESH DONEPUDI5y ago
  You can use the API Project to register your services in startup class
John Kocer8y ago
Https://www.amazon.com/Professional-Entity-Framework-Core-Examples-ebook/dp/B077WT7J3Q/ref=sr_1_1?s=digital-text&ie=UTF8&qid=1515088789&sr=1-1
- Joe Thomas7y ago
  Hi John Kocer how does a person deal with Different Table Columns names not having Id, Say my primary key is ProductId, FoodId, CustomerId, not Id as in Generic Repository, see problem here, https://stackoverflow.com/questions/57386900/net-core-generic-repository-primary-id-key-performance-in-entity-framework , first method var idName = _context.Model.FindEntityType(typeof(TEntity)) .FindPrimaryKey().Properties.Single().Name; is not working right now
mohamed masrooj9y ago
Hello, Good work this is really useful to me. If I have more repository class do i need to add all of those into services. is there any best method?
John Kocer9y ago
This project is simplified for user, so that user can understand the code easily. Real life application business logic will be in a class library project. It is a good idea to have service layer between DAL and UI layer.
Niraj Lama9y ago
Likewise whats the point of using await _blogRepository.SaveAsync(); in adding blog when you have already used await _context.SaveChanges(); in repository itself?
- John Kocer9y ago
  It is a good practice to make _context.SaveChanges() as protected and use encapsulated _blogRepository.SaveAsync(); method
- Niraj Lama9y ago
  Lastly, where do u keep ur business logic!
Niraj Lama9y ago
Why do u need to dispose your repository?
- John Kocer9y ago
  If you look at how we are creating BlogRepository
- John Kocer9y ago
  Services.AddTransient<IBlogRepository, BlogRepository>(); It is a transient.Lifetime services are created each time they are requested. This lifetime works best for lightweight, stateless services.
- John Kocer9y ago
  Usually if you are working with a microservices with messaging HTTP calls to database may take longer time, and Entity framework is a Singleton that means it creates a single instance throughout the application and it is not thread-safe. It creates the instance for the first time and reuses the same object in the all calls. In summary, plain English, for async/await, We need to dispose dataContext for every use and recreate when we need again. You can’t make two DataContext save operation async the same time because, DataContext is not thread-safe. Hope this will help.
Karthick Kumar9y ago
Nice article thanks for sharing
Rijwan Ansari9y ago
Nice article ................