Each LINQ to SQL datacontext we instantiate holds its own identity map (based on Martin Fowlers Identity Map Pattern). This is basically a dictionary with identity keys pointing to entities in memory previously retrieved from the database – within the scope of the datacontext, that is.

In the example below we retrieve two hotels from the database with the same identity and on the same datacontext. The database gets called only ones for our LINQ query. Hotel1 and Hotel2 are pointing to the same object in memory. They are registered in the same identity map.

using System;
using System.Linq;
using Remondo.Database;
 
namespace LinqToSqlIdentityMap
{
    internal class Program
    {
        private static void Main()
        {
            using (var dataContext =
                new HotelsDataContext {Log = Console.Out})
            {
                Hotel hotel1 = dataContext.Hotels.First(h => h.ID == 5);
                Hotel hotel2 = dataContext.Hotels.First(h => h.ID == 5);
 
                Console.WriteLine("Hotel 1 - {0}", hotel1.GetHashCode());
                Console.WriteLine("Hotel 2 - {0}", hotel2.GetHashCode());
 
                Console.ReadKey();
            }
        }
    }
}

In the example below we retrieve two Hotels from our database with the same identity, but from a different datacontext. Now Hotel1 and Hotel2 are two different objects in memory and registered in two different identity maps. We see the database gets called twice for the same query.

using System;
using System.Linq;
using Remondo.Database;
 
namespace LinqToSqlIdentityMap
{
    internal class Program
    {
        private static void Main()
        {
            using (var dataContext1 =
                new HotelsDataContext {Log = Console.Out})
            using (var dataContext2 =
                new HotelsDataContext {Log = Console.Out})
            {
                Hotel hotel1 = dataContext1.Hotels.First(h => h.ID == 5);
                Hotel hotel2 = dataContext2.Hotels.First(h => h.ID == 5);
 
                Console.WriteLine("Hotel 1 - {0}", hotel1.GetHashCode());
                Console.WriteLine("Hotel 2 - {0}", hotel2.GetHashCode());
 
                Console.ReadKey();
            }
        }
    }
}

In the next example we change the name of Hotel 1 on the first datacontext. We call SubmitChanges, so the database has stored the new name for this hotel. However the in-memory hotel object for datacontext 2 still has the old name. We need to call the Refresh method of the second datacontext for its hotel object to retrieve the new name from the database.

using System;
using System.Data.Linq;
using System.Linq;
using Remondo.Database;
 
namespace LinqToSqlIdentityMap
{
    internal class Program
    {
        private static void Main()
        {
            using (var dataContext1 = new HotelsDataContext())
            using (var dataContext2 = new HotelsDataContext())
            {
                Hotel hotel1 = dataContext1.Hotels.First(h => h.ID == 5);
                Hotel hotel2 = dataContext2.Hotels.First(h => h.ID == 5);
 
                PrintHotelNames(hotel2, hotel1);
 
                // Start Logging to the console window from here
                dataContext1.Log = Console.Out;
                dataContext2.Log = Console.Out;
 
                PrintActionTitle("*** Updating Hotel 1 on datacontext 1");
                hotel1.Name = "Hotel de L'Europe";
                dataContext1.SubmitChanges();
 
                PrintHotelNames(hotel2, hotel1);
 
                PrintActionTitle("*** Refreshing Hotel 2 on datacontext 2");
                dataContext2.Refresh(RefreshMode.OverwriteCurrentValues, hotel2);
 
                PrintHotelNames(hotel2, hotel1);
 
                Console.ReadKey();
            }
        }
 
        private static void PrintActionTitle(string actionTitle)
        {
            Console.WriteLine();
            Console.WriteLine(actionTitle);
            Console.WriteLine();
        }
 
        private static void PrintHotelNames(Hotel hotel2, Hotel hotel1)
        {
            Console.WriteLine("Hotel 1 - {0}", hotel1.Name);
            Console.WriteLine("Hotel 2 - {0}", hotel2.Name);
        }
    }
}

It’s sometimes not possible to use LINQ to query to a database or for some reason we want to execute a SQL query statement from within our code. That’s possible by using the DataContext ExecuteQuery or ExecuteCommand methods. The first returns a IEnumerable with entities, while the latter returns a scalar value.

In the example below we query a simple company database for all the male employees ordered by hiredate. We print the employee name and hiredate to the console.

using System;
using System.Collections.Generic;
using Remondo.Database;
 
namespace ExecuteSqlConsole
{
    internal class Program
    {
        private static void Main()
        {
            var dataContext = new RemondoDataContext();
 
            IEnumerable<Employee> employees =
                dataContext.ExecuteQuery<Employee>(
                    "SELECT * FROM Employee " +
                    "WHERE Gender_ID = {0} " +
                    "ORDER BY HireDate",
                    1);
 
            foreach (Employee employee in employees)
            {
                Console.WriteLine(
                    "{0,-24} since {1:yyyy}",
                    employee.Name,
                    employee.HireDate.Date);
            }
 
            Console.ReadKey();
        }
    }
}

Calling Stored Procedures

We can call a Stored Procedure by adding it to the designsurface of the DataContext. We created the same query in a stored procedure called sp_GetEmployeesByGenderOrderedByHireDate. By default this method returns an ISingleResult of some auto-generated type.

In this case we want some list of Employee back, so we set the Return Type property of the stored procedure method to the type of Employee. A warning appears telling us this can’t be undone. Oh well.

We can now call our stored procedure from code and iterate through the result set of employees. The output to the console from both the sql query and stored procedure implementations is as expected.

using System;
using System.Data.Linq;
using Remondo.Database;
 
namespace ExecuteSqlConsole
{
    internal class Program
    {
        private static void Main()
        {
            var dataContext = new RemondoDataContext();
 
            ISingleResult<Employee> employees = dataContext
                .sp_GetEmployeesByGenderOrderedByHireDate(1);
 
            foreach (Employee employee in employees)
            {
                Console.WriteLine(
                    "{0,-24} since {1:yyyy}",
                    employee.Name,
                    employee.HireDate.Date);
            }
 
            Console.ReadKey();
        }
    }
}

With LINQ to SQL we can get model inheritance with filtered mapping, called Single Table Inheritance. We do this by applying a discriminator value to the derived classes. By mapping this value LINQ to SQL knows to instantiate the appropriate base or derived class.

In this example we have a Car base class with the derived types SportsCar and ElectricHybridCar. All cars have a Brand name, but the SportsCar and ElectricHybridCar have some specific properties. The Car base has the discriminator value we mentioned above. It’s a simple character field.

We set the discriminator value by configuring the inheritance properties. In this case we clicked the arrow between Car and SportsCar and set the Derived Class Discriminator Value to “S” for SportsCar and the Base Class Dicriminator to “C” for Car. In the Discriminator Property we select the name of the column in our table we use for storing the discriminator values. We do the same for the ElectricHybricCar inheritance properties, this time with the discriminator value “E”.

We use only one Car table in SQL server to store all the properties of our cars.

To test if LINQ to SQL actually gives us different types of Cars, SportsCars and ElectricHybridCars back based on the discriminator, we build a simple console application. Iterating through the complete list we print out the specific type of car to the console.

using System;
using System.Linq;
using Remondo.Database;
 
namespace CarsLinqToSqlInheritance
{
    internal class Program
    {
        private static void Main()
        {
            var dataContext =
                new CarsDataContext();
 
            IQueryable<Car> cars =
                dataContext.Cars;
 
            foreach (Car car in cars)
            {
                Console.WriteLine("{0}",
                    car.GetType().FullName);
            }
 
            Console.ReadKey();
        }
    }
}

And indeed… ;-)

By default LINQ to SQL is lazy. It defers loading of data until the very first moment it is needed by your application. This behavior makes it possible to keep adding peaces to our query until we are ready to fire a SQL statement to the database. It can also lead to problems and nasty fights with your DBA if you fire hundreds of queries to the db while iterating through some list. This can be prevented by thinking about when to load certain parts of your query and specifying this in the DataLoadOptions of your DataContext.

To illustrate this we have the example below with a simple database of a company in LINQ to SQL classes.

In our application we query for a list of all our female employees ordered descending by their name. We print a list to the console with the Employee name, gender and the building of the department where they work.

In our RemondoDataContext we log all SQL statements to the console. I’ve added some color formatting to point out where LINQ to SQL actually fires of a SQL statement to the database. All data output is shown in white. The green area shows all LINQ to SQL activity to the database with the iteration entry included. The red area show all activity after the first call to the database. As we run our code as shown below, we see a lot of deferred calls (red) in the output on the console.

using System;
using System.Linq;
using Remondo.Database;
 
namespace Remondo.LinqToSqlConsole
{
    internal class Program
    {
        private static void Main()
        {
            // The GREEN area
            Console.ForegroundColor = ConsoleColor.DarkGreen;
 
            var dataContext =
                new RemondoDataContext
                    {
                        Log = Console.Out,
                    };
 
            IOrderedQueryable<Employee> employees =
                dataContext.Employees
                    .Where(g => g.Gender.Name == "Female")
                    .OrderByDescending(e => e.Name);
 
 
            foreach (Employee employee in employees)
            {
                // The RED area
                Console.ForegroundColor = ConsoleColor.DarkRed;
 
                string name = employee.Name;
                string gender = employee.Gender.Name;
                string building = employee.Department.Building.Name;
 
                // Data output in WHITE
                Console.ForegroundColor = ConsoleColor.White;
                Console.WriteLine(
                    "{0}\t{1}\t{2}",
                    name,
                    gender,
                    building);
            }
 
            Console.ReadKey();
        }
    }
}

That’s too lazy for my taste . What we clearly want here is a result set with gender and building joined on the first call of the iteration. We can do this by specifying up front what the LINQ to SQL must load when we ask for our Employee list the first time. We can do this by setting the DataLoadOptions of the DataContext on a per context basis. With the LoadWith method of the DataLoadOptions object we specify to load Gender and Building with each Employee. We also need to load Department for our Building name.

using System;
using System.Data.Linq;
using System.Linq;
using Remondo.Database;
 
namespace Remondo.LinqToSqlConsole
{
    internal class Program
    {
        private static void Main()
        {
            Console.ForegroundColor = ConsoleColor.DarkGreen;
 
            var dataLoadOptions = new DataLoadOptions();
            dataLoadOptions.LoadWith<Employee>(e => e.Department);
            dataLoadOptions.LoadWith<Employee>(e => e.Gender);
            dataLoadOptions.LoadWith<Department>(d => d.Building);
 
            var dataContext =
                new RemondoDataContext
                    {
                        Log = Console.Out,
                        LoadOptions = dataLoadOptions
                    };
 
            IOrderedQueryable<Employee> employees =
                dataContext.Employees
                    .Where(g => g.Gender.Name == "Female")
                    .OrderByDescending(e => e.Name);
 
 
            foreach (Employee employee in employees)
            {
                ConsoleColor prevColor = Console.ForegroundColor;
                Console.ForegroundColor = ConsoleColor.DarkRed;
 
                string name = employee.Name;
                string gender = employee.Gender.Name;
                string building = employee.Department.Building.Name;
 
 
                Console.ForegroundColor = ConsoleColor.White;
                Console.WriteLine(
                    "{0}\t{1}\t{2}",
                    name,
                    gender,
                    building);
 
                Console.ForegroundColor = prevColor;
            }
 
            Console.ReadKey();
        }
    }
}

Checking out the results in the console we see that LINQ to SQL joined the gender, department and building up front as expected.