The short example about Entity Framework 4 CTP4 - Part 3

Part 3: Invoke store procedure in ADO.NET Entity Framework 4

  • Come with Entity Framework extensions

Go to http://code.msdn.microsoft.com/EFExtensions/Release/ProjectReleases.aspx?ReleaseId=3624. And getting a source code of Entity Framework extensions, you must build it and get the dll with named EFExtensions.dll

  • Talk about Materializer and DbCommand

We need a Materializer for build a static function that mapping a model contract with the result return from the store procedure. And we need the DbCommand for executing a store procedure. That is all things I knew about it. For more information, go to http://blogs.msdn.com/b/meek/archive/2008/03/26/ado-entity-framework-stored-procedure-customization.aspx. I think a expert at here will explain easily understanding than me. The theory is enough; I should focus on my work.

  • Build Contract Model for storing result that return from Store-Proc

I must build the Contract Model for mapping result that invoke the store procedure

public class SumarizeCategoriesAndNews
    {
        /// <summary>
        /// Gets or sets the category id.
        /// </summary>
        /// <value>The category id.</value>
        public int CategoryId { get; set; }
 
        /// <summary>
        /// Gets or sets the name of the category.
        /// </summary>
        /// <value>The name of the category.</value>
        public string CategoryName { get; set; }
 
        /// <summary>
        /// Gets or sets the category created date.
        /// </summary>
        /// <value>The category created date.</value>
        public DateTime CategoryCreatedDate { get; set; }
 
        /// <summary>
        /// Gets or sets a value indicating whether [category is delete].
        /// </summary>
        /// <value><c>true</c> if [category is delete]; otherwise, <c>false</c>.</value>
        public bool CategoryIsDelete { get; set; }
 
        /// <summary>
        /// Gets or sets the new id.
        /// </summary>
        /// <value>The new id.</value>
        public int NewId { get; set; }
 
        /// <summary>
        /// Gets or sets the news title.
        /// </summary>
        /// <value>The news title.</value>
        public string NewsTitle { get; set; }
 
        /// <summary>
        /// Gets or sets the content of the news.
        /// </summary>
        /// <value>The content of the news.</value>
        public string NewsContent { get; set; }
 
        /// <summary>
        /// Gets or sets the news created date.
        /// </summary>
        /// <value>The news created date.</value>
        public DateTime NewsCreatedDate { get; set; }
 
        /// <summary>
        /// Gets or sets a value indicating whether [news is delete].
        /// </summary>
        /// <value><c>true</c> if [news is delete]; otherwise, <c>false</c>.</value>
        public bool NewsIsDelete { get; set; }
    }
  • Re-code the DbContext for mapping store procedure

Next, I built the CSPDbContext partial class with named CSPDbContext.StoreProc and its code here:

    public partial class CSPDbContext
    {         
        private static readonly Materializer<SumarizeCategoriesAndNews> s_sumarizeCatAndNewsMaterializer = new Materializer<SumarizeCategoriesAndNews>(r =>
            new SumarizeCategoriesAndNews
            {
                CategoryId = r.Field<int>("CategoryId"),
                CategoryName = r.Field<string>("CategoryName"),
                CategoryCreatedDate = r.Field<DateTime>("CategoryCreatedDate"),
                CategoryIsDelete = r.Field<bool>("CategoryIsDelete"),
                NewId = r.Field<int>("NewId"),
                NewsTitle = r.Field<string>("NewsTitle"),
                NewsContent = r.Field<string>("NewsContent"),
                NewsCreatedDate = r.Field<DateTime>("NewsCreatedDate"),
                NewsIsDelete = r.Field<bool>("NewsIsDelete"),
            });
 
        /// <summary>
        /// Gets the sumarize category and news.
        /// </summary>
        /// <returns></returns>
        public SumarizeCategoriesAndNews GetSumarizeCategoryAndNews()
        {
            // The CreateStoreCommand utility method simplified creation
            DbCommand command = this.ObjectContext.CreateStoreCommand(
                "SumarizeCategoryAndNews",
                CommandType.StoredProcedure,
                null);
            SumarizeCategoriesAndNews catAndNews = s_sumarizeCatAndNewsMaterializer
                .Materialize(command)   // Returns typed results given a DB command.
                .SingleOrDefault();     // We expect at most a single match for this stored procedure.
 
            return catAndNews;
        }
    }

As you see, I invoked the store-proc with named [SumarizeCategoryAndNews]. I think it is clear with anyone that read this code.

And the Repository, we only need called it as:

        public SumarizeCategoriesAndNews GetSumarizeCategoriesAndNews()
        {
            Contract.Assert(DbContext != null, "DbContext is null");
 
            var temp = DbContext as CSPDbContext;
            return temp.GetSumarizeCategoryAndNews();
        }

 

Finally I do unit testing for this function:

         [TestMethod()]
        public void GetSumarizeCategoriesAndNewsTesting()
        {
            var result = CategoryRepository.GetSumarizeCategoriesAndNews();
 
            Assert.IsNotNull(result);
            Assert.AreEqual(result.CategoryName, "test");
            Assert.AreEqual(result.CategoryIsDelete, false);
        }
Notes:
  • You must run the Databasescript.sql to created the store-proc after you created successful the database schema
  • You must set the command 
    Database.SetInitializer<CSPDbContext>(newRecreateDatabaseIfModelChanges<CSPDbContext>());
    //Database.SetInitializer<CSPDbContext>(new DontTouchMyDatabase<CSPDbContext>()); 
    at Testing base class for created new database schema
More developing:
  • Intergrated with IoC container (Unity 2.0, Structure Map, MEF,..) 
  • Do more Unit Testing
Read further:
  • Data Annotation on EF4 CTP4
  • WCF ADO.NET Data Service based on EF4 CTP4 
Part 1: Step up ADO.NET Entity Framework 4 CTP4
Part 2: Repository and Unit of Work on Entity Framework 4

6 Comments

  • You definitely need an IoC here

  • Yeah, I agreed with you about that. This example, I only tried making it simple, so I didn't integrated IoC container here. Thank for your comment, Frank.

  • What's the point of efextensions again?

  • Isn't it already supported? As in social.msdn.microsoft.com:80/Forums/en/adonetefx/thread/632ff459-08f3-42d5-9407-60a17b593c74 here? Just want to hear your thoughts.

  • Nice articles, I read from Part I to III. Your scenario is much simple compare with Northwind, when I use CTP4 with Northwind, I meet some mapping propblems.

    1. How do I use the fluent API map the employee table?
    2. How do I use the fluent API implement many to many mapping, for example Employee to Territories?

    I am really appreciate if you can provide a tip to solve the problem.
    thanks

    andy

  • @Andrew: I just posted the new post at here for your request http://weblogs.asp.net/thangchung/archive/2010/08/03/the-short-example-about-ef4-ctp4-using-dataannotation-configuration.aspx . Regards

Comments have been disabled for this content.