Category: Uncategorized

In Beta 2, there are a number of ways in which linq allows you to see what it is generating under the covers.   Two new methods, GetChangeSet() and GetCommand(), which replace GetChangeText() and GetQueryText() allow you to interact with changes as objects.  Expression returns the expression tree which has been built for the query, while doing a simple ToString() on a query will return you the T-SQL query.

If we start with the Expression property.  This is a public property on a query var type which displays the expression tree built underneath.

I’ve created a simple query to return all the orders in the Northwind database.

DataClasses1DataContext db = new DataClasses1DataContext();
var query = from o in db.Orders
                  select o;

For this, query.Expression contains “Table(Order).Select(o => o)”.   For more complex queries, then this is really interesting to see what is actually being created.  Take this query:

var query = from o in db.Orders
                   join od in db.Order_Details
                   on o.OrderID equals od.OrderID
                   where od.Quantity > 100
                   select o;

This would be a lot harder to express as a expression tree.  The Expression property for this looks like:

Table(Order).Join(Table(Order_Detail), o => o.OrderID, od => od.OrderID, (o, od) => new <>f__AnonymousType0`2(o = o, od = od)).Where(<>h__TransparentIdentifier0 => (Convert(<>h__TransparentIdentifier0.od.Quantity) > 100)).Select(<>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.o)

While not easy to read, you can easily get it to a more readable state:

db.Orders.Join(db.Order_Details, o => o.OrderID, od => od.OrderID, (o, od) => new { order = o, orderDetail = od }).Where(anon => anon.orderDetail.Quantity > 100).Select((anon) => new { order = anon.order, orderDetail = anon.orderDetail });

This is really useful if you are interested in the tree being built and a good learning aid if your interested in what the equivalent tree looks like.  I;m sure there are many other uses by having access to the expression tree.

Moving onto ToString(),  query.ToString() contains the query code which will be executed against the database.  For the simple orders query, it would hold:

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]

Again, useful when learning and debugging.  However, ToString() is not a great way to get the SQL command.  A better way is to use GetCommand.

GetCommand is a method on a DataContext which takes an IQueryable parameter and returns a DbCommand object.  Once we have the DbCommand object, we can view and edit properties as required.  As this is replacing GetQueryText(), the new command would be:

db.GetCommand(query).CommandText

This returns the same value as ToString – at the moment.  Other properties include, being able to access parameters, the SqlConnection object and transaction object. 

Finally, GetChangeSet() which returns an IList read-only collection of objects which have either been added, modified, or removed since the last SubmitChanges().  The different properties are:

IList