I've been playing just a little today with the "Orcas" March CTP and, specifically, trying to update some of my LINQ to SQL bits to this later version.

I don't know how many of the changes that I'm seeing are to do with the March CTP or how many were already there because I was previously on the May CTP from last year so I've noticed a lot of things most of which look to be cosmetic name changes.

Some things that hit me.

1) DataShape class.

Previously, if we're working with something like Northwind's customers and orders then by default I get deferred loading on a customer's orders collection and I could affect that by using the Including operator as in;

  var query =
   (from c in ctx.Customers
    select c).Including(c => c.orders);

In the new CTP this has changed. I can switch on/off DeferredLoadingEnabled on the DataContext and so if I do;

  NorthwindDataContext ctx =
   new NorthwindDataContext("server=.;database=northwind;integrated security=sspi");

  ctx.DeferredLoadingEnabled = false;

  var query = from c in ctx.Customers
              select c;

  foreach (Customer c in query)
  {
   foreach (Order o in c.Orders)
   {
    Console.WriteLine(o.OrderID);
   }
  }

then I'll find that the Orders collection is not populated. If I want to control how it gets populated then I can (it seems) use a DataShape on the DataContext as in;

  NorthwindDataContext ctx =
   new NorthwindDataContext("server=.;database=northwind;integrated security=sspi");

  ctx.DeferredLoadingEnabled = false;

  DataShape shape = new DataShape();
  shape.LoadWith<Customer>(c => c.Orders);

  ctx.Shape = shape;

  var query = from c in ctx.Customers
              select c;

  foreach (Customer c in query)
  {
   foreach (Order o in c.Orders)
   {
    Console.WriteLine(o.OrderID);
   }
  }

2) ObjectTrackingEnabled

On the DataContext there looks to be a new method called ObjectTrackingEnabled. Object tracking is what makes sure that your objects stay consistent in memory (based off primary key values) so that if you do something like this;

  NorthwindDataContext ctx =
   new NorthwindDataContext("server=.;database=northwind;integrated security=sspi");

  Customer c =
   ctx.Customers.Single(cust => cust.CustomerID == "ALFKI");

  c.Country = "UK";

  foreach (Customer c2 in ctx.Customers)
  {
   if (c2.CustomerID == "ALFKI")
   {
    Debug.Assert(c2.Country == "UK");
   }
  }  
 }

then that assertion does not fire because even though that second query forces a trip to the database when we come to find "ALFKI" we ensure that we use the later version of the object from memory rather than the one from the return trip to the DB.

This clearly involves some overhead and if you're doing read-only stuff then you can switch off ObjectTrackingEnabled on the context and the overhead goes away.

If you then try and do an update and call SubmitChanges you'll find that can't work without object tracking and you'll get an exception.

Other Stuff - Paging with LINQ to SQL

I was at Niels talk at DevWeek yesterday and a few interesting questions came up.

One was around whether we can page records with LINQ and no-one really thought it'd be a possibility but it looks like it is (not claiming this as a great discovery as it's in the samples :-)).

If you do something like;

 NorthwindDataContext ctx =
   new NorthwindDataContext("server=.;database=northwind;integrated security=sspi");

  var query = 
   (from o in ctx.Orders 
    orderby o.OrderID descending 
    select o).Skip(100).Take(10);

  foreach (var v in query)
  {
   Console.WriteLine(v.OrderID);
  }

then that will achieve it and the query it fires to the SQL Server uses ROW_NUMBER() and OVER against my SQL Server 2005 database.

Other Stuff - Functions with LINQ to SQL

Another interesting question from yesterday - what happens if you've got a function in the database and you want to use it as part of a query? The question was about CLR based functions but, ultimately, functions are functions regardless of TSQL/CLR implementation so I think it stands either way.

With a function like;

CREATE FUNCTION DoubleIt
(  
  @value integer
)
RETURNS Integer
AS
BEGIN
  return @value * 2
END
GO

I can drag and drop this to my Linq to SQL diagram and then my generated class gets annotated with;

        [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
        [global::System.Data.Linq.Function(Name="dbo.DoubleIt")]
        public int DoubleIt([global::System.Data.Linq.Parameter(Name="@value")] global::System.Nullable<int> value) {
            global::System.Data.Linq.Provider.IQueryResults<int> result = this.ExecuteMethodCall<int>(this, ((global::System.Reflection.MethodInfo)(global::System.Reflection.MethodInfo.GetCurrentMethod())), value);
            return global::System.Linq.Enumerable.Single(result);
        }

Note: when I really did this in the CTP it seemed to come out wrong in that the return type was object and the generic types were object and so I hacked it to change them to int because otherwise it seemed to fail. Not sure whether it's a bug and it's right for me to hack it or whether I'm wrong but it "worked for me".

With that now defined I can write a query such as;

  NorthwindDataContext ctx =
   new NorthwindDataContext("server=.;database=northwind;integrated security=sspi");

  var query =
   from o in ctx.Orders
   select ctx.DoubleIt(o.OrderID);

  foreach (int v in query)
  {
   Console.WriteLine(v);
  }

and when I go and study the T-SQL going to my server I see;

SELECT [dbo].[DoubleIt]([t0].[OrderID]) AS [value]
FROM [dbo].[Orders] AS [t0]

How cool is that?

Similarly, if I write;

  NorthwindDataContext ctx =
   new NorthwindDataContext("server=.;database=northwind;integrated security=sspi");

  var query =
   from o in ctx.Orders
   where ctx.DoubleIt(o.OrderID) > 10000
   select o.OrderID;

  foreach (int v in query)
  {
   Console.WriteLine(v);
  }

and that looks to work the same way - again, very cool and a bit unexpected on my part.