LINQ to SQL is an ORM tool that connects to SQL Server databases. It generates business objects to match a database's table structures, which are all centrally available through the DataContext class. The LINQ-to-SQL designer creates a custom DataContext class that contains a series of Table<Entity> objects representing each of the tables in your database.

LINQ to SQL makes it easier to create application architecture, with some caveats (what doesn't have any in life?).

LINQ maintains all of its relationships between all the related objects. Every primary and foreign key relationship contains an object reference for that particular relationship. A PK reference is represented by an EntityRef generic object, while an FK reference is a collection of objects in an EntitySet generic collection.

Generally, LINQ-to-SQL works like most business objects you would expect to; if you assign the reference for that property to the business object, it retains that reference. However if you do not, it is null. Let me explain.  Let us say the data context has an Orders table. This Orders table is related to the Customers table, where the Customer is the primary key. For an order record in that table, which is represented by the Order business object, this object has a Customer reference property and a CustomerKey GUID property.

If you assign a value to the CustomerKey, the Customer object reference is not automatically populated; however, the reverse is not true. Whenever an object reference is provided to the Customer property, the CustomerKey property contains the GUID primary key value.

Once the call to the DataContext.SubmitChanges is made, the value assigned to CustomerKey refreshes the value in the Customer object reference property. Let us look at an example. Below a new order object is created and a customer key is assigned.

Listing 1

Order order = new Order();
 order.CustomerKey = customerKey;
Assert.IsNotNull(order.Customer);  // is success

But, if using the opposite approach where an object is assigned to the Customer property, the CustomerKey is populated.

Listing 2

Order order = new Order();
 order.Customer = context.Customers.First();
 Assert.IsTrue(order.CustomerKey != Guid.Empty); // is success

If you are updating your LINQ objects, and you want to update an object without submitting the changes to the database at that moment, you can make the assignment via an object reference, which will update the relationship key.

When adding a child LINQ object through its parent object's collection, this does not mean that the object is immediately available through the data context.  For instance, take a look at the following code.

Listing 3

Customer customer = _context.Customers.First();
customer.Orders.Add(order);
Assert.AreEqual(1, customer.Orders.Count());
Assert.AreEqual(++changeCount, _context.GetChangeSet().Inserts.Count);
Assert.AreEqual(0, _context.Orders.Count());

Even though the object is added to the customer's orders collection, the order is not immediately available through the DataContext object (represented by the "_context" variable). Rather, it is listed in the ChangeSet object returned from the GetChangeSet() method. The ChangeSet object contains a collection of the inserted, deleted, and updated objects not yet submitted to the database, which this orders object is one of them.  After the following:

Listing 4

_context.SubmitChanges();
Assert.AreEqual(0, _context.GetChangeSet().Inserts.Count());
Assert.AreEqual(1, customer.Orders.Count());
Assert.IsNotNull(customer.Orders.SingleOrDefault(o => 
     o.ReferenceNumber == "X4590X"));

The order being added is no longer in the change set; it is not a child of the customer object and readily available in the orders table.

Every Database Management System supports default values; whenever a field is left out of an insert statement, the database automatically inserts a default for that row/column. This saves some time, especially when entering data manually into the database. However, there is a challenge with this: default values do not seem to work well in LINQ. Let me explain.

Suppose you have a column (CreatedDate) in the database system that uses the datetime SQL Server data type. This translates to DateTime in the .NET framework, and is required and has a default value of getdate(). The challenge is this: by default, a DateTime value is represented as a value of "1/1/0001" in the .NET framework. This is a valid date and the database system tries to supply this date to SQL Server in the insert.

SQL Server rejects this value because the created date should be a value between 1/1/1753 and 12/31/9999 for the datetime type, which causes an exception and unfortunately no details about the business object type name or property name are provided, meaning that someone may have a flashback of the COBOL days in trying to hunt down a missing punctuation.

With that said, when instantiating business objects, you have to supply the required properties to the object, even though default values are already specified. Identities are an exception because this is a different construct, and LINQ to SQL handles this accordingly. However, if you use GUID's with your tables as the primary key, you have to call Guid.NewGuid() manually to create an ID.

Listing 5

order.OrderKey = Guid.NewGuid();
order.CreatedDate = DateTime.Now;

In a sense, this is better than using an integer identity value because the primary key value is immediately known, and it is not as necessary to call the context's SubmitChanges() method right away.

SQL Server supports the auto-generation of primary key values through the identity setting.  When creating a new LINQ business object, the value of the primary key is zero; meaning no value has been assigned. Calling SubmitChanges on the DataContext refreshes this value with the auto-generated value assigned from the backend database. Let us take a look at an example.

When creating a new business object, the value of the primary key is illustrated below.

Listing 6

Product product = new Product();
product.Name = "Value";

//Assign remaining values.

Listing 7

Assert.AreEqual(0, product.ProductKey);

But after the call to submit changes, the value is updated to:

_context.SubmitChanges();
Assert.AreEqual(6, product.ProductKey);

The product key is assigned to an actual value after the product is refreshed.

When using an operation like the Where() method in the following code:

Listing 8

_context.Customers.Where(i => i.AccountNumber == "T2341003");

The results that come back are a DataQuery object. This object is not null for queries that even return no results. So the following code works:

Listing 9

customers = _context.Customers.Where(i => i.AccountNumber == "XXXX");
Assert.AreEqual(0, customers.Count());
 
count = 0;
foreach (Customer customer in customers)
{
      count++;
      //Test that iterating through collection is OK
}
Assert.AreEqual(0, count);

What this means is that the Where() method returns no records, because the account number does not exist. The result count is an empty query, but you can continue to use this object freely, as shown above, and not have to worry about a null reference exception.

By default, LINQ objects are loaded in a deferred nature; this means that any child objects of a parent are not loaded until they are accessed. So for the Customer object, once the Orders collection is accessed, these orders are loaded at this point, rather than loading them all at the same time. This means another database query pulls back the orders data, involving two hits to the database.

However, it is possible that loading the parent data also loads the child data immediately, using the following setup.

Listing 10

DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Employee>(i => i.EmployeePositions);
_context.LoadOptions = options;

The DataContext class has a LoadOptions property that specifies any children to load for a given parent.  So, when loading employee information, any related records in EmployeePositions (a join table designed for a many-to-many join situation) are also loaded. This prevents a second query for any relationship data. You must define load options before querying against the context.

You may wonder whether that is better; really, it depends on your situation. I cannot say that in every situation, immediate loading is a better approach. Sometimes, you do not need all of the related data, so a deferred approach is better. However, other times, you will use all of the employee and related position records, and so immediate loading would help reduce the number of calls to the database.

Ideally, it is the number of database calls that immediate loading is trying to reduce, and whether that really reduces the number of calls depends on the type of application, the size of the data coming back, the relationships between the data, etc.

I have tried to incorporate some specific detail about the creation of LINQ objects, when the objects are loaded, and discuss how the deferred and immediate loading setup works. I hope this helps when you use LINQ-to-SQL, to understand how objects are instantiated and work with each other.