Sony Arouje

In this post I am going to explain a small experiment I did with Entity Framework CTP4. My intention was to create some Entity classes and use it with the code first approach provided with EF4.

Till now I haven’t touched EF because I am not a big fan of auto generated code and config files generated by EF. When I saw some article about the new features of EF that comes with v4 release, especially the code first approach and POCO support. I thought of wetting my hands in EF 4. POCO support in EF 4 was really good, no auto generated codes, no edmx files, you can write pretty clean codes.

Let’s jump into the code. My project is not structured just very plain. Entities, Context and UI,.. every thing in single project. As I said this is just a tracer to see how things works.

Table Structure

CREATE TABLE [dbo].[User](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserCode] [varchar](10) NOT NULL,
    [UserName] [varchar](50) NOT NULL,
    [Password] [varchar](50) NOT NULL,
    [UserEmail] [varchar](50) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE[dbo].[ImageComment](
   [CommentId] [int] IDENTITY(1,1) NOT NULL,
   [CommentDescription] [varchar](max) NULL,
   [UserName] [varchar](50) NULL,
   [UserId] [int] NOT NULL,
   [CommentDate] [datetime] NOT NULL,
CONSTRAINT[PK_ImageComments] PRIMARY KEY CLUSTERED
(
   [CommentId] ASC
)WITH(PAD_INDEX =OFF,STATISTICS_NORECOMPUTE =OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS =ON,ALLOW_PAGE_LOCKS =ON)ON[PRIMARY]
)ON[PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE[dbo].[ImageComments]  WITH CHECK ADD  CONSTRAINT[FK_ImageComments_Users] FOREIGN KEY([UserId])
REFERENCES[dbo].[Users] ([ID])
GO

User.UserId is a foreign key in ImageComment.

Entity Classes

public class User
{
    public User()
    {
        this.Comments = new List<UserComment>();
    }

    [Key,StoreGenerated(StoreGeneratedPattern.Identity)]
    public int UserID { get; set; }
    public string UserCode { get; set; }
    public string UserName { get; set; }
    public string Password { get; set; }
    public string UserEmail { get; set; }
    public ICollection<UserComment> Comments { get; set; }
}

public class UserComment
{
    public UserComment() { }
    [Key, StoreGenerated(StoreGeneratedPattern.Identity)]
    public int CommentId { get; set; }
    public string Comment { get; set; }
    public virtual int UserId { get; set; }
    public virtual User User1 { get; set; }
    public DateTime CommentDate { get; set; }
}

Nothing much to explain just plain Entity classes. To add Key Attribute (just above the identity column) I added the System.ComponentModel.DataAnnotations;name space to the above classes. You might think that why I added Key Attribute to the indentity, the reason is EF4 POCO supports on Conventions. In some scenarios it may not identify the key property and will throw error “Unable to infer a key for entity type”. We need to attach [Key] to mark the identity key for EF.

Let’s see the Configuration classes.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using Microsoft.Data.Entity.Ctp;
using System.Data.Entity.ModelConfiguration;
namespace EntityFrameWorkTracer_CodeFirstModel
{
    public class UserConfigurationen:EntityConfiguration<User>
    {
        public UserConfigurationen()
        {
            Property(c => c.UserID).IsIdentity();
            Property(c => c.UserName).HasMaxLength(50).IsRequired();
            Property(c => c.UserCode).HasMaxLength(10).IsRequired();
            Property(c => c.Password).HasMaxLength(50).IsRequired();
            Property(c => c.UserEmail).HasMaxLength(50);
            this.HasMany(c => c.Comments);
            this.MapHierarchy(c => new
            {
                UserName = c.UserName,
                UserCode = c.UserCode,
                Password = c.Password,
                UserEmail = c.UserEmail,
                ID = c.UserID
            }).ToTable("Users");
        }
    }

    public class CommentConfiguration : EntityConfiguration<UserComment>
    {
        public CommentConfiguration()
        {
            Property(c => c.CommentId).IsIdentity();
            Property(c => c.Comment).HasMaxLength(5000);
            this.HasRequired(c => c.User1).WithMany(a => a.Comments).WillCascadeOnDelete().HasConstraint((c, y) => c.UserId == y.UserID);
            this.MapHierarchy(c => new
            {
                CommentId=c.CommentId,
                CommentDescription = c.Comment,
                UserId = c.UserId,
            }).ToTable("ImageComments");
        }
    }
}

The configuration class is the important piece of code here. This configuration class maps the Entity to the respective tables. We can also specify the meta property of the column like Required property, size of the field, etc.

You can see one important line of code in CommentConfiguration (marked in bold italics). It specifies the relation between UserComment and User table.

Object Context class

The derived object context class is used of Saving and Retrieving data from the db.

using System;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.Data.Objects;
usingSystem.Data.EntityClient;
namespaceEntityFrameWorkTracer_CodeFirstModel
{
    public classAWModel:ObjectContext
   {
        publicAWModel(EntityConnection connection)
            : base(connection)
        {

        }
        publicIObjectSet<User> User
        {
            get{ return base.CreateObjectSet<User>(); }
        }
    }
}

UI Code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.Entity.ModelConfiguration;
namespace EntityFrameWorkTracer_CodeFirstModel
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                SqlConnection con = new SqlConnection("Data Source=SQLserver;Initial Catalog=ImagePublisher;User ID=sony;PWD=sony;MultipleActiveResultSets=True;");

                var builder = new ModelBuilder();
                builder.Configurations.Add(new UserConfigurationen());
                builder.Configurations.Add(new CommentConfiguration());
                var model = builder.CreateModel();
               
                var context = model.CreateObjectContext<AWModel>(con);
                context.ContextOptions.LazyLoadingEnabled = false;
                var query = from c in context.User select c;
                foreach (var u in query)
                {
                    context.LoadProperty(u, "Comments"); //Explicity loads Comments property in User object.
                    MessageBox.Show(u.UserName); 

                    foreach (UserComment comment in u.Comments)
                    {
                        MessageBox.Show(comment.Comment); 
                    }
                }

                
                //Uncomment and see how data save will work
                //User usr = new User
                //{
                //    UserName = "Kevin",
                //    Password = "Kevin",
                //    UserEmail = "Kevin@gmail",
                //    UserCode = "gh"
                //};
                //context.User.AddObject(usr);
                //context.SaveChanges();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

    }
}

I struggled a lot to figure out how to load the related objects, like Comments in the User object. My architect forwared a MSDN link and solved my puzzle. From the link I got to know about the Context.LoadProperty and implemented, viola it worked.

CTP4 supports the above code only support of configuration, before that you have to provide the edmx file as the configuration. Download CTP4.

Let’s see how to achieve Many to Many relation ship in POCO model

Many to Many Relation ship

To experiment many to many relation ship I added a new table to my db, Role and UserRoles as shown below

I modified the User entity to add the below property.

public ICollection<Role> UserRoles { get; set; }

This property holds all the Roles associated to the User.

Role Entity is very straight forward as shown below

using System;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Text;
usingSystem.ComponentModel.DataAnnotations;
namespaceEntityFrameWorkTracer_CodeFirstModel
{
    public classRole
   {
        publicRole()
        {
            this.Users = newList<User>();
        }
        [Key]
        public int RoleId { get; set; }
        public stringRoleName { get; set; }
        publicICollection<User> Users { get; set; }
    }
}

The important piece of code is the RoleConfiguration class. Let see the configuration class

public classRoleConfiguration : EntityConfiguration<Role>
{
    publicRoleConfiguration()
    {
        Property(r => r.RoleId).IsIdentity();
        this.HasMany(r => r.Users).WithMany(u => u.UserRoles).Map(“dbo.UserRoles”, (role, user) =>new
        {
            RoleId=role.RoleId,
            UserId=user.UserID
        });
       
        this.MapHierarchy(r => new
       {
            RoleId=r.RoleId,
            RoleName=r.RoleName
        }).ToTable(“Role”);
    }
}

The code I marked in bold italics is our area of interest. Here I specified relationship between Role.Users and User.Roles. Also I specified the mapping table that holds the relation, here my mapping table is UserRoles. RoleId, RoleName, UserId I marked in big font is nothing but the columns in the respective tables in the db. It is a mapping between Table columns and entities property.