Archive

Archive for the ‘Entity Framework’ Category

Table Splitting in Entity Framework

October 15, 2015 2 comments

In my previous post I covered the Entity Splitting technique and in this post I am going to talk about just the opposite of that technique.

Table Splitting:- In this scenario there are multiple entities or domain objects but the data is stored in a single table. Lets say in our database we have an employee table which stores basic information like first name, last name, DOB as well their photo. However in our domain model we want to create two different entities.

Lets write our domain objects to see this is action.

Employee.cs

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public DateTime DOB { get; set; }
    public virtual EmployeePhoto Photo { get; set; }
}

EmployeePhoto.cs

public class EmployeePhoto
{
    public int Id { get; set; }
    public byte[] Photo { get; set; }
    public Employee Employee { get; set; }
}

As you can see we have define two entities Employee and EmployeePhoto in our domain model and we have declared EmployeePhoto as virtual, with this declaration we can take advantage of lazy loading and when we query the Employee entity through EF the photo column will not be selected as part of the linq query. Second it also indicates that EmployeePhoto entity cannot exist without an Employee. It is also important that EmployeePhoto has a reference of Employee entity and has an Id properties which we will use to map the two entities into one table.

Lets wire up the relationship in our context class as shown below.

EmployeeContext.cs

public class EmployeeContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
    public DbSet<EmployeePhoto> Photos { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<EmployeePhoto>().HasKey(p => p.Id);

        modelBuilder.Entity<Employee>()
            .HasRequired(e => e.Photo)
            .WithRequiredPrincipal(p => p.Employee);

        modelBuilder.Entity<EmployeePhoto>().ToTable("Employee");
        modelBuilder.Entity<Employee>().ToTable("Employee");
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

We first defined the secondary entity( Employee Photo ) that and state that it has an identity key which is identical in our main entity. Next in our Employee entity we defined that the photo property is required with the Employee as the primary entity and then using ToTable function we map both the entity to the same table i.e. Employee.

Advertisements

Entity Splitting in Entity Framework

September 30, 2015 1 comment

I have done a lot of post on Entity Framework Code First which includes some simple tips and tricks to more complex scenarios of mapping relationship and here are some of those post if you want to read them.

Entity Framework Tutorial

However recently I was helping a friend of mine to understand Entity Framework modelling for a legacy system. Since the schema is already defined it becomes a bit difficult to create a rich domain model keeping the model in your design. The other thing I have seen is that people simply follow the schema and let schema dictating what your domain is, which in my opinion is not correct.

Anyways, after some discussion I realized that they are not taking advantage of Entity Splitting in Entity Framework, so I thought about blogging it.

Entity Splitting:- In this scenario there is a single entity or domain object but the data is stored in multiple tables with a one to one relationship. For example lets say the domain is a fitness/health industry and you have customer table which stores basic information like first name, last name, DOB in the customer table and other vital stats like resting heat beat,blood type,cholesterol level, blood pressure, Sugar level etc. in their health information table. However in your domain model the customer object is composed of both information.

Lets see this in action.

Customer.cs

public class Customer
{
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public DateTime DOB { get; set; }

    public int RestingHeartBeat { get; set; }

    public string BloodType { get; set; }

    public decimal Cholesterol { get; set; }

    public string BloodPressure { get; set; }
}

So this is my domain object which represents the whole customer object however in the legacy system some information is stored in the Customer table and some in the VitalStats table.

And lets write the configuration for customer in which we will split the entity into two different tables using the Map and ToTable function as shown below.

CustomerConfiguration.cs

public class CustomerConfiguration : EntityTypeConfiguration<Customer>
{
    public CustomerConfiguration()
    {
        this.Map(c =>
       {
           c.Properties(p => new
           {
               p.Id,
               p.FirstName,
               p.LastName,
               p.DOB
           });
           c.ToTable("Customer");
       })
        .Map(v =>
       {
           v.Properties(p => new
           {
               p.Id,
               p.BloodPressure,
               p.BloodType,
               p.Cholesterol,
               p.RestingHeartBeat
           });
           v.ToTable("VitalStats");
       });
    }
}

And here is my DbContext class.

public class HealthContext : DbContext
{
    DbSet<Customer> Customer { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
    }
}

Entity Framework 7: In Memory Testing

August 31, 2015 2 comments

I am really excited about the in-memory testing feature of Entity Framework 7 as testing in the previous versions of Entity Framework wasn’t straight forward and painful at times .

Let’s get started by installing the Entity Framework “InMemory” testing nuget package and make sure you have selected the “Include Prerelease” in the nuget dialogue as Entity Framework 7 is still in beta 6.

install ef7 pre release using nuget

Alternatively the old-fashioned way

PM>Install-Package EntityFramework.InMemory -Prerelease

 

As you can see that when we install the EntityFramework.InMemory package it also brings the EntityFramework.Core package.These are some of the new changes to Entity Framework as EF has been decomposed into smaller manageable packages.

Lets start writing our domain objects and I am going to use the simple example of Student and Course as show below.

Student.cs

public class Student
{
    public Student()
    {
        Courses = new HashSet<Course>();
    }
    public int Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public ICollection<Course> Courses { get; set; }

    public override string ToString()
    {
        return 
            string.Format(
                    "Id = {0}, FirstName = {1}, LastName = {2}", 
                    this.Id, 
                    this.FirstName, 
                    this.LastName);
    }
}

Course.cs

public class Course
{
    public int Id { get; set; }

    public string Name { get; set; }

    public string Description { get; set; }

}

Next we will define the database context and this is where you will see a significant change to the previous version. In the constructor of our context we are creating a dependency on DbContextOptions which will decide which source to use to persist the data.

TrainingContext.cs

public class TrainingContext : DbContext
{
    public TrainingContext(DbContextOptions options)
        : base(options)
    {
    }

    public DbSet<Student> Student { get; set; }

    public DbSet<Course> Course { get; set; }
}

Now we will wire up the TrainingContext to the main program and using the DbContextOptionsBuilder we will instruct the entity framework to use the in-memory database. I also created a separate method to populate the student table with 2 records.

Program.cs

class Program
{
    static void Main(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<TrainingContext>();
        optionsBuilder.UseInMemoryDatabase(true);

        using (var context = new TrainingContext(optionsBuilder.Options))
        {
            AddStudentData(context);

            var student = context.Student.FirstOrDefault(x => x.Id == 1);

            Console.WriteLine(student.ToString());

            student.FirstName = "James";
            context.SaveChanges();

            student = context.Student.FirstOrDefault(x => x.Id == 1);

            Console.WriteLine(student.ToString());

            Console.ReadLine();

        }
    }

    private static void AddStudentData(TrainingContext context)
    {
        context.Student.Add(new Student { Id = 1, FirstName = "Joe", LastName = "Blogg" });

        context.Student.Add(new Student { Id = 2, FirstName = "Jane", LastName = "Doe" });

        context.SaveChanges();
    }
}

As you can see we first retrieve the student with id 1 then we changed the first name of the student and asked entity framework to save the changes into the in-memory database.

And below is the output of the before and after state of that record.

Saved changes output to the console window

Getting Started with Entity Framework 5

August 30, 2012 4 comments

I finally took the plunge and installed Visual Studio 2012 and Entity Framework 5.Before I delve into any EF 5 code a few things I want to clarify about it.

Entity Framework 5 only comes with Visual Studio 2012 and you can’t get it using Visual Studio 2010, even if you try to use Nuget package manager it will install Entity Framework 4.4 but will report that it has installed Entity Framework 5.0

So if you run the following command

PM> Install-Package EntityFramework

and look into the version property of Entity Framework assembly it will still be 4.4.0.0 and that was a bit of a gotcha for me.

I recommend you install Visual Studio 2012 Web Express if VS2012 is not available and currently there is no Visual Studio Express Edition for Windows 7 so no “Console Application” for the time being.

I think this must be some sort of marketing strategy from Microsoft to force users to upgrade to Windows 8 but in my opinion it is not a good thing. I work mainly on web technologies so it does not matter much to me but out there many people who would like to play with it and write some simple console application and they can’t.

Another thing which was sore to my eyes was the light color visual theme for visual studio it really looks ugly and I recommend change it from light to dark by doing Tools > Options > Environment > General > Visual Experience , trust me it will look and feel much nicer.

Visual Studio 2012 Web Express with Dark theme

See i told it does looks nice and shiny and since I had worked with Expression blend and Sketch flow in the past I actual like this theme.So enough of the intro and lets get back to EF 5

Let’s run the Install package command from VS 2012 as EF is hard wired up with .Net Framework 4.5.

Entity Framework installed from Nuget package manager

So the feature I am going to explore today is a much awaited feature and that is the support for enumerations and I will use a simple example of a Real Estate Property
as shown below.

Real Estate Property Class Diagram

As you can see a I have a domain object called Property which holds information of a real estate property.It also two enumeration attributes which are 1) The type of property 2) The Energy rating for a particular property.And this is how the code looks like.

Property.cs

public class Property
{
    public int Id { get; set; }
    public string AddressLine1 { get; set; }
    public string AddressLine2 { get; set; }
    public PropertyType PropertyType { get; set; }
    public EnergyRating EnergyRating { get; set; }
    public string Suburb { get; set; }
    public string State { get; set; }
    public string ZipCode { get; set; }
}

PropertyType.cs

public enum PropertyType
{
    Residential,
    Commercial,
    Governmental
}

PropertyType.cs

public enum EnergyRating
{
    Poor = 0,
    Average = 1,
    Good = 2,
    Excellent = 3
}

And lets write a unit test to see whether we can create the database or not.

[TestMethod]
public void Should_be_able_to_create_RealEstateContext_database()
{
    var context = new RealEstateContext();
    context.Database.Initialize(true);
}

So far so good and when you inspect the database tables you will see the tables have int as their data type for the enum columns,lets go ahead and add some data into the table.

[TestMethod]
public void Should_be_able_to_add_properties()
{
    var property1 = new Property
    {
        AddressLine1 = "333 Baker Street",
        PropertyType = PropertyType.Residential,
        EnergyRating = EnergyRating.Excellent,
        Suburb = "Suburb1",
        State = "ACT",
        ZipCode = "2100"
    };

    var property2 = new Property
    {
        AddressLine1 = "444 Laker Street",
        PropertyType = PropertyType.Commercial,
        EnergyRating = EnergyRating.Average,
        Suburb = "Suburb2",
        State = "ACT",
        ZipCode = "2110"
    };

    var property3 = new Property
    {
        AddressLine1 = "404 Not Found Street",
        PropertyType = PropertyType.Governmental,
        EnergyRating = EnergyRating.Excellent,
        Suburb = "Suburb2",
        State = "ACT",
        ZipCode = "2120"
    };

    var context = new RealEstateContext();
    context.Property.Add(property1);
    context.Property.Add(property2);
    context.Property.Add(property3);

    context.SaveChanges();
}

And as you can see all the three new property data is added to the database.Now lets write another test to retrieve this data using some LINQ and see if it all comes through.

[TestMethod]
public void Should_be_able_filter_by_energy_rating()
{
    var context = new RealEstateContext();
    var query = from p in context.Property
                where p.EnergyRating == EnergyRating.Excellent
                select p;
    var result = query.ToList();
    Assert.IsTrue(result.Count > 0);
}

As you can see I am trying to retrieve properties which have an energy rating of excellent and this test also passes through flying green color.

Entity Framework takes care of all the mapping, storing and retrieving of enum types.It also support both specified values or just as a type enums. For example in the above 2 enums theenergy rating type had specified values and EF 5 worked it out automatically how to map this values and store them in the database.

Below is the returned data in the watch window.

All the properties with Energy Rating Excellent

Entity Framework: Viewing SQL

July 16, 2012 1 comment

In this post I am going to show you a cool trick I learned regarding ObjectQuery class and how it can be useful when working with Entity Framework.

In order to see what SQL statement Entity Framework is executing we generally tend to run the SQL profiler and set custom filters like the database name or id, the current user under which our code is going to execute the linq query etc so that we can isolate our query execution.

I find this approach very ugly as you have to start-stop your profiler trace in order to capture the exact time when the SQL is executed and in multi developer environment when everyone is developing against the same database it becomes quite challenging.

Well it’s not painful if we use a better approach using the ObjectQuery class and an extension method which extends the IQueryable interface. So here is the code for the ObjectQuery Extension class.

ObjectQueryExtension.cs

public static class ObjectQueryExtension
{
    public static string ToTraceString(this IQueryable t)
    {
        var objectQuery = t as ObjectQuery;
        return objectQuery != null ? objectQuery.ToTraceString() : 
                                     String.Empty;
    }
}

So now I’ll generate the EF Model from the publisher database and the entities I am interested in are Author and Title as shown below.

EF Publisher Data Model

and the following code to display the SQL statement in the console window.

Program.cs

class Program
{
    static void Main(string[] args)
    {
        using (var context = new PublisherContext())
        {
            var authorId = "998-72-3567";
            var query = context.Author
                            .Where(x=>x.au_id == authorId)
                            .SelectMany(ta=> ta.titleauthors,(au,ti)
                            => new {
                                    au.au_id,
                                    au.au_fname,
                                    au.au_lname,
                                    ti.title
                                    });

            var sql = query.ToTraceString();
            Console.WriteLine("Generated SQL is :");
            Console.WriteLine(sql);
            Console.ReadKey();
        }
    }
}

and this is how the output looks like.

SQL Output to Console Window

As you can see this small extension methods has lots of potential as you can use it along with logging frameworks like log4net and output the SQL to a file.

EF Code First:Executing Stored Procedure

May 10, 2011 8 comments

In this post I am going to show you how to use stored procedure with Entity Framework 4.1 even though it is official that Entity Framework 4.1 doesn’t support stored procedure and this was announced by the ADO.NET team at the time of the release. What it means that you can’t write stored procedure which return different data structure other than your entity wrapped in with DbSet and currently there is no way to map it a different entity which is not a database table.

Anyway without going into much details that why this feature didn’t make it to its first release, lets focus on how we can execute stored procedure which maps to a table.

I recommend you read my previous post which explains the idea behind POCOContextScript class, so we will add 2 more constants which has the DML for the stored procedures.

POCOContextScript.cs

public static class POCOContextScript
{
   public const string CreateProcGetAllProducts
      = @"create procedure [dbo].[GetAllProducts]
            as
            begin
                set nocount on;
                select * from product
            end";

   public const string CreateProcGetProductById
      = @"create procedure [dbo].[GetByProductId]
             @productid int
           as
           begin
               set nocount on;
               select * from product p
               where p.id = @productid
           end";
}

Nothing special just 2 sql procedure statement to get all products and get a specific product by id. Now we will follow the way we used in the previous post we can hook this into a class which implements the IDatabaseInitializer interface and lets run a unit test to see how we can invoke them.

GetAllProduct

[TestMethod]
public void Should_be_able_to_execute_sql()
{
   var context = new POCOContext();
   var result = context.Product.SqlQuery("GetAllProducts").ToList();
   Assert.IsTrue(result.Count > 0);
}


GetByProductId

[TestMethod]
public void Should_be_able_to_get_product_by_id()
{
   var context = new POCOContext();
   var parameter = new SqlParameter
   {
      DbType = DbType.Int32,
      ParameterName = "ProductId",
      Value = 1
   };

   var result = context.Product.SqlQuery
                                ("GetByProductId @ProductId",
                                   parameter
                                ).FirstOrDefault();

   Assert.AreEqual(1, result.Id);
}

That’s it for now and I hope the ADO.Net team is working on this feature for the next release and along with bulk copy and other features the next version will be even better.

EF Code First:Running SQL scripts

In this post I am going to show you how to run SQL scripts in Entity Framework 4.1 as we have seen that it’s quite easy to define our database entities using the DBSet and all the relationship using the EntityTypeConfiguration class.

The best part of this whole infrastructure classes are that they have pretty simple and clean interface which promotes writing “Clean Code” and have a nice “Single Responsibility Principle” and “Separation of concern”.

In order to run script as part of your database initialization you have to simply implement the IDatabaseInitializer interface and wire it up with your database context object.For example after your database tables are initialized and relationship set you want to create an unclustered index on a particular table and I will show you how we use the above interface to do the job.

Since we are dealing with scripts it means we will have to either write them as string literal or add them as SQL files to your project and somewhere in your project start-up read the script files. I will use the string literal approach for illustration and feel free to choose what ever approach works better for you.

In order to minimize the “Magic String” problem I am going to wrap it inside a static class as constant so that it is easy to call,maintain and unit test.
So this is how the code looks like.


POCOContextScript.cs

public static class POCOContextScript
{
   public const string CreateUnClusteredIndexOnProduct =
   @"if  exists (select * from sys.indexes
    where object_id = object_id('[dbo].[product]')
    and name = 'product_price_un_clustered_index')
    drop index [product_price_un_clustered_index] on
   [dbo].[product] with ( online = off )
    go
   create nonclustered index [product_price_un_clustered_index]
    on [dbo].[product]
    (
       [price] asc
    )
    with (pad_index  = off, statistics_norecompute  = off,
   sort_in_tempdb = off,ignore_dup_key = off, drop_existing = off,
   online = off, allow_row_locks  = on,allow_page_locks  = on)
   on [primary]
   go";
}

and now we write our DbInitializer class which will implment the IDatabaseInitializer interface.

DbInitializer.cs

public class DbInitializer : IDatabaseInitializer<POCOContext>
{
   public void InitializeDatabase(POCOContext context)
   {
      context.Database.SqlCommand (
      POCOContextScript.CreateUnClusteredIndexOnProduct
       );
   }
}

 

Nothing special here just calling the SqlCommand method of the Database object and since we wrapped our SQL inside our static class we can simply refer to the constant “CreateUnClusteredIndexOnProduct” as if it’s a property of POCOContext class.

Lets write a unit test to see how we are going to invoke and wire up rest of the stuff.

UnitTest.cs

[TestMethod]
public void Should_be_able_to_initialize_database()
{
   var dbInitializer = new DbInitializer();
   DbDatabase.SetInitializer(dbInitializer);
   var target = new POCOContext();
   target.Database.Initialize(true);
}

As you can I am creating a new instance of our DbInitializer class and passing that object to SetInitializer method of DbDatabase object provided by Entity Framework 4.1 and when I call the initialize method of my POCOContext instance the script gets executed and the unclustered index gets created.

In a large scale real life project you probably will have many configuration class and database initializer class and the above could should be wired up with a BootStrapper class and should use an IoC.