Entity Framework (EF) is an object-relational mapper that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.
EF has two different types of approaches.
1. Db First.
2. Code First.
Scenario details below.
I want to speak more about the Accounting packages,where in we deal with stock in and stock out details.
First create the data model project of type (Class library.)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace DataModels
{
public class Customer
{
public int Id { get; set; }
public string FirstName { get; set; }
public string MiddleName { get; set; }
public string LastName { get; set; }
public int Age { get; set; }
public int MobileNo { get; set; }
public int HomePhoneNo { get; set; }
public string ResidentialAddress { get; set; }
public string BusinessAddress { get; set; }
public string ReferredByName { get; set; }
public int ReferredById { get; set; }
public int RefereedByMobileNo { get; set; }
public DateTime CreationDate { get; set; }
public DateTime ModifiedDate { get; set; }
public string Description { get; set; }
}
}
Create a Separte project for Data Access Layer.
Install the Nuget package for Entity Framework.
Then we have to create the mapping for this. Basically this is needed as we have to create the validations at db level. This is a kind of standard approach. Dev's can skip if they want.
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.ModelConfiguration;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataAccess;
namespace DataAccessLayer
{
public class CustomerMap : EntityTypeConfiguration<DataModels.Customer>
{
/// <summary>
/// Mapping class for Customer details...
/// </summary>
public CustomerMap()
{
// Primary Key
this.HasKey(t => t.Id);
this.Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
// Properties
this.Property(t => t.FirstName).HasColumnType("nvarchar").HasMaxLength(Constant.StringMaxLength).IsRequired();
this.Property(t => t.MiddleName).HasColumnType("nvarchar").HasMaxLength(Constant.StringMaxLength).IsRequired();
this.Property(t => t.LastName).HasColumnType("nvarchar").HasMaxLength(Constant.StringMaxLength).IsRequired();
this.Property(t => t.Age).IsOptional();
this.Property(t => t.MobileNo).IsRequired();
this.Property(t => t.HomePhoneNo).IsOptional();
this.Property(t => t.ResidentialAddress).HasColumnType("nvarchar").HasMaxLength(Constant.StringMaxLength).IsRequired();
this.Property(t => t.ReferredByName).IsOptional();
this.Property(t => t.ReferredById).IsRequired();
this.Property(t => t.ModifiedDate).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed).IsRequired();
this.Property(t => t.CreationDate).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Computed).IsRequired();
this.Property(t => t.Description).HasMaxLength(Constant.StringMaxLength).IsOptional();
}
}
}
Your typical Data Access Layer Solution Structure should look like below screen shot.
The Accounts Context class would be inherited from the dbContext. It would be passing the connection string in the constructor. We have mention the dbSet of all the Models which are to be seen as tables after migrations.
Few important points in the Context Class are below.
1. Connection string,the way we pass it.
2. DbSets Or Models refferred.
3. Save changes method. We can override it.
4. OnModelCreating Method ,where in we refer the mapping classes (validation kept on the models).
Find the below Context class for your reference.
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using DataModels;
using DataAccessLayer;
using System.Configuration;
namespace DataAccess
{
public class AccountsContext : DbContext
{
public static string connstr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();
public AccountsContext()
: base(connstr)
{
this.Configuration.ProxyCreationEnabled = false;
}
public DbSet<DataModels.Customer> Projects { get; set; }
public DbSet<DataModels.StockIn> Purchase { get; set; }
public override int SaveChanges()
{
var changedEntries = this.ChangeTracker.Entries()
.Where(item => item.State == EntityState.Added
|| item.State == EntityState.Deleted
|| item.State == EntityState.Modified);
// var auditLogs = this.ConstructAuditLogs(changedEntries);
int rowsAffected = 0;
try
{
// Save User Operations
rowsAffected = base.SaveChanges();
// this.AuditLogs.AddRange(auditLogs);
var task = base.SaveChangesAsync();
task.Wait();
}
catch (Exception exception)
{
}
return rowsAffected;
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
//for mapping the stored proc
modelBuilder.Entity<DataModels.Customer>().MapToStoredProcedures();
//for Mapping Classes that are set with fluent validations..
modelBuilder.Configurations.Add(new CustomerMap());
}
private List<AuditLog> ConstructAuditLogs(IEnumerable<DbEntityEntry> dbEntityEntries)
{
var auditLogs = new List<AuditLog>();
var jsonSerializerSettings = new Newtonsoft.Json.JsonSerializerSettings
{
PreserveReferencesHandling = Newtonsoft.Json.PreserveReferencesHandling.Objects
};
// Get the Table() attribute, if one exists
TableAttribute tableAttr = null;
// Get table name (if it has a Table attribute, use that, otherwise get the pluralized name)
string tableName = null;
foreach (var dbEntry in dbEntityEntries)
{
// Get Table Attribute
tableAttr = dbEntry.Entity.GetType().GetCustomAttributes(typeof(TableAttribute), false).SingleOrDefault() as TableAttribute;
// Use Table Name from Table Attribute else use the type name
tableName = tableAttr != null ? tableAttr.Name : dbEntry.Entity.GetType().Name;
// AuditLog
var auditLog = new AuditLog()
{
TableName = tableName,
ActionType = dbEntry.State.ToString(),
CreatedBy = System.Threading.Thread.CurrentPrincipal.Identity.Name,
CreatedDateTime = DateTime.Now,
OriginalValues = (dbEntry.State != EntityState.Added) ? this.ConstructValues(dbEntry.OriginalValues, jsonSerializerSettings) : null,
NewValues = (dbEntry.State != EntityState.Deleted) ? this.ConstructValues(dbEntry.CurrentValues, jsonSerializerSettings) : null,
EntityLog = Newtonsoft.Json.JsonConvert.SerializeObject(dbEntry.Entity, Newtonsoft.Json.Formatting.Indented, jsonSerializerSettings)
};
auditLogs.Add(auditLog);
}
return auditLogs;
}
private string ConstructValues(DbPropertyValues dbPropertyValues, Newtonsoft.Json.JsonSerializerSettings jsonSettings)
{
var dictionary = new Dictionary<string, object>();
foreach (var propertyName in dbPropertyValues.PropertyNames)
{
dictionary.Add(propertyName, dbPropertyValues[propertyName]);
}
var result = Newtonsoft.Json.JsonConvert.SerializeObject(dictionary, Newtonsoft.Json.Formatting.Indented, jsonSettings);
return result;
}
}
}
Now we are ready with all the setting of entity framework ,go ahead and run the migration comments.
The data access layer project should be having the app.config which would be having the connection string.
Then Run the entity framework commands for the code first.
Follow the below article for the reference.
http://www.asp.net/mvc/overview/getting-started/getting-started-with-ef-using-mvc/migrations-and-deployment-with-the-entity-framework-in-an-asp-net-mvc-application
