August 2007 - Posts
Since SRT Solutions has opened our own office (with user group meeting space in mind), AADND is moving its meeting space.
Starting in September, AADND will meet at 206 S. Fifth St, suite 200. Same city: Ann Arbor, MI 48104.
Here's a link to the new location:
http://local.live.com/?v=2&sp=Point.r1d0xp80n11f_206%20S%205th%20Ave%2C%20Ann%20Arbor%2C%20MI%2048104-2229%2C%20United%20States___&encType=1
I've posted all the slides and demos for my talks in Ohio (Dayton .NET User Group and Columbus .NET User Group)
You can grab all the files here: http://srtsolutions.com/files/folders/presentations/default.aspx
I think it's because I'm in 'writing avoidance mode' again.
Anyway:
I AM
62%
OPTIMUS PRIMETake the Transformers Quiz
Throughout the LINQ to SQL samples, I've been stressing the deferred execution nature of LINQ queries. To refresh, deferred execution means that when you create a LINQ query, the query expression holds an Expression Tree, not the results of the query. The expression tree gets converted to SQL, to be executed at the server. The actual results are only returned when the query is executed.
That is the behavior you want most of the time. It minimizes the number of trips to the database. You see, each additional query expression merely creates a new Expression Tree (which still contains the old BLOCKED EXPRESSION, rather than making another trip to the database to grab new content. You only make a trip to the database when it's time to actually enumerate all those entries.
But, other times, you want to operate on the series locally. When that's the case, you can direct LINQ to SQL to execute the query and return the results immediately. There are a number of APIs that do this for you. They differ in what kind of a collection they build.
First, there is the AsEnumerable() method. AsEnumerable simply changes the compile time type of a query from something that implements IEnumerable<T> to IEnumerable<T>. Sounds pretty useless, right? Not so fast. AsEnumerable() does do something: It removes any specific implementations a class may have made to any extension method defined on IEnumerable<T> in the Queryable class. It sounds very strange, but an example will make it very clear.
This query:
var q = from p in db.Products
where p.UnitPrice > 10m
select p;
uses the Table<T>.Where() method. That's because db.Products is a database table. The result of that behavior means that the query is translated into SQL, and gets executed at the server.
However, AsEnumerable() changes that:
var q = from p in db.Products.AsEnumerable()
where p.UnitPrice > 10m
select p;
Now, AsEnumerable() means the compiler views the db.Products as an IEnumerable<T>, not as a Table<T>. The result is that your query executes locally. However, you still need to get all the products. In this version, the entire product table is returned because you'll enumerate the entire table. The filter (p.UnitPrice > 10m) will be executed client side.
One reason you would do this is that sometimes you might make a query that can't be converted to T SQL. Consider this bit of code:
public void LinqToSqlConversion01() {
var q =
from p in db.Products.AsEnumerable()
where isValidProduct(p)
select p;
ObjectDumper.Write(q);
}
private bool isValidProduct(Product p) {
return p.ProductName.LastIndexOf('C') == 0;
}
Well, isValidProduct can't very well be turned into T-SQL, so you need the AsEnumerable() conversion on the table. (If you remove it, the example above will throw an exception).
AsEnumerable() doesn't actually change anything, it just modifies how the compiler views a sequence. Two other methods: ToArray() and ToList() actually modify the sequence, copying it into an Array, or List<T>, respectively. Why would you do that? Well, that caches the results of the query on the client side. That would mean you could continue to reuse that collection repeatedly in your application. For example, in the Northwind database, I'd consider grabbing the entire categories table, rather than querying it each time.
Finally, there is ToDictionary(). ToDictionary() translates a sequence into a dictionary, which stores key / value pairs. You write one, or two lambda expressions that describe how to map your sequence into a set of keys, and optional values.
For example, this creates a set of keys containing the product ID, and the value is the product.
var q =
from p in db.Products
where p.UnitsInStock <= p.ReorderLevel && !p.Discontinued
select p;
Dictionary<int, Product> qDictionary = q.ToDictionary(p => p.ProductID);
This version creates a set of Key / Value pairs where the key is the ID, and the value is an anonymous type containing the product name, the supplier, and the units in stock:
var q =
from p in db.Products
where p.UnitsInStock <= p.ReorderLevel && !p.Discontinued
select p;
// Var needed now because of the anonymous type
var qDictionary = q.ToDictionary(p => p.ProductID,
p => new { p.ProductName, p.Supplier.CompanyName, p.UnitsInStock });
That's all for today. See you soon with direct SQL in LINQ to SQL.
Let's continue looking at LINQ 2 SQL and how it handles object identity vs. database identity, and how you can control loading related objects.
The object identity concept is pretty straightforward: If you execute the same query more than once, you will receive the same object. Here's a quick example:
Customer cust1 = db.Customers.First(c => c.CustomerID == "BONAP");
Customer cust2 = db.Customers.First(c => c.CustomerID == "BONAP");
Console.WriteLine("cust1 and cust2 refer to the same object in memory: {0}",
Object.ReferenceEquals(cust1, cust2));
This is one of the fundamental concepts for the Object – Relational Mapping (ORM) functionality in LINQ. The same query always returns the same record from the database. If the same LINQ query always returns a reference to the same object, then any routine that examines (or updates) that object will be updating the same in memory copy. This simplifies updates: If your application retrieves the same record from the DB in two different locations in code, even if both locations modify the object in memory, your application still only has one copy of the DB record to update. (You do still have concurrency issues if another application modifies the record after you've loaded it, but your application is safe).
It's important to note that this behavior is only guaranteed if your query returns the same class type. If you change the select statement to create some columns from the table, that's a different object. In particular, two anonymous types that came from the same database record will not match.
However, it doesn't matter if the query is that's executed is the same. It only matters if the record returned from the DB is the same. These two queries return the same object:
Customer cust1 = db.Customers.First(c => c.CustomerID == "BONAP");
Customer cust2 = (
from o in db.Orders
where o.Customer.CustomerID == "BONAP"
select o )
.First()
.Customer;
Notice that completely different logic is used to get to the customer with the "BONAP" customer ID. It doesn't matter.LINQ2SQL still returns the exact same cached object.
A similar subject is the concept of deferred and eager loading. Obviously, your database has several relationships between different tables. (In Northwind, customers have orders, orders have order details, and so on). You can direct LINQ 2 SQL in terms of whether or not related records are loaded. By default, records in related tables are not loaded. Therefore, this bit of logic will make N +1 queries to the database, where N is the number of customers that match the initial query:
var custs =
from c in db.Customers
where c.City == "Sao Paulo"
select c;
foreach (var cust in custs) {
foreach (var ord in cust.Orders) {
Console.WriteLine("CustomerID {0} has an OrderID {1}.",
cust.CustomerID, ord.OrderID);
The first query returns all the matching customers. Later, as the customers are enumerated, each customer's orders are retrieved in a separate query.
You can change that behavior by specifying DataLoadOptions. Below, the data load options specify that everytime a customer is loaded, the orders for that customer should be prefetched. If multiple customers are returned from an order, *all* their orders are also returned.
Northwind db2 = new Northwind(connString);
DataLoadOptions ds = new DataLoadOptions();
ds.LoadWith<nwind.Customer>(p => p.Orders);
db2.LoadOptions = ds;
var custs = (
from c in db.Customers
where c.City == "Sao Paulo"
select c);
foreach (var cust in custs) {
foreach (var ord in cust.Orders) {
Console.WriteLine("CustomerID {0} has an OrderID {1}.",
cust.CustomerID, ord.OrderID);
You can specify multiple relationships for eager loader, simply be specifying more LoadWith relationships. All are obeyed in each query:
Northwind db2 = new Northwind(connString);
DataLoadOptions ds = new DataLoadOptions();
ds.LoadWith<Customer>(p => p.Orders);
ds.LoadWith<Order>(p => p.OrderDetails);
db2.LoadOptions = ds;
var custs = (
from c in db.Customers
where c.City == "London"
select c );
foreach (var cust in custs) {
foreach (var ord in cust.Orders) {
foreach (var orderDetail in ord.OrderDetails) {
Console.WriteLine(
"CustomerID {0} has an OrderID {1} with ProductID {2} that has name {3}.",
cust.CustomerID, ord.OrderID,
orderDetail.ProductID, orderDetail.Product.ProductName);
}
}
}
That's enough for today. Next, I'll look at more of the ways where you can specify how LINQ2SQL works with your database, and whether queries are executed at the database server, or client side.
Yes, it's time to do more LINQ investigation.
Beta 2 is out, and now all the sample query applications are delivered as part of the normal install.
The samples are all located in C:\Program Files\Microsoft Visual Studio 9.0\Samples\1033 in the standard install. You can (and should) move the zip files under your project directory and from there you can build and run them.
One important caveat: You need to run the LINQ 2 SQL samples as Administrator due to the way the samples access the SQL database.
In this blog post, I'll cover a few of the key points on the string and date functions in LINQ 2 SQL. There are quite a few samples in this section, and they all demonstrate one key point: Whenever the LINQ 2 SQL libraries can translate a .NET string function to a SQL statement, they do the translation, and the SQL statement is executed at the database. For example, this quey:
var q =
from c in db.Customers
where c.ContactName.StartsWith("Maria")
select c;
translates to this SQL:
SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[ContactName] LIKE @p0
And, this one:
var q =
from e in db.Employees
select e.HomePhone.Substring(0, 5).Trim();
Becomes this SQL:
SELECT LTRIM(RTRIM(SUBSTRING([t0].[HomePhone], @p0 + 1, @p1))) AS [value]
FROM [dbo].[Employees] AS [t0]
Finally, this query, which replaces an abbreviation for company with the full text:
var q =
from s in db.Suppliers
select new {
s.CompanyName,
Country = s.Country.Replace("UK", "United Kingdom")
.Replace("USA", "United States of America")
};
Becomes this SQL:
SELECT [t0].[CompanyName], REPLACE(REPLACE([t0].[Country], @p0, @p1), @p2, @p3) AS [value]
FROM [dbo].[Suppliers] AS [t0]
Whenever possible, the string functions become SQL statements.
The same is true for DateTime functions. This query:
var q =
from o in db.Orders
where o.OrderDate.Value.Year == 1997
select o;
Becomes this SQL:
SELECT [t0].[OrderID], [t0].[CustomerID], [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate], [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName], [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], [t0].[ShipPostalCode], [t0].[ShipCountry]
FROM [dbo].[Orders] AS [t0]
WHERE DATEPART(Year, [t0].[OrderDate]) = @p0
Next, I'll look at how object identity is used to track database identity.
This blog has just been moved from its previous address to www.srtsolutions.com/blogs/billwagner
That's because we've moved from our website from a custom application written by our friends and colleagues at Myst Technology. It's now using Community Server.
The server should be automatically redirecting you here to the new address (sorry about the duplicates on the most recent posts, but that's the rules.
So, why the change?
Well, it's that MyST has been focusing on an interesting business called blogsite. The technology, and the business, is focused on building a blog cloud around your business. It's a fantastic tool for providing visibility for your business. However, that focus on blogs, and simplicity for business people means that it doesn't contain some of the features we want: forums for attendees of our events, file uploads for samples and presentations, and the blog engine is similar. Over the coming weeks and months, you'll see those features added.
I do want to make it very clear that this change is not a reflection of their technology, You can see an impressive list of blogsite customers here.