LINQ to SQL : Data Modeling – Inheritance – Relationships

LINQ to SQL is a LINQ implementation which can be used to query relational Microsoft SQL Server database, in an object oriented way. You can create an object model (classes) that maps your relational database tables, then query against this model using LINQ to SQL queries, which are eventually converted to the respective SQL native queries in a database level by the LINQ to SQL engine. If you are new to LINQ you can read the LINQ Basics post or the LINQ To Objects one, to get an idea. Before start writing LINQ to SQL queries, you need to define your entity model and map it to a relational database. Hence, the first thing I am gonna show you is how to create and configure your Entities in LINQ to SQL. Let’s start.

Open Visual Studio 2012 and create a new C# Console Application project named “LinqToSQL”. Right click your project and add a reference to the System.Data.Linq assembly. Right click your project again and add a C# class named “Entities”. This is where we ‘ll define our entities. I am going to use the Northwind database for all examples in this and later posts of these series, so make sure you install it in your SQL Server to follow along with this post. If you don’t want to, never mind, just keep reading this post to understand the concepts and later you can write similar code to your development environment.

In order to map a class to a respective table in a database, you need to decorate that class with the appropriate metadata annotations. For example, you need to define which database table the entity class maps and how properties map the corresponding table columns. We will create a Customer entity class which will map our “Customers” table in the Northwind database. You will notice that you don’t have to map all the table columns in your entity class.

I highlighted the most important parts of the above code. First of all you need to add using statements for the System.Data.Linq and System.Data.Linq.Mapping namespaces. You map an entity class to a table, adding a [Table] attribute at the class definition. The name parameter specifies the corresponding database table (We haven’t specified which database yet, but that’s ok). The class properties you want to map to the table columns, must be decorated with the [Column] attribute. If you wish to keep the same name as the column’s one, just the [Column] attribute is enough, assuming you name the property respectively. If you want to use a different name for your property, you need to add a Name parameter in the Column attribute (see how CountryCode property maps the Region Column).
To query Customers data, you need to instantiate a Customer table, and to do this, first you have to create an instance of a DataContext class. This class is the bridge between the LINQ and the relational database. It takes a connection string as a parameters so let’s create one. I created a new class “Connections” in the Program.cs file, above the Program class, where I define my connection string to the Northwind database in my SQL Server. Change yours to point your SQL Server instance.

It’s time to create and test our first LINQ to SQL query. Create a new class file named “Queries” in your project. Add again using statements for the System.Data.Linq namespace and the LinqToSQL.Entities one as well.

We created a DataContext instance passing our connection string as parameter and we retrieved a Table of Customer objects using the DataContext.GetTable method. Call the “QueryUKCustomers()” method in the main method of your Program class. Build and run your project.

The next feature will show is how LINQ to SQL allows a set of classes derived from the same base class to map to the same relational table. In other words.. Inheritance. First you declare which is the Base entity class, then you decorate this class with [InheritanceMapping] Attributes passing to it parameters, to tell the LINQ to SQL engine how to map the possible values of the discriminator column to the corresponding Derived types. The following example will make things clearer. Notice the Northwind “Contacts” table, that has a column named “ContactType” which can take 4 distinct values (Customer, Supplier, Employee, Shipper). It would be nice to create particular Entity classes such as “CustomerContact” or “SupplierContact”, and when referenced in a LINQ to SQL query automatically detect the corresponding records.

Under the Customer class in the Entities.cs file, add the following code.

In code lines 2-5 we declared how different discriminator column values (ContactType – line 14,15) will map the respective derived types. So simple. Let’s create now a new LINQ To SQL query to retrieve Contacts with ContactType=Supplier. Add the following method in the Queries class and call it from the main method in the Program.cs file. Notice the db.Log statement, you may want to use it a lot to track every time an SQL statement is passed to the database from the LINQ to SQL engine.

Now, here’s a million dollar question. How does LINQ to SQL Data Modeling support relationships? The answer is that in the same way a relational database support relationships between tables based on the foreign and primary keys concept, class entities can use the same concept using the Association Attribute. To demonstrate entity relationships create a new class file named RelationshipEntites. We ‘ll create the entity relationships for the Orders and Customers Northwind tables. This relationship is One-To-Many, which means, one customer can have many orders. In database level, Orders table has a foreign key named CustomerID which points to the responding primary key in the Customers table. Create the relationship in the new class file as follow.

First of all, the Order Entity must include a “CustomerID” property, cause this will be used to match the respective Customer in the database. Second, it defines a Customer property of type Customer annotating it with an Association attribute. This property also stores its information in an EntityRef member (named _Customer), which enables deferred loading of references. The ThisKey argument indicates the foreign key column and the IsForeignKey specifies that “Order” entity will be the Many side of the One-To-Many relationship. In the same pattern the new Customer class entity, implements the relationship by defining an EntitySet Orders property, annotating with the same attribute, [Association]. Be aware, that using the “Storage” parameter isn’t necessary but it’s very convenient for LINQ to SQL queries since it enables deferred loading of references. In other words, you could create the Customer entity like this.

Ok, lets test our new Entities now. Add a new class file named RelationshipQueries. We create a new file because we want to use the new Customer entity. Write a LINQ to SQL query, to retrieve all orders made my customers live in France.

The interesting part of the code is that when you are actually writing your query you can access immediately the Customer property of an Order, without need to write a JOIN. Though what is actually run behind the scenes is indeed a JOIN query. I added a

db.Log = Console.Out;

statement in my method.

Let’s create another method to query all customers who have ordered more than 25 times.

5 replies

Wonderful goods from you, man. I’ve understand your stuff previous to and
you’re just extremely great. I actually like what you have acquired here, really like what you
are stating and the way in which you say
it. You make it enjoyable and you still take care of to keep it sensible.
I can’t wait to read much more from you. This is actually a wonderful site.

The purpose of this blog is to broaden my education, promote experimentation and enhance my professional development. Albert Einstein once said that “If you can’t explain it simply, you don’t understand it well enough” and I strongly believe him!