Entity Framework and Database

By using Entity Framework, we can save a lot of time on writing SQL ourselves because Entity Framework, a Microsoft-supported ORM for .NET, is able to generate the SQL for us.

I started to use ADO .NET when I was building .NET web applications in my first job. I learnt about how to call stored procedures with ADO .NET. I witnessed how my colleague wrote a 400-line SQL to complete a task which we normally will choose to do it in C#. I also realized the pain of forgetting to update the stored procedure when the C# code is already different.

After that, my friend introduced me Entity Framework when I was working on my first ASP .NET MVC project. Since then, I have been using Entity Framework because it enables me to deliver my web applications faster without writing (and debugging) any SQL myself. I read a very interesting article comparing between Entity Framework and ADO .NET. The author also acknowledged that the performance of Entity Framework was slower than hand-coded ADO .NET. He emphasized that, however, Entity Framework did maximize his productivity.

How I react when I read a 400-line stored procedure submitted by my colleague.
How I react when I read a 400-line stored procedure submitted by my colleague.

What Is Happening in Database with Entity Framework?

The SQL generated by Entity Framework is believed to be pretty good. However, it’s still nice to be aware of what SQL is being generated. For example, I have the following code to retrieve Singapore weather info.

using (var db = new ApplicationDbContext())
{
    var forecastRecords = db.SingaporeWeathers.ToList();
}

In Visual Studio, I can just mouse-over “SingaporeWeather” to get the following query.

SELECT 
    [Extent1].[RecordID] AS [RecordID], 
    [Extent1].[LocationID] AS [LocationID], 
    [Extent1].[WeatherDescription] AS [WeatherDescription], 
    [Extent1].[Temperature] AS [Temperature], 
    [Extent1].[UpdateDate] AS [UpdateDate]
FROM [dbo].[SingaporeWeathers] AS [Extent1]

If I have the following code which retrieves only records having temperature greater than 37, then I can use ToString().

using (var db = new ApplicationDbContext())
{
    var query = from sw in db.SingaporeWeathers where sw.Temperature > 37 select sw;
    Console.WriteLine(query.ToString());
}
SELECT
     [Extent1].[RecordID] AS [RecordID],
     [Extent1].[LocationID] AS [LocationID],
     [Extent1].[WeatherDescription] AS [WeatherDescription]
     [Extent1].[Temperature] AS [Temperature],
     [Extent1].[UpdateDate] AS [UpdateDate]
FROM [dbo].[SingaporeWeathers] AS [Extent1]
WHERE [Extent1].[Temperature] > cast(37 as decimal(18))

I am using DBContect API, so I can just use ToString(). Alternatively, you can also use ToTraceString(), which is a method of ObjectQuery, to get the generated SQL.

SQL Logging in Entity Framework 6

It is a great news for developer when Entity Framework is announced to have SQL Logging feature added For example, to write database logs to a file, I just need to do as follows.

using (var db = new ApplicationDbContext())
{
    var logFile = new StreamWriter("C:\\temp\\log.txt");
    db.Database.Log = logFile.Write;
    var forecastRecords = db.SingaporeWeathers.Where(x => x.Temperature > 37).ToList();
    logFile.Close();
}

Then in the log file, I can see logs as follows.

...
Closed connection at 6/6/2015 10:59:32 PM +08:00
Opened connection at 6/6/2015 10:59:32 PM +08:00
SELECT TOP (1) 
    [Project1].[C1] AS [C1], 
    [Project1].[MigrationId] AS [MigrationId], 
    [Project1].[Model] AS [Model], 
    [Project1].[ProductVersion] AS [ProductVersion]
FROM ( SELECT 
    [Extent1].[MigrationId] AS [MigrationId], 
    [Extent1].[Model] AS [Model], 
    [Extent1].[ProductVersion] AS [ProductVersion], 
    1 AS [C1]
    FROM [dbo].[__MigrationHistory] AS [Extent1]
    WHERE [Extent1].[ContextKey] = @p__linq__0
) AS [Project1]
ORDER BY [Project1].[MigrationId] DESC
-- p__linq__0: 'MyWeb.Migrations.Configuration' (Type = String, Size = 4000)
-- Executing at 6/6/2015 10:59:32 PM +08:00
-- Completed in 70 ms with result: SqlDataReader

Closed connection at 6/6/2015 10:59:32 PM +08:00
Opened connection at 6/6/2015 10:59:32 PM +08:00
SELECT 
    [Extent1].[RecordID] AS [RecordID], 
    [Extent1].[WeatherDate] AS [WeatherDate], 
    [Extent1].[WeatherDescription] AS [WeatherDescription], 
    [Extent1].[WeatherSecondaryDescription] AS [WeatherSecondaryDescription], 
    [Extent1].[IconFileName] AS [IconFileName], 
    [Extent1].[Temperature] AS [Temperature], 
    [Extent1].[UpdateDate] AS [UpdateDate]
FROM [dbo].[Weathers] AS [Extent1]
WHERE [Extent1].[Temperature] > cast(37 as decimal(18))
-- Executing at 6/6/2015 10:59:33 PM +08:00
-- Completed in 28 ms with result: SqlDataReader
...

So, as you can see, even the Code First migration related activity is logged as well. If you would like to know what are being logged, you can read an article about SQL Logging in EF6 which was written before it’s released.

Migration and the Verbose Flag

Speaking of Code First migration, if you would like to find out the SQL being generated when Update-Database is executed, you can add a Verbose flag to the command.

Update-Database -Verbose

Navigation Property

“I have no idea why tables in our database don’t have any relationship especially when we are using relational database.”

I heard from my friend that my ex-colleague shouted this in the office. He left his job few days after. I think bad codes and bad design do anger some of the developers. So, how do we do “relationship” in Entity Framework Code First? How do we specify the foreign key?

I quit!
I quit!

In Entity Framework, we use the Navigation Property to represent the foreign key relationship inside the database. With Navigation Property, we can define relationship between entities.

If we have a 1-to-1 Relationship between two entities, then we can have the following code.

public class Entity1
{
    [Key]
    public int Entity1ID { get; set; }
    public virtual Entity2 Entity2 { get; set; }
}

public class Entity2
{
    [Key, ForeignKey("Entity1")]
    public int Entity1ID { get; set; }
    public virtual Entity1 Entity1 { get; set; }
}

By default, navigation properties are not loaded. Here, the virtual keyword is used to achieve the lazy loading, so that the entity is automatically loaded from the database the first time a property referring to the entity is accessed.

However, there are people against using virtual keyword because they claim that lazy loading will have subtle performance issue in the application using it. So, what they suggest is to use the include keyword, for example

dbContext.Entity1.Include(x => x.Entity2).ToArray();

By specifying the ForeignKey attribute for Entity1ID in Entity2 class, Code First will then create a 1-to-1 Relationship between Entity1 and Entity2 using the DataAnnotations attributes.

For 1-to-n Relationship, we then need to change the navigation property, for example, in Entity1 class to use collection as demonstrated in the code below.

public class Entity1
{
    [Key]
    public int Entity1ID { get; set; }
    public virtual ICollection<Entity2> Entity2s { get; set; }
}

Finally, how about n-to-m Relationship? We will just need to change the navigation property in both Entity1 and Entity2 classes to use collection.

public class Entity2
{
    [Key]
    public int Entity2ID { get; set; }
    public virtual ICollection<Entity1> Entity1s { get; set; }
}

Together with the following model builder statement.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Entity2>()
        .HasMany(e2 => e2.Entity1s)
        .WithMany(e1 => e1.Entity2s)
        .Map(e12 => 
            {
                e12.MapLeftKey("Entity1ID");
                e12.MapRightKey("Entity2ID");
                e12.ToTable("Entity12");
            });
}

The code above is using Fluent API which won’t be discussed in this post.

Database Context Disposal

When I first used Scaffolding in MVC 5, I noticed the template of controller class it generates look something as follows.

public class MyController : Controller
{
    private MyContext db = new MyContext();
    
    protected override void Dispose(bool disposing)
    {
        if (disposing) 
        {
            db.Dispose(); 
        } 
        base.Dispose(disposing);
    }
}

Before using Scaffolding, I have always been using the Using block, so I only create database context where I have to, as recommended in a discussion on StackOverflow. Also, the Using block will have the Dispose() be called automatically at the end of the block, so I don’t need to worry about forgetting to include the Dispose() method to dispose the database context in my controller.

Azure SQL: Database Backup and Restore

Before ending this post, I would like to share about how DB backup and restore is done in Azure SQL Database.

First of all, Azure SQL Database has built-in backups and even self-service point in time restores. Yay!

For each activate databases, Azure SQL will create a backup and geo-replicate it every hour to achieve 1-hour Recovery Point Objective (RPO).

If there is a need to migrate the database or archive it, we can also export the database from Azure SQL Database. Simply click on the Export button in the SQL Databases section of Azure Management Portal and then choose an Azure blob storage account to export the database to.

Finally, just provide the server login name and password to the database and you are good to go.

Export DB from Azure SQL Database.
Export DB from Azure SQL Database.

Later, we can also create a new database using the BACPAC file which is being generated by the Export function. In the Azure Management Portal, click New > Data Services > SQL Database > Import. This will open the Import Database dialog, as shown in the screenshot below.

Create a new database in Azure SQL Database by import BACPAC file.
Create a new database in Azure SQL Database by import BACPAC file.

Okai, that’s all for this post on Entity Framework, database, and Azure SQL Database. Thank you for your time and have a nice day!

Summer 2015 Self-Learning Project

This article is part of my Self-Learning in this summer. To read the other topics in this project, please click here to visit the project overview page.

Summer Self-Learning Banner

Advertisements

Summer 2015 Self-Learning

Summer Self-Learning
It has been about half a year since I started to learn ASP .NET MVC and Entity Framework (EF). In this period of time, I have learnt about not just MVC and EF, but also Azure PaaS, Google Maps API, web application security, cool jQuery plugins, Visual Studio Online, etc.

In the beginning of May, I started to note down useful things I’d learned in my learning journey. Months of bringing together information in this summer has helped me compile my notes about what I’ve learned in the past 6 months. I have currently completed compiling notes for 17 topics that I’ve learnt in this summer.

I listed down the title of the 17 posts below to give you a quick overview about all the 17 topics.

Contents

ASP .NET MVC and Entity Framework

Security

Microsoft Azure

Google APIs

Web Development Tools

Learning After Work

I’m working in Changi Airport. The office working hour is from 8:30am to 6pm. In addition, I am staying quite far from the airport which will take about one hour for me to travel from home to office. Hence, the only time that I can have sufficient time to work on personal projects is weekends.

This summer self-learning project is originally planned to be done by the end of May. Normally, it takes me about one day to finish writing a post. After that, if I find any new materials about the topics, I will then modify the post again. Sometimes, however, I am just too tired and I would not write anything even though it’s weekend. Hence, I end up finishing all the 17 topics three months later.

This summer learning project covers not only what I’ve learnt in my personal projects, but also new skills that I learn in my workplace. I always enjoy having a chat with my colleagues about the new .NET technology, app development, Azure hosting, and other interesting development tools. So yup, these 17 articles combine all the new knowledge I acquire.

I’m also very happy that that I am able to meet developers from both .NET Developers Community Singapore and Azure Community Singapore and share with them what I’ve learnt. That gives me a great opportunity to learn from those experienced .NET developers. =)

Azure Community March Meetup in Microsoft Singapore office.
Azure Community March Meetup in Microsoft Singapore office.

I am not that hardworking to work on personal projects every day. Sometimes, I will visit family and friends. Sometimes, I will travel with friends to overseas. Sometimes, I will play computer games or simply just sleep at home. So ya, this self-learning project takes a longer time to complete. =D

Working on personal projects after work is stressful also. Yup, so here is a music that helps reducing my stress. =)