Introduction
The following example requires generating a mapping file for a Northwind database.
LINQ to SQL solves the mismatch between the object oriented and relational database world. In an application we deal in terms of our domain objects such as customer and orders. However, when it comes to saving these objects and solving the datatype mismatch between the programming and database world, it has traditionally been the developer's responsibility to write code to persist these objects to a database and handle the data type conversions. Where LINQ to SQL shines as compared to other ORM tools in the market is the fact that not only LINQ to SQL abstracts away the database structure behind our business objects, it also supports a query language called LINQ. Although LINQ to SQL provides a query language, you can still write your queries using stored procedure or dynamic SQL. LINQ to SQL maps your entity classes to database tables and entity class properties to database table columns. Let’s see an example.
public static void DynamicInsert()
{
NorthWindDataContext db = new NorthWindDataContext();
db.ExecuteCommand("insert into shippers(companyname,
phone) values({0},{1})", "Test shipper", "(111)111-1111");
var shipper = db.Shippers.Single(s =>
s.CompanyName == "Test shipper");
Console.WriteLine(shipper.ShipperID);
}
The example above shows how we can directly insert record into Northwind without using our shipper object and then querying the database using our context to confirm that record got inserted. Not only can we insert but we also have the ability to query database using dynamic SQL and directly get our objects back. The benefit of going that route would be to write your own SQL but not have to deal with issues of converting datareader to our objects because Datacontext takes care of it as long as the fields returned from SQL matches the properties on your objects. Let see an example of that.
public static void DynamicSql()
{
NorthWindDataContext db = new NorthWindDataContext();
IEnumerable<Category>
categories = db.ExecuteQuery<Category>(
"select categoryid,categoryName CatName from categories where categoryname = {0}",
"Beverages");
foreach (var cat in categories)
{
Console.WriteLine(cat.CategoryName);
}
}
When you run the above query you will notice that category name would be set to null. When you are constructing a dynamic SQL, the column name needs to match with the properties on your object(Category). Also notice that we are not returning all the fields from our database and yet we do not get any exception. The reason for that is that LINQ to SQL will not set any values on the properties that are not returned from the SQL. However, when your dynamic SQL statement does not contain the column which is defined as primary key, LINQ to SQL will raise a runtime exception as shown below.
public static void DynamicSqlWithNoPrimaryKey()
{
NorthWindDataContext db = new NorthWindDataContext();
IEnumerable<Category> categories = db.ExecuteQuery<Category>(
"select categoryName from categories where categoryname = {0}", "Beverages");
foreach (var cat in categories)
{
Console.WriteLine(cat.CategoryName);
}
}
Another useful method that I found on the datacontext is translate. The translate method basically takes in a datareader and returns a collection of objects. This would come in handy on existing data access layers which returns a datareader and you can use the translate method to get your business objects back. Let's see an example.
public static void ReturnObjectsUsingDataReader()
{
NorthWindDataContext db = new NorthWindDataContext();
db.Connection.Open();
SqlCommand cmd = new SqlCommand("select categoryid,categoryName,
Description from categories",
db.Connection as SqlConnection);
IEnumerable<Category> categories = db.Translate<Category>(
cmd.ExecuteReader(CommandBehavior.CloseConnection));
foreach (var category in categories)
{
Console.WriteLine(category.CategoryName);
}
}
There are times when you would be interested in knowing what LINQ to SQL does under the covers. There are various methods to capture the SQL. For example, you can use SQL Server profile. Datacontext has a property called log which can be used to output the SQL or you can use the getquerytext method on datacontext to find out what SQL would execute if you were to execute the LINQ to SQL query. Let's have a look at an example.
Overriding Insert,Update and Delete Methods
LINQ to SQL allows you to use stored procedures and specify your own implementation for inserting an object into the database. Going that route would require more involvement from the developer side which includes resolving change conflicts that could occur. One way to accomplish the task is using the designer by dragging the stored procedure onto the method pane. And right-clicking on Category to configure its behavior as follows.
create proc dbo.InsertCategory
(
@CategoryID int output,
@CategoryName nvarchar(15),
@Description nvarchar(100)
)
as
begin
insert into dbo.categories(categoryname,[description])
values (@CategoryName,@Description)
set @CategoryID = @@identity
end
Once you are done configuring the wizard with the stored procedure you can insert category as follows.
public static void InsertCategoryUsingStoredProcedure()
{
NorthWindDataContext db = new NorthWindDataContext();
Category category = new Category { CategoryName = "test",
Description = "test description" };
db.Categories.InsertOnSubmit(category);
db.SubmitChanges();
Console.WriteLine(category.CategoryID);
}
Inserting Object Graph
When you are inserting an object by calling insertonsubmit, LINQ to SQL does not actually insert the row until you call submit changes. One of the nice things about LINQ to SQL is if you have an object hierarchy, say: Category, Product and supplier, which are related to each other in the hierarchy you don’t necessarily have to call insertonsubmit on each object. Calling on the base object like category is sufficient enough for LINQ to SQL to save all the objects including product and supplier. Let's look at an example of this
public static void InsertingHiearachy()
{
NorthWindDataContext db = new NorthWindDataContext();
Category category = new Category
{
CategoryName = "Test",
Description = "test description",
Products =
{
new Product
{
ProductName="my product",
Supplier= new Supplier{CompanyName="test company"}
}
}
};
db.Categories.InsertOnSubmit(category);
db.SubmitChanges();
var cat = db.Categories.Single(c =>
c.CategoryID == category.CategoryID);
Console.WriteLine(cat.CategoryName);
Console.WriteLine(cat.Products[0].ProductName);
Console.WriteLine(cat.Products[0].Supplier.CompanyName);
}
You will notice from the above example that we did not call insertonsubmit on product or supplier but instead LINQ to SQL because the relationship was able to insert all three wobjects when submit changes were called.
Since all the objects returned from datacontext are returned as Table<T> which happens to implement IQueryable<T>, therefore most LINQ to SQL queries are differed and converted into expression trees instead of intermediate language code. Once they are converted to expression trees, the LINQ to SQL provider converts the expression tree into SQL and executed on the database. Because the queries are executed on the database, calling a .NET method inside of a query does not have any translation to the database, which would cause the query to fail.
LINQ TO SQL Differed Exection
By default LINQ to SQL will not load up any child entity, unless the child property is called. This behavior is called differed exection. This behavior ensures that LINQ to SQL to not retrieve any child objects unless they are explicitly referenced. Let's look at an example.
public static void LinqDifferedExection()
{
NorthWindDataContext db = new NorthWindDataContext();
db.Log = Console.Out;
var customer = db.Customers.First();
Console.WriteLine(customer.ContactName);
Console.WriteLine(customer.Orders.Count());
}
If you look at the SQL statements executed you will notice that there were two separate queries executed. And it is only when the Orders object was accessed that LINQ to SQL made an additional call to access all the orders for that customer.
Although differed loading is the default behavior of LINQ to SQL, in a case where you do not want to incur the hit everytime you access a particular child object you can use dataloadoptions to tell LINQ to SQL to immediately load the desired class specified in the loadWith<T> operator. When using LoadWith<T> the associated class is retrieved when LINQ to SQL queries for the primary class. Immediate loading is not limited to loading one associated class. You can provide as many associated classes that you would need to be loaded ahead of being called. Let's look at an example.
public static void ImmedidateLoading()
{
NorthWindDataContext db = new NorthWindDataContext();
db.Log = Console.Out;
DataLoadOptions option = new DataLoadOptions();
option.LoadWith<Customer>(c => c.Orders);
db.LoadOptions = option;
var customers = db.Customers.Where(c => c.City == "London");
foreach (var customer in customers)
{
Console.WriteLine(customer.ContactName);
foreach (Order order in customer.Orders)
{
Console.WriteLine(order.OrderID);
}
}
}
Looking at an above example you would notice that we are looping through the order collection of customer, but only 1 single query was executed by LINQ to SQL because we are using loadoptions which ensures that all the orders for the related customers are loaded ahead of time.
The behavior of immediate loading changes when you are loading multiple associated classes. Only the first one will be joined with the primary class and others will be loaded when you reference the primary class. However, it is still considered immediate because the LINQ to SQL will query the object before you actually reference the object. A separate query would be performed to pull the data for associated class every time you access the primary class. For this demo I have added another table to Northwind called Address. Basically I will immediately load all addresses and orders for a particular customer.
CREATE TABLE [dbo].[Address](
[AddressId]
[int] IDENTITY(1,1) NOT NULL,
[Address1]
[varchar](50) NULL,
[CustomerID]
[nchar](5) NOT NULL,
)
public static void ImmediateMultipleAssociations()
{
NorthWindDataContext db = new NorthWindDataContext();
db.Log = Console.Out;
DataLoadOptions option = new DataLoadOptions();
option.LoadWith<Customer>(c => c.Addresses);
option.LoadWith<Customer>(c => c.Orders);
db.LoadOptions = option;
var customers = db.Customers.Where(c => c.City == "London");
foreach (Customer customer in customers)
{
Console.WriteLine(customer.ContactName);
}
}
When looking at query generated by LINQ to SQL, you will notice that customers were joined to the orders table but a seprate query was run for each customer to access its address every time a customer object was referenced. Since neither order nor order details are being referenced in the code, we can correctly say that it is performing immediate loading. However, the address query is being executed for every customer.
Let's look at an example where we load customer, its orders and for each order its order detail using immediate loading.
public static void ImmmediateLoadingWithDependency()
{
NorthWindDataContext db = new NorthWindDataContext();
db.Log = Console.Out;
DataLoadOptions option = new DataLoadOptions();
option.LoadWith<Customer>(c => c.Orders);
option.LoadWith<Order>(o => o.Order_Details);
db.LoadOptions = option;
var customers = db.Customers.Where(c => c.City == "London");
foreach (Customer customer in customers)
{
Console.WriteLine(customer.ContactName);
}
Based on the query you will notice the same behavior where a separate query was performed to access the order table every time Customer was being referenced inside the foreach loop. The only main difference between this example and the previous example is this time LINQ to SQL only retrieved Customers. It was only when the customer object was being referenced inside the for loop that LINQ to SQL not only queried order but also the order details table.
Another use of data load options is to filter the associated classes by using associatedWith method. As you can see in the result below that although each categories have more than 1 product, we use LINQ to SQL to retrieve only 1 product for each category because of the filter applied on AssociateWith method on DataLoadOptions.
public static void FilteringAssociatedClasses()
{
NorthWindDataContext db = new NorthWindDataContext();
DataLoadOptions option = new DataLoadOptions();
option.AssociateWith<Category>(c => c.Products.Take(1));
db.LoadOptions = option;
foreach (Category category in db.Categories)
{
Console.WriteLine(category.CategoryName);
Console.WriteLine(category.Products.Count());
}
}
Operator Conversion
There are numerous methods that are defined in .NET to manipulate strings. Some of them have equivalent translation in LINQ to SQL and gets executed on SQL Server.
public static void OperatorConversions()
{
NorthWindDataContext db = new NorthWindDataContext();
db.Log = Console.Out;
string[] cities = {"London","Seattle"};
var orders = db.Orders.Where(o => cities.Contains(o.ShipCity));
Console.WriteLine(orders.Count());
}
Notice how we are using the "contains" clause which gets converted to the "in" clause in SQL Server.
DataContext Query Behavior
As I mentioned in the start of the article that LINQ to SQL will not execute you query unless submit changes is called. So if you add a customer to the customer collection and then query the datacontext you will not get that record back since submit changes has not been called. Let's take a look at an example of that.
public static void SubmitChangesBehavior()
{
NorthWindDataContext db = new NorthWindDataContext();
db.Log = Console.Out;
Customer customer = new Customer { CustomerID = "TestID",
CompanyName = "Test Company" };
db.Customers.InsertOnSubmit(customer);
var _customer = db.Customers.SingleOrDefault(c => c.CustomerID == "TestID");
Console.WriteLine(
_customer == null ? "Customer not found" : "Customer found");
}
There are two things to notice. First, despite the fact that LINQ to SQL was tracking the object in its context; it made a query to SQL Server to search for the customer with a specific customerid. Secondly, you will also notice that no customer was found.
This confirms the fact that customer object will not be returned from the context unless submit changes is called.
Another interesting behavior which I have noticed with the single operator is when you query for an object based on its primary key, the next time you query with same primary key, LINQ to SQL does not even go to the database, it retrieves the object from its internal cache. Let's have a look at an example of that.
public static void LinqToSqlCachingBehavior()
{
NorthWindDataContext db = new NorthWindDataContext();
db.Log = Console.Out;
var cust1 = db.Customers.Single(c => c.CustomerID == "ALFKI");
Console.WriteLine(cust1.ContactName);
var cust2 = db.Customers.Single(c => c.CustomerID == "ALFKI");
Console.WriteLine(cust2.ContactName);
}
As you will notice from the result that the query got retrieved only once. Even though we requested it twice because we were querying based on primary key. If this would not have been the case then LINQ to SQL would return the same object but would still execute the query both the times. Let's have a look at an example of that.
public static void LinqToSqlSameObjectReturned()
{
NorthWindDataContext db = new NorthWindDataContext();
db.Log = Console.Out;
var cust1 = db.Customers.Single(c => c.ContactName == "Maria Anders");
Console.WriteLine(cust1.ContactName);
var cust2 = db.Customers.Single(c => c.ContactName == "Maria Anders");
Console.WriteLine(cust2.ContactName);
Console.WriteLine(cust1 == cust2);
}
You will notice from the result that although the reference to the object was the same, LINQ to SQL executed the query twice.
