Archive for June, 2008

Controlling eager and lazy loading in LightSpeed models

LightSpeed’s system of eager and lazy loading provides an extremely convenient and efficient way to tune performance without troubling application code with the details of when and how to access the database. The key concept here is that of named aggregates, an abstraction layer which allows an application to say what it wants to use the entity for, and lets the entity designer map that down to specific fields to load.

Let’s review how this works. By default, when you load an entity (using Find, FindOne or LINQ), LightSpeed retrieves from the database all the value fields of that entity, but doesn’t retrieve the data for any associated entities or collections. For example, when you load an Employee entity, LightSpeed fetches the Name, ManagerId and Photo, but doesn’t load the Manager entity, or populate the DirectReports collection. When somebody references these associations, LightSpeed goes back to the database at that point.

Sometimes it makes sense to tune this default based on expected usage. If the main consumer of Employee entities is a program which draws org charts, it may be a good idea to load the DirectReports collection while you’re fetching the Employee, because otherwise you’ll just have to go back a microsecond later. You can do this using the EagerLoad attribute.

Similarly, a program for drawing org charts probably doesn’t need to load the Photo property, which might be quite a large bitmap, all the time — only when somebody actually chooses to drill in and look at the photo. We could add a “lazy load” attribute for this, but that’s not always a great solution because often an entity has several fields that are often used together. For example, our Employee record might include fields that are of interest to the holiday approval page, such as the date the employee started work, the number of vacation days outstanding, etc. We don’t want to load these all the time, because most users of Employee objects don’t care; but naively marking each with our hypothetical LazyLoad attribute would mean that the holiday approval page would trigger several round-trips to the database, fetching one field at a time. To avoid this, LightSpeed implements lazy loading as “eager loading of named aggregates.” We label each holiday-specific fields as [EagerLoad("Holiday")]. Then in the holiday approval page, when we load the Employee, we specify the “Holiday” aggregate. LightSpeed eager-loads any fields tagged as [EagerLoad("Holiday")], resulting in only a single visit to the database to fetch everything the holiday page needs. Fields that are tagged as part of an aggregate other than “Holiday” are left for lazy-loading. A field can be part of more than one named aggregate. The employee start date, for example, might be of interest to the performance review subsystem as well as the holiday subsystem.

So by applying EagerLoad attributes to associations, collections and fields, we can make very efficient use of the database without coupling the application layer to the details of the optimisations. With LightSpeed 2, however, you may be using the visual model designer and getting it to create your code for you, so you can’t add the required attribute or attributes by hand. Instead, you can set up the eager loading control via the designer.

To mark a collection as (always) eager-loaded, select the association, go to the Properties window, and set EagerLoad to true:

To mark a collection or property as part of a named eager-load aggregate, select the association or property, go to the Properties window, and enter the name in the Aggregates box:

If the item is part of more than one aggregate, separate them with semi-colons.

You can also mark properties as part of an aggregate via the Model Explorer: right-click the property and choose Add Aggregate.

If you mark an association as part of an aggregate, you don’t also need to set EagerLoad to true, even though the aggregate is represented at the code level by the EagerLoadAttribute. In fact, if you set EagerLoad on an association, there’s no point having named aggregates on it as well, because the association will always be eager-loaded.

Add your comments
Author icon

Posted by Ivan Towlson on 30 June 2008

Routed events in WPF

Tagged as WPF

One of our WPF Property Grid customers was, for various reasons, interested in knowing when the user expanded a node. My first thought was, “We don’t expose an event for that.” My second thought was, “And it will be pretty hairy to get access to the event from the underlying template.” My third thought was, “Oh, wait. That’s easy.”

The reason is that a lot of events in WPF are actually routed events rather than straight CLR events. A CLR event is essentially a member which happens to be of delegate type and has a bit of extra metadata: it’s a way for a component to invoke callbacks. (Actually, a CLR event is just the bit of metadata; it’s just that most languages also create a backing member unless you say otherwise.) So if you want to know about an event, you need to get a reference to the object that declares the event so as to register your callback (event handler).

A routed event, on the other hand, is a first-class object which the WPF infrastructure can pass around the visual tree. To handle a routed event, therefore, you don’t need to have a reference to the object that declares and raises the event. You just need to be able to talk to something through which the event will pass.

Why is this important? Because our customer’s problem was that he wanted to handle an event that was happening somewhere deep inside the property grid’s control template, and routed events solve that problem. Getting hold of an individual TreeViewItem inside the grid in order to hook up the event would be hard: the grid manufactures them internally, and intercepting that manufacturing process can be messy and tedious. But the TreeViewItem’s Expanded event is actually a routed event, so we don’t need to register our event handler directly with each TreeViewItem. When a TreeViewItem raises the Expanded event, WPF bubbles the event upwards, and we can therefore register our event handler anywhere above the TreeViewItem in the visual tree. Specifically, we can register the event handler on the grid itself:

grid.AddHandler(TreeViewItem.ExpandedEvent, new RoutedEventHandler(OnNodeExpanded));

Or in XAML using an attached property-like syntax:

<ms:PropertyGrid TreeViewItem.Expanded="OnNodeExpanded" />

Our OnNodeExpanded routine will now get called whenever any node in the grid is expanded. And we haven’t need to get references to the individual TreeViewItem to make this happen; nor have we needed to add an event to the grid itself to report a node expansion.

One thing to watch out for when handling an event above the level that it is raised is that the sender parameter in the event handler will be the place where the event handler was attached. In the examples above, the sender passed to OnNodeExpanded will be the PropertyGrid object. To find the object that actually raised the event — the TreeViewItem which is expanding — you need to look at the OriginalSource property of the RoutedEventArgs.

By the way, you often won’t notice the difference between CLR events and routed events. For example, if you do have a reference to a TreeViewItem, you don’t need to mess around with AddHandler and ExpandedEvent: you can call tvi.Expanded += MyEventHandler just as if Expanded were a CLR event. This is because Expanded is a CLR event. Classes that declare routed events typically also declare corresponding CLR events, precisely so that the usual language event syntax continues to work. The idea is similar to dependency properties: just as, say, Window.TitleProperty is a DependencyProperty and Window.Title is a CLR property to allow convenient code-based access, TreeViewItem.ExpandedEvent is a RoutedEvent and TreeViewItem.Expanded is a CLR event. This CLR event is pure metadata, with no backing field. Backing is delegated to the routed event manager.

One of the big architectural themes of WPF is decoupling the code (behaviour) and API (usage) of controls from their visual tree and appearance. Data binding and commands are part of this decoupling. Routed events round out the set by freeing control developers from having to worry about surfacing template-dependent events, and by enabling control consumers to handle events happening within templated controls without requiring the consumer code to get intimate with the contents of the template.

Add your comments
Author icon

Posted by Ivan Towlson on 26 June 2008

Using SQL functions in LightSpeed

Tagged as LightSpeed, News

LightSpeed 2 adds the ability to use SQL functions and arithmetic operators in your queries. We expect that most of the time, you’ll do this through LINQ, but if you’re targeting .NET 2.0 or you want to use a SQL function that doesn’t have a LINQ equivalent, it can be useful to know what’s going on behind the scenes.

For example, suppose that we want to retrieve a list of titles, but we want them all in lower case, perhaps as a canonical form for some sort of comparison. If we’re using LINQ, we can just call the .NET String.ToLower() function:

var query = from c in unitOfWork.Contributions
            select new { c.Id, c.Title.ToLower() };

Although this looks like a .NET call, LightSpeed translates it into a call to the SQL ==LOWER()== function and has it executed on the database.

If we’re not using LINQ, we can still get at the ==LOWER()== function, but it’s slightly harder work:

Query query = new Query(typeof(Contribution));
query.Projection.Add("Id");
query.Projection.Add(Entity.Attribute("Title").Function("LOWER"));
IDataReader results = unitOfWork.Project(query);

You can also use the .Function() syntax in query expressions to apply the function in SQL WHERE and ORDER BY clauses.

Arithmetic operators introduce an additional complexity: an arithmetic operator has two arguments. Consider an entity with UnitPrice and Quantity attributes. If we want to get the total price, namely UnitPrice times Quantity, we need to use the SQL * (multiplication) operator. Again, in LINQ, we can do this using our language’s multiplication operator, but if we want to do it without LINQ, it looks like this:

Query query = new Query(typeof(SalesLine));
query.Projection.Add("Id");
query.Projection.Add(Entity.Attribute("UnitPrice").Function(
  "*", Entity.Attribute("Quantity")));
IDataReader results = unitOfWork.Project(query);

Note the additional argument to the .Function() method. To call a SQL function with three or more arguments, such as REPLACE, just keep adding arguments.

You might notice that the * example uses the same syntax as the LOWER example even though * is an infix operator and LOWER is a function: that is, * goes between its arguments, as in UnitPrice * Quantity, whereas LOWER goes before its argument and needs brackets, as in ==LOWER(Title)==. LightSpeed knows which “functions” are actually infix operators and takes care of this for you.

(You might be wondering why we don’t support an infix notation in the LightSpeed query API? Why can’t you write Entity.Attribute(“UnitPrice”) * Entity.Attribute(“Quantity”)? The answer is that we expect that most people who need to do this will use LINQ. So optimising the experience of using infix operators in the query API hasn’t been a high priority.)

One last wrinkle. We can only apply functions to expressions. So far, we’ve always had an expression to go in as the first argument of the function. What happens if we want the expression to appear somewhere else in the argument list? For example, in PostgreSQL, if we want the year part of a date, we’d need to generate SQL such as DATE_PART(‘year’, DateOfBirth).

For this situation, you can supply an additional argument to .Function() specifying where in the argument list the expression should go:

Entity.Attribute("DateOfBirth").Function(1, "DATE_PART", "year")

Again, we’d generally recommend and expect that you use LINQ for this sort of thing — in this case using the .NET DateTime.Year property. This is not only easier to write and read, it also abstracts away the different function names and syntaxes between the different database engines.

Still, if ever you need the ability to get down to the raw SQL level, now you know how.

Add your comments
Author icon

Posted by Ivan Towlson on 24 June 2008

Custom sorting WPF collection views and the WPF Property Grid

Tagged as WPF, WPF Property Grid

Back in January, I wrote about sorting and grouping in the WPF Property Grid. I described how to sort entries using ICollectionView.SortDescriptions.

The problem with this is that SortDescription is a very limited beast: you can specify a property to sort on, and whether the sort should be ascending or descending, and that’s it. If, for example, you want to sort the entries into an order other than alphabetical, you’re out of luck. If you’re using ItemsSource and the ObservableDictionary, there’s a trick we found to work around this. But this trick can’t be extended to when you have a SelectedObject and are displaying its properties. This isn’t just a problem for the grid; it’s potentially an issue for any WPF items control that wants to display elements in a custom order.

Well, today I learned about a WPF sorting feature that I didn’t know about before. Although ICollectionView’s sort support is limited to SortDescriptions, the ListCollectionView class has a CustomSort property, which you can set to an arbitrary IComparer. And this completely solves the custom sorting issue. And it solves it for the grid, too, because the grid’s BindingView collection view is a ListCollectionView.

Let’s put this into practice in the WPF Property Grid. Suppose that we have defined a custom DisplayPositionAttribute which can be applied to properties to specify in which order they should be displayed. In this case, we want Age and Treachery to beat Youth and Innocence as they do every time:

public class Person : INotifyPropertyChanged
{
  [DisplayPosition(20)]
  public int Treachery { ... }
 
  [DisplayPosition(10)]
  public int Age { ... }
 
  [DisplayPosition(40)]
  public int Innocence { ... }
 
  [DisplayPosition(30)]
  public int Youth { ... }
}

Since BindingView is a list of PropertyGridRows, our comparer is going to get given two PropertyGridRow objects. For each object, it will get the property metadata and extract the value of the DisplayPositionAttribute. It then needs to return the comparison of those values, which due to the design of the IComparer interface is easily done by subtracting them.

// Error checking omitted for clarity
public class RowComparer : IComparer
{
  public int Compare(object x, object y)
  {
    PropertyGridRow row1 = x as PropertyGridRow;
    PropertyGridRow row2 = y as PropertyGridRow;
 
    int index1 = GetDisplayPositionAttribute(row1.Node.Property);
    int index2 = GetDisplayPositionAttribute(row2.Node.Property);
    return index1 - index2;
  }
 
  private static int GetDisplayPositionAttribute(IPropertyInfo property)
  {
    object attr = property.GetCustomAttributes(typeof(DisplayPositionAttribute), true)[0];
    return ((DisplayPositionAttribute)attr).Position;
  }
}

Of course you can use whatever ordering mechanism you want — for example, looking in a text file containing the property names in the order you want them to appear, ordering by data type, etc.

Now all we need to do is to tell the BindingView.DefaultView to use this custom comparer to determine the sort order. This requires us to cast it to ListCollectionView, but is otherwise trivial:

ListCollectionView collectionView = (ListCollectionView)(pg.BindingView.DefaultView);
collectionView.CustomSort = new RowComparer();

If you’ll pardon the expression: Sorted!

Add your comments
Author icon

Posted by Ivan Towlson on 19 June 2008

Schema round-tripping in the LightSpeed designer

Tagged as LightSpeed

LightSpeed 2 includes a designer which you can use to create your domain models visually by dragging and dropping. As you update the model, LightSpeed automatically generates the entities as C# or Visual Basic code.

However, the entity code is only one half of the equation. What about the database side of things? To help you keep your database and your model in sync, the LightSpeed designer includes a feature called “schema round-tripping,” which enables you to update your model to match your database schema, or update your database schema to match your model.

For example, suppose you’re designing a Product entity, and you add a Description attribute. Without schema round-tripping, you would go into a separate database design tool (such as SQL Server Management Studio or MySQL Administrator) to add a column to the Products table in the database. Using the LightSpeed designer, however, you can right-click the Product shape and choose Update Database. LightSpeed determines that the Description attribute doesn’t have a corresponding database column, and offers to create one:

If you’re happy that this is the right change, LightSpeed applies it:

Schema round-tripping works in both directions: if you had added the Description attribute to your database first, you could have chosen Update From Source, and LightSpeed would have offered to add it to your model. So you can design in a data-centric way or an object-centric way, whichever you feel most comfortable with.

Schema round-tripping can help you with the following changes:

Adding a new attribute / column
Deleting an attribute / column
Changing the data type of an attribute / column
Adding a one-to-many association / foreign key
Deleting a one-to-many association / foreign key
Adding a new entity / table
Changing the identity type of an entity / table

The full feature set is available on SQL Server, Oracle and PostgreSQL. There are currently some limitations in MySQL and SQLite support. If you run into these limitations, though, don’t forget that you can instead make changes through your database tools and use Update From Source to get them into LightSpeed. Alternatively, if you do prefer to design model-first, you can use the Update Database dialog as a checklist, even if you have to make the changes manually.

To get schema round-tripping to work, you need to tell LightSpeed which database to synchronise against. You can set this up manually by clicking on the designer background and configuring the provider and connection string in the Properties window. LightSpeed will automatically configure the database if you drag a table from Server Explorer (and the model doesn’t already have synchronisation set up). One gotcha is that, in order to match up entities with database tables, LightSpeed needs to know whether you are using the convention that table names are pluralised or not (that is, should it look for a Person table or a People table). If you find that LightSpeed keeps offering to create a Person table when the database already contains a perfectly good People table, click the designer background, go to Properties and check the pluralisation setting.

Schema round-tripping isn’t a 100% synchronisation. For example, it doesn’t currently detect if the length of a text field is out of sync between the database and the model. Nor does it have the facilities of a production-quality database designer. For example, it doesn’t generate SQL scripts that can be saved and run in your other environments, and in the 2.0 release it doesn’t have great support for bulk updates such as adding multiple tables with relationships between them all in one go. It’s aimed at enabling you to rapidly make small, frequent, iterative changes — adding and removing attributes or relationships, adding new entities, changing data types — and thereby to speed up your development cycle. Within those limitations, however, it will help you more rapidly prototype, prove, evolve and test your domain model.

Schema round-tripping is included in all editions of LightSpeed, including the free Express edition.