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.

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.

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.

[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);
            }
        }

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.

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.

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");

        }

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.

Categories: ADO.NET

Tags: Language Integrated Query, LINQ, SQL