EF Code First is similar to ruby on rails database setup. You just define your entities with attributes in a class file and reference them as tables. They use predefined conventions to create keys and relations. Ruby on rails is much mature compare to EF code first, but Asp.NET MVC is really doing well with recent features. I normally dont use code first for large projects. It is mostly for initial phase. For large projects, you can use database project in VS which provides schema comparison, pre and post deployment scripts, multi-environment config support etc. I like CodeFirst as part of MVC.
I will walk you through simple and more realistic example. We will start with one table. Before that, you can start a MVC 3 app in visual studio and import EF codefirst libraries from nuget.
How to create the table with code first
Our sample app will be market campaign setup and management. We will have MarketEvent class with some properties to start with. Create this class at Model folder.
public class MarketEvent { [Required] public global::System.Int32 MarketEventId { get; set; } [Required] [StringLength(50)] public global::System.String Campaign { get; set; } [Required] [StringLength(50)] public global::System.String CampaignType { get; set; } [StringLength(50)] public global::System.String Description { get; set; } public Nullable<global::System.DateTime> StartDate { get; set; } public Nullable<global::System.DateTime> EndDate { get; set; } public global::System.Guid RowGuid { get; set; } [Required] [StringLength(50)] public global::System.String EventStatus { get; set; } }
We are using Required, StringLength attributes to decorate our model. Code first uses these attributes to provide validations and to create tables with predefined fields. Your string length attribute will add validation to web page and also create these fields with specified length. If you don't use
StringLength, field type will be nvarchar(max) in your database table. It is best to reduce regular columns to 50 varchar or something, so you should add
StringLength attribute to string fields.
We defined our entity above, but it is not defined as a table yet. We need a class that inherits from DbContext. I copied our table definition. We will have two tables if we run the app. EF Codefirst will add Metadata table to keep track of changes. You can get rid of that if you want with remove call and related convention. Create this class at model folder.
public class MarketContext : DbContext { public MarketContext() { } public MarketContext(string connectionstr) : base(connectionstr) { //inject your connection string } public DbSet<MarketEvent> MarketEvents { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { // add your extra model rules here
}
}
Build your app. Now, we will create controller for this entity. We will use scaffolding with razor view engine. It is good to start with. If you right click controller folder and then click add controller, you can select entity type and context. Screenshot below:
Scaffolding will generate create, edit,details and delete methods and views.
You can create a contexxt obj with your static connection string if you dont want to use default connection convetion.
private MarketContext db = new MarketContext(ConnectionStr); // // GET: /MarketEvent/ public ViewResult Index() { var marketevents = db.MarketEvents ; return View(marketevents.ToList()); }
Lets add more tables to our model and then show how to do the seeding and stored proc support.
We will define foreign key relations by convetion. Inside our updated marketevent class, we have Company property and CompanyId field. EF will create a foreign key for us at CompanyId and navigation with Company property. If you use Tablename+Id , conventions will work fine. Relatioins can be delicate with conventions, so you may need to use onmodelcreating call to add more relation.
public class MarketEvent { [Required] public global::System.Int32 MarketEventId { get; set; } [Required] public global::System.Int32 CompanyId { get; set; } public Company Company { get; set; } [Required] [StringLength(50)] public global::System.String Campaign { get; set; } [Required] [StringLength(50)] public global::System.String CampaignType { get; set; } [StringLength(50)] public global::System.String Description { get; set; } public Nullable<global::System.DateTime> StartDate { get; set; } public Nullable<global::System.DateTime> EndDate { get; set; } public global::System.Guid RowGuid { get; set; } [Required] [StringLength(50)] public global::System.String EventStatus { get; set; } } public class Company { public int CompanyId { get; set; } public string Name { get; set; } } public class Customer { public int CustomerId { get; set; } public global::System.Guid RowGuid { get; set; } public global::System.Int32 CompanyId { get; set; } public Company Company { get; set; } [Required] [StringLength(50)] [Display(Name = "First Name*")] public string FirstName { get; set; } [Required] [StringLength(50)] [Display(Name = "Last Name*")] public string LastName { get; set; } [StringLength(50)] [Display(Name = "Email")] public string EmailAddress { get; set; } [Required] [StringLength(50)] [Display(Name = "Home Address*")] public string Address { get; set; } [StringLength(50)] [Display(Name = "Apt / Unit")] public string Address2 { get; set; } [Required] [StringLength(50)] [Display(Name = "City*")] public string City { get; set; } [Required] [StringLength(2)] //Select [Display(Name = "State*")] public string State { get; set; } [Required] [StringLength(10)] [Display(Name = "Zip Code*", Prompt = "Only Numbers")] public string PostalCode { get; set; } } public class User { public int UserId { get; set; } /// <summary> /// 0 for asp.net /// </summary> public int LoginMethod { get; set; } /// <summary> /// Reference key to authenticaiton source /// asp.net membership will be guid /// </summary> public string RefKey { get; set; } public DateTime CreatedOn { get; set; } public DateTime ModifiedOn { get; set; } public string Username { get; set; } } /// <summary> /// Individual record /// </summary> public class MarketEventItem { [Required] public global::System.Int32 MarketEventItemId { get; set; } [StringLength(50)] public string Barcode { get; set; } [Required] public int CustomerId { get; set; } [Required] public DateTime Created { get; set; } public DateTime Mailed { get; set; } public DateTime? Returned { get; set; } } public class ItemComment { [Required] public int Id { get; set; } [Required] public DateTime DateCreated { get; set; } [StringLength(140)] public string Content { get; set; } [Required] public int MarketEventItemId { get; set; } public MarketEventItem MarketEventItem { get; set; } /// <summary> /// dont need navig. /// </summary> public int UserId { get; set; } } public class EventComment { public int Id { get; set; } [Required] public DateTime DateCreated { get; set; } [StringLength(140)] public string Content { get; set; } [Required] public int MarketEventId { get; set; } public MarketEvent MarketEvent { get; set; } /// <summary> /// dont need navig. /// </summary> public int UserId { get; set; } } public class sp_GetCacheUpdateTime_view { public DateTime UpdateTime { get; set; } } public class MarketContext : DbContext { public MarketContext() { } public MarketContext(string connectionstr) : base(connectionstr) { //inject your connection string here } public DbSet<MarketEvent> MarketEvents { get; set; } public DbSet<MarketEventItem> MarketEventItems { get; set; } public DbSet<ItemComment> ItemComments { get; set; } public DbSet<EventComment> EventComments { get; set; } public DbSet<User> Users { get; set; } public DbSet<Customer> Customers { get; set; } public DbSet<Company> Companys { get; set; } public IEnumerable<sp_GetCacheUpdateTime_view> ProcGetCacheUpdateTime(string tablename) { //with param //this.Database.SqlQuery<myEntityType>("sp_GetCacheUpdateTime @param1, @param2, @param3", new SqlParameter("param1", param1), new SqlParameter("param2", param2), new SqlParameter("param3", param3)); //we are returning a select statement so it is best to get first or default here return this.Database.SqlQuery<sp_GetCacheUpdateTime_view>("sp_GetCacheUpdateTime @tablename",new SqlParameter("tablename",tablename)); } protected override void OnModelCreating(DbModelBuilder modelBuilder) { // add your extra model rules here //we have seed db calls in our initializer class //modelBuilder.Conventions.Remove<IncludeMetadataConvention>(); } }
---------------------------------------------------------------------------------------------
We got our model complete. We will define data initializer for this context. You can register that at global.asax file.
public class MyInitializer : DropCreateDatabaseIfModelChanges<MarketContext> { public static string TableCacheTrack = @" CREATE TABLE [dbo].[CacheTrack]( [CacheTrackId] [int] IDENTITY(1,1) NOT NULL primary key, [TableName] [nvarchar](50) NOT NULL, [UpdateTime] [datetime] NOT NULL, ) "; #region ErrorLog Related public static string TableErrorLog = @" CREATE TABLE [dbo].[ErrorLog]( [id] [int] IDENTITY(1,1) NOT NULL primary key, [UserName] [nvarchar](50) NULL, [ErrorNumber] [int] NULL, [ErrorSeverity] [int] NULL, [ErrorState] [int] NULL, [ErrorProcedure] [nvarchar](50) NULL, [ErrorLine] [int] NULL, [ErrorMessage] [nvarchar](2000) NULL ) "; public static string ProcPrintError = @" CREATE PROCEDURE [dbo].[uspPrintError] AS BEGIN SET NOCOUNT ON; -- Print error information. PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) + ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) + ', State ' + CONVERT(varchar(5), ERROR_STATE()) + ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') + ', Line ' + CONVERT(varchar(5), ERROR_LINE()); PRINT ERROR_MESSAGE(); END;"; public static string ProcLogError = @" CREATE PROCEDURE [dbo].[uspLogError] @ErrorLogID [int] = 0 OUTPUT -- Contains the ErrorLogID of the row inserted -- by uspLogError in the ErrorLog table. AS BEGIN SET NOCOUNT ON; -- Output parameter value of 0 indicates that error -- information was not logged. SET @ErrorLogID = 0; BEGIN TRY -- Return if there is no error information to log. IF ERROR_NUMBER() IS NULL RETURN; -- Return if inside an uncommittable transaction. -- Data insertion/modification is not allowed when -- a transaction is in an uncommittable state. IF XACT_STATE() = -1 BEGIN PRINT 'Cannot log error since the current transaction is in an uncommittable state. ' + 'Rollback the transaction before executing uspLogError in order to successfully log error information.'; RETURN; END; INSERT [dbo].[ErrorLog] ( [UserName], [ErrorNumber], [ErrorSeverity], [ErrorState], [ErrorProcedure], [ErrorLine], [ErrorMessage] ) VALUES ( CONVERT(sysname, CURRENT_USER), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE() ); -- Pass back the ErrorLogID of the row inserted SELECT @ErrorLogID = @@IDENTITY; END TRY BEGIN CATCH PRINT 'An error occurred in stored procedure uspLogError: '; EXECUTE [dbo].[uspPrintError]; RETURN -1; END CATCH END; "; #endregion /// <summary> /// Script to create delete trigger for our column /// </summary> public static string TriggerDeleteMarketEvents = @" create TRIGGER [dbo].[dMarketEventsCode] ON [dbo].[MarketEvents] AFTER delete AS BEGIN DECLARE @Count int; --=================================================== -- After Trigger for delete -- Trigger type is after so this will happen after all update operation is done. --=================================================== SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY -- Insert record into CacheTrack if not exists, otherwise update that If NOt exists (select CacheTrackId from CacheTrack where tablename = 'MarketEvents') INSERT INTO dbo.CacheTrack (TableName ,UpdateTime ) SELECT 'MarketEvents' ,Getutcdate() ; ELSE UPDATE dbo.CacheTrack SET UpdateTime = Getutcdate() where TableName = 'MarketEvents'; END TRY BEGIN CATCH EXECUTE [dbo].[uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [dbo].[uspLogError]; END CATCH; END; "; public static string TriggerUpdateMarketEvents = @" CREATE TRIGGER [dbo].[uMarketEventsCode] ON [dbo].[MarketEvents] AFTER UPDATE,INSERT AS BEGIN DECLARE @Count int; --=================================================== -- After Trigger to detect changes for Campaign. we need to detect those to populate cache -- Trigger type is after so this will happen after all update operation is done. --=================================================== SET @Count = @@ROWCOUNT; IF @Count = 0 RETURN; SET NOCOUNT ON; print 'uMarketEventsCode fired'; BEGIN TRY --DEBUG ----select 'inserted',* from inserted ----select 'deleted',* from deleted ----select i.Campaign FROM Inserted i ---- LEFT JOIN Deleted d ON i.MarketEventId = d.MarketEventId ---- WHERE ISNULL(i.Campaign, '<some null value>') != ISNULL(d.Campaign, '<some null value>') --Update only if column data changes --Update function does not care if column data changed or not,It returns true if statement contains this column --We can check previous value to make sure it is really a change on this column. If it is an insert op, it does not have deleted record --Dont forget that trigger's inserted or deleted may contain multiple rows. It fires for all the inserts,updates.. IF UPDATE(Campaign) and exists(select i.Campaign FROM Inserted i LEFT JOIN Deleted d ON i.MarketEventId = d.MarketEventId WHERE ISNULL(i.Campaign, '<some null value>') != ISNULL(d.Campaign, '<some null value>')) -- Insert record into CacheTrack if not exists, otherwise update that BEGIN print 'uMarketEventsCode add record'; If NOt exists (select CacheTrackId from CacheTrack where tablename = 'MarketEvents') INSERT INTO dbo.CacheTrack (TableName ,UpdateTime ) SELECT 'MarketEvents' ,Getutcdate() ; ELSE UPDATE dbo.CacheTrack SET UpdateTime = Getutcdate() where TableName = 'MarketEvents'; END; END TRY BEGIN CATCH EXECUTE [dbo].[uspPrintError]; -- Rollback any active or uncommittable transactions before -- inserting information in the ErrorLog IF @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END EXECUTE [dbo].[uspLogError]; END CATCH; END;"; public static string ProcGetCacheUpdateTime = @" create procedure [dbo].[sp_GetCacheUpdateTime] @tablename nvarchar(50) as select UpdateTime from dbo.CacheTrack where tablename = @tablename "; protected override void Seed(MarketContext context) { //some setup context.Database.ExecuteSqlCommand("SET ANSI_NULLS ON"); context.Database.ExecuteSqlCommand("SET QUOTED_IDENTIFIER ON"); context.Database.ExecuteSqlCommand("SET NOCOUNT ON"); //DATA SEEDING add company context.Companys.Add(new Company() {Name = "ABC Inc"}); //add user context.Users.Add(new User() { CreatedOn = DateTime.UtcNow, LoginMethod = 0, ModifiedOn = DateTime.UtcNow, RefKey = null, Username = "tes" }); context.SaveChanges(); try { //Create error log tables and procs context.Database.ExecuteSqlCommand(TableErrorLog); context.Database.ExecuteSqlCommand(ProcPrintError); context.Database.ExecuteSqlCommand(ProcLogError); //Ok we will create our cache track table first context.Database.ExecuteSqlCommand(TableCacheTrack); //triggers on our marketevent table context.Database.ExecuteSqlCommand(TriggerDeleteMarketEvents); context.Database.ExecuteSqlCommand(TriggerUpdateMarketEvents); //proc to get cache update time context.Database.ExecuteSqlCommand(ProcGetCacheUpdateTime); } catch (Exception ex) { //delete that if fails, so that you dont need to manually remove broken tables or procs instead of manual cleanup context.Database.Delete(); throw ex; } } }
Now register this at Global.asax
protected void Application_Start() { AreaRegistration.RegisterAllAreas(); RegisterGlobalFilters(GlobalFilters.Filters); RegisterRoutes(RouteTable.Routes); //REGISTER our init class This is for DbContext API (EFv4.1) Database.SetInitializer<MarketContext>(new MyInitializer());
}
Our initializer inherits from DropCreateDatabaseIfModelChanges ,so it will check metadata for model changes. If hashcode is different, it will drop and create db. Hashcode is inside additinal metadata table. You can use different initializers. if you ctr+click over that class, you can see other related classes. This example shows you how to use data seed, call stored procs and add triggers to EF code first. Data initializers are good way to start your db with some seed data for testing. You dont need to drop databse at data init. Data init calls will happen when it is initializing your db. You can include all stored procs and seed data into initializer project and keep everything under source control. Third party tools are best for migrations if you are using Code first for production system.
You can download code sample from https://github.com/omercs/CacheMvc
Very good post,thanks man.This work in MVC 5 , EF 6.
ReplyDelete