Sunday, 29 March 2015 00:00

Entity Framework Composite Primary Key

Entity Framework Composite Primary Key

Entity Framework composite primary keys could be tricky to handle. Although I recommend avoid such conditions ,both defining the keys and referencing that Class in another class are discussed in this tutorial. Let’s consider an example, my favorite Hotel case, where you have three classes: Guest, Reservation and Room. Reservation Model needs to have a composite primary key, Guest Id and Date of reservation. Room class is actually a middle table and keeps track of the Guest’s room for a specific reservation thus it needs a foreign key to the reservation table. Classes’ codes are as follow:

    public class Guest

    {

        public int Id { get; set; }

        public string FirstName { get; set; }

        public string SurName { get; set; }

        public DateTime DateOfBirth { get; set; }

        public byte Gender { get; set; }

        public int ReservationId { get; set; }

        public Reservation Reservation { get; set; }

    }

    public class Reservation

    {

        public DateTime Date { get; set; }

        public int GuestId { get; set; }

        public virtual Guest Guest { get; set; }

    }

    public class Room

    {

        public int RoomNumber { get; set; }

        public DateTime Date { get; set; }

        public int GuestId { get; set; }

        public Reservation Reservation { get; set; }

    }

 

Now we need to specify the multiple primary key for reservation class and multiple foreign key for the room class. We use fluent API to do this:

Composite Primary Key

    public class ReservationMap : EntityTypeConfiguration<Reservation>

    {

        public ReservationMap()

        {

            // Defining multiple primary key

            this.HasKey(t => new {t.Date,t.GuestId});

            this.HasRequired(t => t.Guest).WithMany();

        }

    }

 

Composite Foreign Key

    public class RoomnMap : EntityTypeConfiguration<Room>

    {

        public RoomnMap()

        {

            // Defining multiple primary key

            this.HasKey(t => new { t.RoomNumber,t.Date, t.GuestId });

            // Referencing Reservation Class

            this.HasRequired(t => t.Reservation);

        }

    }

 

Please note that Room Class also has Composite primary key. Room class has 2 fields which are the same name of those of Reservation class. This is the key point here, after we define the relationship with the Reservation class EF automatically uses these two fields for foreign keys.

Complex Primary Key/Foreign Key

The generated scripts by EF are like this:

CREATE TABLE [dbo].[Rooms] (

    [RoomNumber] INT      NOT NULL,

    [Date]       DATETIME NOT NULL,

    [GuestId]    INT      NOT NULL,

    CONSTRAINT [PK_dbo.Rooms] PRIMARY KEY CLUSTERED ([RoomNumber] ASC, [Date] ASC, [GuestId] ASC),

    CONSTRAINT [FK_dbo.Rooms_dbo.Reservations_Date_GuestId] FOREIGN KEY ([Date], [GuestId]) REFERENCES [dbo].[Reservations] ([Date], [GuestId]) ON DELETE CASCADE

);

 

 

GO

CREATE NONCLUSTERED INDEX [IX_Date_GuestId]

    ON [dbo].[Rooms]([Date] ASC, [GuestId] ASC);

 

Although we successfully created the relation and tables you should have noticed the design pitfall I deliberately put there. The Room class is redundant because you just want to assign the room number to a reservation and this easily can be done using the Reservation table (placing the RoomNumber in Reservation). This is actually what happens with most of the designs like this. As I said in the introduction I strongly recommend you avoid Multiple Primary/Foreign keys.

Published in Entity Framework
Wednesday, 18 February 2015 00:00

Repository Pattern | Generic Repository

Repository pattern

Repository pattern is a software engineering concept which in simple words can be seen as the warehouse of data in code. In fact every communication regarding invocation or persisting data is within repositories.

Repository and its benefits

Repositories are to help achieving separation of concerns; they help to put an abstraction layer between the logic of data persistence and other parts of the code.

Outcome of using repository pattern

  • The first outcome of using repository pattern is reaching maintainability.
  • The second outcome is that most of the time unit testing is possible with repositories in place
  • Finally the separation of concerns is leading to development speed where team members can work simultaneously without affecting each other.

Repositories are often used for CRUD operations. They are an interface between the endpoints for data --Database, files, XMLs, remote web services and etc-- and the classes which are dealing with the Data. For example if a class X is supposed to invoke “orders” of a customer it does not inquiry Database explicitly, it instead ask repository to give it the "orders". This abstraction provides an opportunity to separate the Data-Access code from other components. For example in this case repository can provide statically hard-coded orders in repository class, or read it from an XML file and the X class does not have any cognizance of what is going under the "order" invocation code layer.

Maintainability is the first and direct effect of using repositories. As stated in the above example the developer can change the logic of data access without much further tampering of other parts of the code. He or she may want to postpone the Data Access logic to the final phase of development and just go and implement other parts of the software with a hard coded data. Unit testing often needs a repository interface because they want to test part of a code independently and repository interfaces provide such functionality. For example for an “Update” operation a unit testing package binds the interface to a method which always returns a true result representing success operation. Last but not the least with repository in place one programmer can work on data access logic while another team member just invokes the repository interface method which is bind to a simple implementation. When Data access code is completed other team members bind the repository method to the real implementation.

Repositories provide an abstraction layer and this abstraction provides separation of concerns for Data access and other parts of the code. When we separate CRUD operations from other logics several outcomes will be achieved. Re-usability, Unit testing and fast development are the benefits of using repositories.

Increasing reusability using Generic Repository Pattern

In the previous section I talked about repository concept and its benefit. In this section I’m going to talk about a Repository Pattern which I developed it myself after analyzing concerns and practices I encountered during development. There are tons of documents out there explaining Generic-Repository patterns like this one. They have done a great job but still there are some limitations in those designs which led me to get my own hands dirty and modify a couple of things to reach perfectness.

Generic repository pattern advantages

  • Separating Persistence layer from Domain and logic i.e. you can later plugin another persistence layer easily
  • Make unit testing possible (an outcome of first concern)
  • Speed the development process by defining persistence code just once
  • Following reusability and accuracy as an inherent benefit of using Generic concept

Problems with the common repository pattern

To achieve SOP you should always remember no to lean on an ORM too much and write your codes as independent as possible. By its inherent concept, repositories do not depend on any specific ORM but I see a lot of developers tie their repositories to Entity Framework. That is a pitfall to use DataContext object everywhere in your repositories. As a matter of fact the references to DataContext should be minimal so when you want to switch to another ORM you avoid excess of headache. As long as you have just one Generic Interface and one Generic Repository you’re good but when you need to define a custom interface the problem arises. For example if you want to have a GetSuspendedUsers() method in the interface class to get all of the Suspended users and naturally calling it in the Controller class, you should extend Generic Interface.

    public interface IUserRepository : IGenericRepository<User>

    {

        IQueryable<User> GetSuspendedUsers ();

    }

 

And then implementing it like this:

    public class UserRepository : GenericRepository<User>, IUserRepository

    {

        public IQueryable<User> GetSuspendedUsers ()

        {

            return this.Context.Users.Where(a => a.Suspended);

        }

    }

 

There are at least two problems with this implementation. First there is a violation of SOP concept since you tried to access Context object and this reference causes pain in case of changing ORM.

Decoupling Generic Repository Pattern from Persistance layer 

The Correct way of implementing GetSuspendedUsers  method would be:

        public IQueryable<User> GetSuspendedUsers ()

        {

            return this.GetAll().Where(a => a.Suspended);

        }

 

This modification does not make the design perfect, because still you have not reached the maximum reusability. To clarify let’s elaborate on a case when you need to have same domain but with several persistence mechanisms. For instance you want to have a web application with SQL server and EntityFramework and a Desktop application with NHibernate and SQLLite. Well for the maximum reusability in this case you should avoid too much modification e.g. modifying the repository pattern two times to be prepared for these two ORMs. The problem becomes more colorful when you realize that your unit tests for Domain level depend on the implementation of generic repository and a violation of SOP shows itself. The solution to this problem would be to inject the IGenericRepository implementation to child repositories i.e. defining an IGenericRepository property in child repositories and injecting implementation in the Constructor:

    public class UserRepository : IUserRepository

    {

 

        public UserRepository(IGenericRepository<User> instance)

        {

            this.Implementedfunctions = instance;

        }

 

        public IGenericRepository<User> Implementedfunctions;

 

        public IQueryable<User> GetSuspendedUsers()

        {

            return this.GetAll().Users.Where(a => a.Suspended);

        }

 

        public IQueryable<User> GetAll()

        {

            return Implementedfunctions.GetAll();

        }

 

        public IQueryable<User> FindBy(Expression<Func<User, bool>> predicate)

        {

            return Implementedfunctions.FindBy(predicate);

 

        }

 

        public void Add(User entity)

        {

            Implementedfunctions.Add(entity);

        }

 

        public void Delete(User entity)

        {

            Implementedfunctions.Delete(entity);

        }

 

        public void Edit(User entity)

        {

            Implementedfunctions.Edit(entity);

        }

 

        public void Edit(User entity, params Expression<Func< User, object>>[] propsToUpdate)

        {

            Implementedfunctions.Edit(entity, propsToUpdate);

        }

 

        public void Save()

        {

            this.Implementedfunctions.SaveChanges();

        }

    }

 

Now your domain level is completely separated from persistence logic and you can unit test your functions independently. The great outcome of this pattern is that you can inject the persistence logic according to your needs in each project e.g. for the aforementioned example you can easily implement a Generic Repository with EF for your Web project and leave the Domain codes untouched and implement another with NHibernate and still your domain’s codes remain intact.

Entity Framework code first migration

Migration is one of the key features of Entity Framework and you strongly need it when you’re developing in “Code-First” style which is my favorite. From time to time however, it may irritate you by throwing exceptions.

Initial configuration

  1. Install Entity Framework
  2. Issue Enable-Migration command in Console Manager
  3. Set “AutomaticMigrationsEnabled” in your configuration file (created by Enable-Migration command) to “True”.
  4. Issue Update-Database command in Console Manager

Troubleshooting Entity Framework Migration

Troubleshooting process is actually straightforward but it may take time till you figure it how. Just follow these steps if you run to any problems:

  • Delete Data in tables which have been changed
  • Delete all the tables (including _MigrationHistory table) if previous step does not solve your problem.
  • Delete the Database if previous step does not solve your problem.

If you have all of your Data in the “Seed” method then just do the last step – Delete the database.

Published in Entity Framework
Advanced Programming Concepts
News Letter

Subscribe our Email News Letter to get Instant Update at anytime