Entity Framework: Foreign Keys and Relationships

Foreign key

In the context of relational databases, a foreign key is a column or combination of columns in one table that uniquely identifies a row of another table in order to establish and enforce a link between the data in two tables.

In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table.

For example, a table called Customer has a primary key called CustomerID. Another table called Order has a foreign key which references CustomerID in order to uniquely identify the relationship between both the tables.

Relationships

There are three types of relationships: one-to-one, one-to-many, and many-to-many.

In a one-to-many relationship, the foreign key is defined on the table that represents the many end of the relationship.

The many-to-many relationship involves defining a third table (called a junction or join table), whose primary key is composed of the foreign keys from both related tables.

In a one-to-one relationship, the primary key acts as a foreign key and there is no separate foreign key column for either table.

In reality, one-to-one relationship is not frequently used because in this case usually you don’t need to create two separate tables. Instead you may consider merging them into one table. But there are some cases creating two tables may be better.

1)  It can be used for partitioning both logic and physic data

See this Stackoverflow post:
http://stackoverflow.com/questions/517417/is-there-ever-a-time-where-using-a-database-11-relationship-makes-sense

2) It can also used for improving efficiency in the case of 1 to 0..1 case
See this Stackoverflow post:
http://stackoverflow.com/questions/3939589/database-design-should-one-to-one-relationships-be-avoided?lq=1

Configuring Relationships with the Fluent API

This is the best article about this topic:
https://msdn.microsoft.com/en-us/data/jj591620

 

Entity Framework Jump Start With ASP.NET MVC and SQL Server

What is Entity Framework?

Entity Framework (EF) is an object-relational mapper(ORM) that enables .NET developers to work with relational data using domain-specific objects. It eliminates the need for most of the data-access code that developers usually need to write.

What ORM does is helping us take care of creating connections and commands, and execute them on database. When you are performing queries, it will take the results of the queries, read the data from them, create instances of your domain classes, and push the data into the instance. All of that repetitive work is taken care of by an ORM, in this case Entity Framework.

The key feature of ORM is the mapping it uses to bind an object to its data in the database. Mapping expresses how an object and its properties and behaviors are related to one or more tables and their fields in the database. An ORM uses this mapping information to manage the process of converting data between its database and object forms, and generating the SQL for a relational database to insert, update, and delete data in response to changes the application makes to data objects.

So instead of writing commands like this every time before EF:

String sql = “SELECT … FROM Users WHERE ID = 123”

DbCommand cmd = new DbCommand(connection, sql);

Result result = cmd.Execute();

String name = result [0][“NAME”];

You do something like this:

User user = User.Get(User.Properties.Id == 123);

string name = user.Name;

Why Entity Framework?

In additional to the benefits as an decent ORM framework has, EF also has two other advantages:

• It is the first class member in .Net stack. 
• We can work with Entity Framework using the consistent query syntax with LINQ to Entities.

Geting start with Entity Framwork 6

Environment: Visual studio 2015 Community + SQL Server 2014 + ASP.NET MVC 5  

1)  Create a Blank Solution called EfJumpStart.

2) Add a new Class Library called EfJumpStart.Domain. 

3)  Create our domain class. For example, here I create a class caled User. 

4) Add a new Class Library project called EfJumpStart.Data which is our data access layer. In this project, we need to Install lastest version of Entity Framework package.

After installation, you will see that we not only get two new package references, but also an App.config file. In the App.config, we can see that Microsoft SQL Server LocalDB is used by default. We will just use it for now. But later i will show you how to switch to Sql Server easily.

5) Now we will get into the important party of Entity Framework: DbContext.

DbContext is the primary class that is responsible for interacting with data as objects. The context class manages the entity objects during run time, which includes populating objects with data from a database, change tracking, and persisting data to the database. Once you have context, you would need to access DbSet in DbContext. DBSet class represents an entity set that is used for create, read, update, and delete operations. (Reference link)

Intuitively, a DbContext corresponds to your database (or a collection of tables and views in your database) whereas a DbSet corresponds to a table or view in your database. So it makes perfect sense that you will get a combination of both! You will be using a DbContext object to get access to your tables and views (which will be represented by DbSet’s) and you will be using your DbSet’s to get access, create, update, delete and modify your table data. (Reference link)

Now let’s create our DbContext. The recommended way to work with context is to define a class that derives from DbContext and exposes DbSet properties that represent collections of the specified entities in the context. (See following screenshot. )

6) Create a Database from the model using Code First Migration

First, we need to enable migration from Package Manager Console for EfJumpStart.Data project.

You can see that EF has generated a folder and configration class for us.

Now we will perform Code First Data Migration. It’s a three-steps process.

• Define/Change the model
• Create a migration file
• Apply the migration to Database

Since we already created a domain class called User. Then we can start to create a migration file.

The command is Add-Migration + MigrationName. Here I name the first migration as Initial.

After it is done, we can see that a migration file is auto-generated for us. It basically describes what needs to do for achieving current migration and reversing the migration. Since this is our first migration, it is about creating a new table. 

Now it is time to apply the migration with the changes described in the migration file using command update-database. It’s recommended that we add -verbose option in command. So we can see what this command has done behind scene.

From the screenshot above, you can see that it shows what SQL commands were executed during migration. And in the end, what it did was creating a migration history in the Database. SO next time when we try creating new migration file, the EF will compare the changes in the models with previous migration history to determine what changes needs to be done and generate new migration file for us.

Now we can go to see the new database has been created for us by EF.

7) It’s time to interact with data.

We can create a simple ASP.NET MVC 5 application. It contains a simple HomeController with a action called Index and its view called Index.cshtml. 

First, Reference our Domain and Data project. And we also need to install Entity Framework as well.

 

Second, let’s use our DbContext class. See following action and view code.

Now let’s run the MVC applicaiton.

Ops, we get follwing exception.

By checking the InnerException details, it says “The underlying provider failed on Open.” This means that we didn’t connect to SQL Server successfully. In our MVC application, we need to config the applicaiton to connect to our database.

The fix is that adding following connection string in Web.config file. This will connect to the database Ef created for us in LocalDB.

<connectionStrings>
<add name=”EfJumpStartDbContext”
connectionString=”Data Source=(localdb)\MSSQLLocalDB;Initial    Catalog=EfJumpStart.Data.EfJumpStartDbContext;Integrated Security=True;”
providerName=”System.Data.SqlClient” />
</connectionStrings

Now run the MVC application and it works!

8) Switch to full version of SQL Server

In reallity, we will use full version of SQL Server instead of SQL Server LocalDB.

First, go to SQL Server Management Studio(I am using SQL Server 2014) and create a new database. For example, the database name is EfJumpStart.

Then just change the connection string to the following one

<connectionStrings>
<add name=”EfExamplesDbContext”
providerName=”System.Data.SqlClient”
connectionString=”Data Source=YourServerName; Initial Catalog=EfJumpStart; Integrated Security=True;MultipleActiveResultSets=True” />
</connectionStrings>

Now if you run the MVC application, the EF will do the code first migration in SQL Server 2014 automatically and create the table for us again.

 

 

Database indexing basics

For this topic, I won’t pretend to be an expert. Recently I just started to learn the basics of indexing since our site project has search function to search large amount data.

I found serveral tutorials online that explain quite well on this topic. What I want to know are:

• What is index?
• Why index?
• When to use index?
• How to index in real project?

A good start is reading this answer on Stackoverflow to the question”How does database index work?“. If you find it is still not so clear for you, i would recommend these two short videos to give you some more basic introduction in a different way.

• DBMS Indexing: The Basic ConceptDBMS Indexing: The Basic Concept
• When to Index an RDBMS

After understanding the basic concept of indexing, you may want to know more about it and how it is used in real project. Then I would go for the following good speech video from industry export to learn deeper knowledge about Indexing Fundamentals for Microsoft SQL Server .

 

 

ASP.NET MVC HttpGet vs HttpPost

Problem:

I have a page that displays list of items. And user can filter the items by create date, title, etc. For example:

<h2>Blog List</h2>

@using (Html.BeginForm("List", "Blog"))
{
 @Html.EditorFor(m => m.Filter.CreateDate)
 @Html.EditorFor(m => m.Filter.Title)

 // Table ...

 <input type="submit" value="Submit"/>
}

The action looks something like this:

public ActionResult List(BlogListFilter filter)
{
  var filter = filter ?? new BlogListFilter();
  var model = new BlogListViewModel
  {
    Filter = filter,
    Blogs = _blogService.GetBlogsByFilter(filter)
  };

  return View(model);
}

It works well until i want to add a pager compent for the table because the list of blogs is too large.

@using (Html.BeginForm("List", "Blog"))
{
 @Html.EditorFor(m => m.Filter.CreateDate)
 @Html.EditorFor(m => m.Filter.Title)

 // Table ...

 @Html.PagerFor(m => m.Paging)
 <input type="submit" value="Submit"/>
}

The problem occurs when clicking on the page number to jump to another page. The filter turn out be not working anymore in this case.

public ActionResult List(BlogListFilter filter, int page = 1)
{
  var filter = filter ?? new BlogListFilter();

  // ... create Paging information 
  // ... create paged list
  var model = new BlogListViewModel
  {
    Filter = filter,
    Blogs = pagedBlogList
  };

  return View(model);
}

When I debug it, i found that the filter parameter was null on List action.

After half hour research and comparing with other working example, i found the solution to fixing it was adding HttpGet attribute on List action and FormMethod.Get to form.

Why?

First, i didn’t explictly specify HTTP request method, then the default one is HTTP POST. This can be proved in ASP.NET MVC source code:

 

Second, what’s the accutally difference between HttpGet and HttpPost?  There is a article related to this topic on W3CShool.

The key difference is that when sending data, the GET method adds the data to the URL. In this case, the filter data has been added into URL. You can see it clearly in the Browser’s address bar.

http://localhost:49182/Blog/List?Filter.CreateDate=2016-01-01+00%3A00%3A00&Filter.Title=&page=2

So when clicking page number, the pager just gets current URL and add “page” parameter to that and send the complete URL to server. So the action can still receive the current filter data.

If using HttpPost, then the URL only contains page number data.

http://localhost:49182/Blog/List?page=2

 

Deployment issue: Could not load file or assembly ‘System.Web.Http’

Today I got an exception when deploying an ASP.NET MVC 4 application to our web server.

Could not load file or assembly 'System.Web.Http, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified

It never happened to me before. The only change I did was small code change and package update. What really confused me was that my application doesn’t use System.Web.Http pacakge. I couldn’t see it anywhere in the solution, no matter in References or package.config or Web.config.

After long time checking of installed packages and assemblies, I used remote access to Web server and checked the bin folder of deployed project, System.Web.Http dll file was actually there and its version was 4.0. The reason i can come up with was that some of the packages i updated had dependency on System.Web.Http. But then the problem was “why can’t it be loaded by system?”

Inspired by one post related to this issue on StackOverflow, I tried a solution which added assembly redirects in Web.config like the following one. It resolved the issue.   

<dependentAssembly>
   <assemblyIdentity name="System.Web.Http" publicKeyToken="31bf3856ad364e35" culture="neutral" />
   <bindingRedirect oldVersion="0.0.0.0-4.0.0.0" newVersion="4.0.0.0" />
</dependentAssembly>

That means that some assembly is still referencing the older version of System.Web.Http which caused the problem. By using Assembly Binding Redirection, we are able to tell system bind to the newer version which is 4.0 in this case.

 

Learn Architecting .NET Application – Design Principles

I have recently read the book  Microsoft .NET – Architecting Applications for the Enterprise (Developer Reference).  The book was published in 2008 but the architecture principles and patterns explained in this book are still quite helpful for us in today’s .Net software development.  If you dont’ want to read a really-long-book, I strongly recommend you to watch the Pluralsight course Architecting Applications for the Real World in .NET.

The immediate outcome of these learning is that i have now better understanding of existing system i am working on and start to have a better thought about the code i am writing from architecture’s point view.

Here are some key takeways about design principles that really inspire me:

A system designed to achieve low coupling and high cohesion generally meets the requirements of high readability, maintainability, easy testing and good reuse.

Coupling measures the level of dependency existing between two software modules such as classes, functions or libraries. More specifically, two modules, A and B are said to be coupled when it turns out that you have to make changes to B every time you make any change to A. Low coupling doesn’t mean modules should be completely isolated from each other. They are allowed to communicate with each other but through set of well-defined and stable interfaces. Each module should be able to work without knowing another module’s internal implementation.

Cohesion measures the distance between the logic expressed by various methods on a class, various functions in a library and various actions accomplished by a method. High cohesion means the a module should have well-defined responsibility and focus on what it should do instead of doing many things that have nothing in common. Single responsiblity Principle is a good example of high cohesion.

A good object-oriented design, in fact, is characterized by low coupling and high cohesion, which means that self-contained objects(high cohesion) are interacting with other objects through a stable interface (low coupling).

A principle that is helpful to achieving high cohesion and low coupling is Separation of Concerns(SoC).

SoC is all about breaking the system into distinct and possibly non-overlapping features. Each feature you want in the system represents  a concern and an aspect of the system. It can be achieved by using modular code and making heave use of information hiding.

The concept of SoC has been applied everywhere. In OOP programming languages like C# and Java, you separate concerns using classes. In Service-Oriented Architecture, you use services to separate concerns. Layered architectures are based on SoC. Object/Relational Mapping tool (O/RM) can be used to separate persistence from the domain models. Model-View-Controller(MVC) is also a typical application of SoC.

OOD principle NO 1: Program to an interface, not an implementation.

Interface represents “What” to do not “How” to do it. When class dependencies are based on interface rather than an implementation, you minimized coupling between classes to the smallest possible set of functions which are those well-defined in the interface.

OOD principle NO2: Favor object composition over class inheritance.

Object composition and class inheritance are the two ways to achieve software reusability. On composition, a class, which desire to use functionality of an existing class, doesn’t inherit, instead it holds a reference of that class in a member variable (internal object), that’s why the name composition. Inheritance and composition relationships are also referred as IS-A and HAS-A relationships.With composition, changes to the composite object doesn’t affect the internal object. Likewise, changes to the internal object don’t affect the outmost containers as long as there are no changes to the public interface.

Basic principles such as low coupling, high cohesion(along with the single responsibility principle), separation of concerns, plus the first two principles of OOD give us enough guidance about how to design a software application