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.

linqtosql1_1

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.

linqtosql1_2

Create your first entity in the Entities file like this.

using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace LinqToSQL.Entities
{
    [Table(Name="Customers")]
    public class Customer
    {
        [Column(IsPrimaryKey=true)]
        public string CustomerID;
        [Column]
        public string CompanyName;
        [Column]
        public string City;
        [Column(Name = "Region")]
        public string CountryCode;
        [Column]
        public string Country;
    }
}

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.

namespace LinqToSQL
{
    public static class Connections
    {
        // Connection with SQL Server 2012 - Northwind database
        public const string ConnectionString = "Data Source=developer-pc; Initial Catalog=Northwind;Integrated Security=True";
    }

    class Program
    {
        static void Main(string[] args)
        {
        }
    }
}

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.

using System.Data.Linq;
using LinqToSQL.Entities;

namespace LinqToSQL
{
    public class Queries
    {
        public static void QueryUKCustomers()
        {
            DataContext db = new DataContext(Connections.ConnectionString);
            Table<Customer> customers = db.GetTable<Customer>();

            var query = from c in customers
                         where c.Country == "UK"
                         select new { c.CustomerID, c.CompanyName, c.City };

            foreach (var row in query)
                Console.WriteLine(row);
        }
    }
}

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.

linqtosql1_3

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.

linqtosql1_4

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

[Table(Name = "Contacts")]
    [InheritanceMapping(Code = "Customer", Type = typeof(CustomerContact))]
    [InheritanceMapping(Code = "Supplier", Type = typeof(SupplierContact))]
    [InheritanceMapping(Code = "Shipper", Type = typeof(ShipperContact))]
    [InheritanceMapping(Code = "Employee", Type = typeof(Contact), IsDefault = true)]
    public class Contact
    {
        [Column(IsPrimaryKey = true)]
        public int ContactID;
        [Column(Name = "ContactName")]
        public string Name;
        [Column]
        public string Phone;
        [Column(IsDiscriminator = true)]
        public string ContactType;
    }

    public class CompanyContact : Contact
    {
        [Column(Name = "CompanyName")]
        public string Company;
    }
    public class CustomerContact : CompanyContact
    {
    }

    public class SupplierContact : CompanyContact
    {
    }

    public class ShipperContact : CompanyContact
    {
        public string Shipper
        {
            get { return Company; }
            set { Company = value; }
        }
    }

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.

public static void QuerySuppliersWithInheritance()
        {
            DataContext db = new DataContext(Connections.ConnectionString);
            Table<Contact> contacts = db.GetTable<Contact>();
            db.Log = Console.Out;

            var query = from c in contacts.OfType<SupplierContact>()
                        select c;

            foreach (var row in query)
            {
                Console.WriteLine("ID:" + row.ContactID
                    + "Name: " +row.Name + "Type: " + row.ContactType);
            }
        }

linqtosql1_5

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.

using System.Data.Linq.Mapping;
using System.Data.Linq;

namespace LinqToSQL.RelationshipEntities
{
    class RelationshipEntites
    {
    }

    [Table(Name = "Orders")]
    public class Order
    {
        [Column(IsPrimaryKey = true)]
        public int OrderID;
        [Column]
        private string CustomerID;
        [Column]
        public DateTime? OrderDate;

        [Association(Storage = "_Customer", ThisKey = "CustomerID", IsForeignKey = true)]
        public Customer Customer
        {
            get { return this._Customer.Entity; }
            set { this._Customer.Entity = value; }
        }

        private EntityRef<Customer> _Customer;

        public Order()
        {
            _Customer = default(EntityRef<Customer>);
        }
    }
    [Table(Name="Customers")]
    public class Customer
    {
        [Column(IsPrimaryKey = true)]
        public string CustomerID;
        [Column]
        public string CompanyName;
        [Column]
        public string Country;

        private EntitySet<Order> _Orders;

        [Association(OtherKey = "CustomerID", Storage = "_Orders")]
        public EntitySet<Order> Orders
        {
            get { return this._Orders; }
            set { this._Orders.Assign(value); }
        }

        public Customer()
        {
            this._Orders = new EntitySet<Order>();
        }
    }
}

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.

[Table(Name="Customers")]
 public class Customer {
    [Column(IsPrimaryKey=true)] public string CustomerID;
    [Column] public string CompanyName;
    [Column] public string Country;
    [Association(OtherKey="CustomerID")]
    public EntitySet<Order> Orders;
}

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.

using System.Data.Linq;
using LinqToSQL.RelationshipEntities;

namespace LinqToSQL
{
    public class RelationshipQueries
    {
        public static void OrdersByCustomersLiveInFrance()
        {
            DataContext db = new DataContext(Connections.ConnectionString);
            Table<Order> orders = db.GetTable<Order>();

            var query = from o in orders
                        where o.Customer.Country == "France"
                        select new { o.OrderID, o.OrderDate, o.Customer.CustomerID };

            foreach (var item in query)
                Console.WriteLine("Order ID: " + item.OrderID +
                                    " Order Date: " + item.OrderDate +
                                    " Customer ID: " + item.CustomerID);

        }
    }
}

Run your new method in the Program class and see the results.

linqtosql1_6

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.

linqtosql1_7

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

public static void CustomersWhoHaveOrderedMoreThan25Times()
        {
            DataContext db = new DataContext(Connections.ConnectionString);
            Table<Customer> customers = db.GetTable<Customer>();

            db.Log = Console.Out;

            var query = from c in customers
                        where c.Orders.Count() > 25
                        select new { c.CustomerID, numberOfOrders = c.Orders.Count()  };

            foreach (var item in query)
                Console.WriteLine("Customer ID: " + item.CustomerID +
                    " has ordered " + item.numberOfOrders + " times");

        }

linqtosql1_8

That’s it, we have seen both how to create and map our LINQ to SQL entities to database tables and how to implement relationships. In later posts, will see more query examples with the LINQ to SQL.

Advertisements


Categories: ADO.NET

Tags: , ,

5 replies

  1. Thank you for sharing Chris, most informative

  2. 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.

Trackbacks

  1. LINQ to SQL : CRUD operations and Stored procedures | chsakell's Blog
  2. Ajax and jQuery in ASP.NET MVC | chsakell's Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Diary Of A Programmer

Because every day is worth noting

Chara Plessa

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!

chsakell's Blog

Anything around ASP.NET MVC,WEB API, WCF, Entity Framework & AngularJS

Kumikoro

A Front End Developer's Blog

Muhammad Hassan

Full Stack Developer | ASP.NET | MVC | WebAPI | Advanced Javascript | AngularJS | Angular2 | C# | ES6 | SQL | TypeScript | HTML5 | NodeJS, MS candidate @LUMS, Grad & EX-Adjunct Faculty @NUCES-FAST, seasonal blogger & open-source contributor. Seattle, WA.

Software Engineering

Web development

IEvangelist

.NET, ASP.NET, C#, MVC, TypeScript, AngularJS

leastprivilege.com

Dominick Baier on Identity & Access Control

Happy DotNetting

In Love with Technology

Knoldus

Knols of experience to your advantage

knowshnet

Search - Read - Request - Share

Rahul's space

Learn, Share and Grow with me !

Dhananjay Kumar

Developer Evangelist @Infragistics | MVP @Microsoft |

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Conficient Blog

Random bits of tech from @conficient

Code! Code! Code!

SOLID & KISS

Code Wala

Designing and coding

Microsoft Mentalist

A way to start with Microsoft Technologies

Tony Sneed's Blog

A glimpse into the lives of Tony & Zuzana Sneed

%d bloggers like this: