|
LINQ to SQL Instantiation
|
by Brian Mains
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
40922/
61
|
|
|
Introduction |
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?).
|
Business Object Assignments |
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.
|
Adding Child Objects |
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.
|
Default Values |
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.
|
Automatic Primary Keys |
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.
|
Query Operations |
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.
|
Deferred vs. Immediate Loading |
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.
|
Conclusion |
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.
|
|
|
|
|
|