Support for DateTimeOffset for PostgreSQL provider

We have introduced DateTimeOffset support for PostgreSQL provider in 5.0.11 RC. Here, we want to explain some restrictions of this RDBMS which affect our implementation of that type support.

We warn you that support for date/time with time zone is different from Oracle Database and Microsoft SQL Server implementations. The last two RDBMS has full support for all parts of date/time including time zone, but PostgreSQL doesn't.

If you want to use DateTimeOffset type in domain models on PostgreSQL storage we need to explain some things you may face with.

Avoiding connection pool fragmentation

Microsofties admitted that some noticeable performance problems are possible while working with MS SQL Server via ADO.Net (link, scroll to Pool Fragmentation). The connection pooling technique used in ADO.Net to optimize and minimize the cost of opening connections reported to be "not so optimal" in particular scenarios.

The root of the problem is that the exact match between connection string and corresponding connection pool is required. Even if 2 connection strings are build from the same key-value pairs but differ in their order, you get 2 connection pools.

As a result, we get connection pool fragmentation, which is a common problem in many Web applications where the application can create a large number of pools that are not freed until the process exits. This leaves a large number of connections open and consuming memory, which results in poor performance. Surprise for large multi-tenant application developers and maintainers!

From their side, Microsoft is not going to do anything about it in foreseeable future, instead they suggest to use a workaround: connect to master database and open the desired database with a separate command:

// Assumes that command is a SqlCommand object and that
// connectionString connects to master.
command.Text = "USE DatabaseName";
using (SqlConnection connection = new SqlConnection(
  connectionString))
  {
    connection.Open();
    command.ExecuteNonQuery();
  }

How can you achieve the same not leaving the zone of comfortable DataObjects.Net API? Time to get familiar with new member of DomainConfiguration class: ConnectionInitializationSql. The main purpose of the member is to literally execute the provided text as DbCommand right after a connection is opened.

Say, we are using Northwind database:


<domain
name="Default"
provider="sqlserver"
connectionString="Server=myServerAddress;Database=master;..."
connectionInitializationSql="USE NORTHWIND"
...>
<domain>


The feature is available in DataObjects.Net 4.6.4. Download DataObjects.Net

Support for in-memory database

I'm not sure whether anyone remembers, but in the beginning of DataObjects.Net 4.0 epoch we had a semi-successful attempt to provide our own in-memory database implementation, which existed for several years but eventually was removed from the product by various reasons.

And guess what? Starting from DataObjects.Net 4.6.4, in-memory database support is back! But this time it is not our own version, but SQLite in-memory mode.

To connect to in-memory database use the following connection string:

<domain
  name="Default"
  provider="sqlite"
  connectionString="Data Source=:memory:"
  ...>
<domain>

The advantages of such kind of storage are the highest possible speed of database-related operations and no necessity to bother with database files on disk. On the contrary, there are also disadvantages, like the absence of multiple connections to database and no real persistence.

SQLite in-memory mode has some requirements that DataObjects.Net must have met. The most important one is that as soon as connection to in-memory database is closed, the database is destroyed and all data is lost.

Before version 4.6.4, DataObjects.Net always built database scheme in a separate connection which was closed after that procedure. Moreover, the ORM didn't keep any connections open at all, closing them as soon as corresponding session is disposed to free up resources and return connection to connection pool. To support the SQL in-memory mode we had to change the connection management layer and make it more flexible and configurable.

And so we did it. Now we open connection to SQL in-memory database only once on domain build and keep it open until domain is disposed, protecting the database from being destroyed, so your data is safe while domain is alive. In the meantime, you may open and close sessions as usual except you shouldn't open concurrent sessions as only one connection is allowed at a time.

Bad practice: using nested sessions with SQLite in-memory database. Session use the same connection concurrently.

  using (var session1 = domain.OpenSession()) {
    using (var t1 = session1.OpenTransaction()) {

      using (var session2 = domain.OpenSession()) {
        // Here you'll get InvalidOperationException 
        // that the connection is used by another session

Good practice: using subsequent sessions. Both sessions use the same connection, but not concurrently.

  using (var session1 = domain.OpenSession()) {
    using (var t1 = session1.OpenTransaction()) {

      // Do some stuff
      t1.Complete();
    }
  }

  using (var session2 = domain.OpenSession()) {
    using (var t2 = session2.OpenTransaction()) {

      // Do some stuff
      t2.Complete();
    }
  }

Enhancement in optimistic concurrency mode

Starting from DataObjects.Net 4.6.4 we are introducing an update to optimistic concurrency feature — server-side version check.

The present API of optimistic concurrency feature in DataObjects.Net with all these VersionSet, VersionCapturer, VersionValidator, whatever is kind of tricky, over-complicated and mind-blowing, so eventually we started moving towards more simple and transparent solution. The server-side version check is the first step in this direction.

The whole idea of server-side version check is obvious: each time an entity is fetched from database, it is associated with a version. On each successful update version is incremented. Each UPDATE command contains additional check for the specific version. Here is an example:

Say, we operate an online book store and use the following simplified Book model with Version field for optimistic concurrency check:

[HierarchyRoot]
public class Book : Entity
{
    [Field, Key]
    public int Id { get; set; }

    [Field(Length = 128)]
    public string Title { get; set; }

    [Field, Version]
    public int Version { get; set; }

    public Book(Session session) : base(session)
    {}
}

As the field is marked with VersionAttribute, DataObjects.Net detects and uses it to store the Book version which is automatically incremented after each successfully committed transaction. Now we want that every update command to include the additional check for version, e.g.:

UPDATE [dbo].[Book]  
SET [Title] = 'DataObjects.Net 4 unleashed' 
WHERE (([Book].[Id] = 123) AND ([Book].[Version] = 2)); 

If this check fails, the Xtensive.Orm.VersionConflictException is thrown with message "Version of entity with key 'Book, (123)' differs from the expected one", so it can be easily detected and handled.

By default, this mode in DataObjects.Net 4.6.4. is switched off to provide compatibility with the older versions. To switch it on we should change SessionConfiguration like this:

var sessionConfig = new SessionConfiguration(
    SessionOptions.ServerProfile | SessionOptions.ValidateEntityVersions);

using (var session = domain.OpenSession(sessionConfig)) {
    // do some stuff 
}

Alternatively, this can be done in configuration file so it is automatically applied to all sessions, like this:

<Xtensive.Orm>
    <domains>
      <domain name="Default"
              upgradeMode="Recreate"
              connectionUrl="sqlserver://localhost/AmazonBookStore">
        ...
        <sessions>
          <session name="Default" options="ServerProfile, ValidateEntityVersions" />
        </sessions>
      </domain>
    </domains>
  </Xtensive.Orm>

Advanced mapping in DataObjects.Net 4.6 — Part 1

This article starts a series of posts covering advanced mapping capabilities introduced in DataObjects.Net 4.6.

Idea

The idea of advanced mappings is simple — to have a way to spread groups of persistent classes among different database schemas and even different databases according to number of mapping rules.

Basics

Advanced mapping configuration is based on set of mapping rules, each of them defines how to map a particular entity. Mapping rule consists of two logical parts:

• condition (defines when the rule is applied)
• mapping (specifies in what database/schema to place the target)

The rules could be specified in configuration file or right in code using fluent API. They are processed in the order they appear, so the first matching rule wins.

In addition to collection of rules, two properties specify default mapping: DefaultSchema and DefaultDatabase. These properties are used as fallback values if no rule matches entity being processed. Note, that 'defaultSchema' option is mandatory if any rules for schemas are specified.

XML configuration

OK, we are done with theory. Let's write some real mapping configurations. The most trivial one is setting the default schema. This is already supported by DataObjects.Net since version 4.3. Let's recall it:

<domain defaultSchema="myapp" ... />

Starting from version 4.6, we can write more precise mapping rules. They are defined in <mappingRules> element inside domain configuration.

<domain defaultSchema="myapp" ... >
  <mappingRules>
    <rule namespace="MyApp.Model.Foo" schema="myapp_foo" />
  </mappingRules>
</domain>

The above example maps all types from namespace 'MyApp.Model.Foo' to schema 'myapp_foo'. All other types are mapped to schema 'myapp' (fallback rule). If you have model with multiple assemblies in might be convenient to map to assembly names instead of namespaces:

<domain defaultSchema="mysite" ... >
  <mappingRules>
    <rule assembly="MySite.Model.Blog" schema="mysite_blog" />
    <rule assembly="MySite.Model.Forum" schema="mysite_forum" />
  </mappingRules>
</domain>

The above example splits model into two schemas. One for persistent types related to blog module. The other one is for forum module. The rest is mapped to 'mysite' schema. 

Fluent configuration

As alternative to XML-based configuration you can programmatically define mapping in code. Mapping rules could be added by using DomainConfiguration.MappingRules collection. Two examples from previous section will look like this:

domainConfiguration.DefaultSchema = "myapp";
var rules = domainConfiguration.MappingRules;
rules.Map("MyApp.Model.Foo").ToSchema("myapp_foo");

domainConfiguration.DefaultSchema = "mysite";
var rules = domainConfiguration.MappingRules;
blogAssembly = typeof (MySite.Model.Blog.Post).Assembly;
forumAssembly = typeof (MySite.Model.Forum.Thread).Assembly;
rules.Map(blogAssembly).ToSchema("mysite_blog");
rules.Map(forumAssembly).ToSchema("mysite_forum");

The last example uses hypothetical 'MySite.Model.Blog.Post' and 'MySite.Model.Forum.Thread' types to discover the corresponding assemblies.

To be continiued

This post covered basic ideas as well as mapping to multiple schemas. The second part will cover mapping to multiple databases and related application design techniques. Stay tuned.

DataObjects.Net Extensions, part 2. Reprocessible tasks

This is the second part of the series of posts about DataObjects.Net Extensions, created and maintained by Alexander Ovchinnikov, the author of LINQPad provider for DataObjects.Net.
The first part was about batch server-side update and delete operations and this one is dedicated to reprocessible operations support.

Reprocessible operations

In multi-threaded environment, especially under high load there are pretty good chances of deadlocks, update conflicts, unique constraint violations, etc. Obviously, such cases must be correctly handled and this is where reprocessible tasks take up the challenge.

Which tasks are reprocessable?

1. Reprocessible task should represent an autonomous block of logic, usually a single method or a delegate so it potentially could be executed as many times as needed in case of failure.
   
   
2. Reprocessible task must be transactional, so it shouldn't change the state of the system unless it is successfully completed. Moreover, it shouldn't spoil outermost transaction if any.
   
   
3. Reprocessible task should be smart enough to join active session (if present) or initialize its own one.
   

A simple reprocessible task might look like this:

Domain.Execute(session =>
  {
    // Task logic
  });

Note Action<Session> argument. This is required to have the ability to join current Session or open a new one. However, this sort of declaration doesn't provide any way to manage conditions of reproccesibility and the transaction isolation level to follow. Overloads of the Domain.Execute extension method with full list of arguments provide such options:

void Execute(IsolationLevel isolationLevel, 
             IExecuteActionStrategy strategy, 
             Action<Session> action);

T Execute<T>(IsolationLevel isolationLevel,
             IExecuteActionStrategy strategy,
             Func<Session, T> action);

An implementor of IExecuteActionStrategy is capable of how to react to exceptions thrown, how many attempts to execute the task to make and so on.

Task execution strategies

There are dozens of ways how to execute the tasks, that's why the strategy is an interface; you may want to implement your own strategy for some specific scenarios. However, DataObjects.Net Extensions contains several ready-to-use strategies for the most common cases:

1. HandleReprocessableExceptionStrategy
2. HandleUniqueConstraintViolationStrategy
3. NoReprocessStrategy

HandleReprocessableExceptionStrategy

The strategy is used to handle any ReprocessibleException, which in turn is a erroneous situation that can be recovered by rolling back active transaction and reprocessing all actions in a new one. This strategy is applicable for the majority of scenarios and is recommended as a default one.

Domain.Execute(
    ExecuteActionStrategy.Reprocessable,
    session =>
        { 
            // do some stuff
        });

The default instance of HandleReprocessableExceptionStrategy can be accessed via ExecuteActionStrategy.Reprocessable static property. Default number of attempts is 5.

HandleUniqueConstraintViolationStrategy

The strategy is used to handle either ReprocessibleException or UniqueConstraintViolationException as latter one doesn't inherit ReprocessibleException. The strategy extends HandleReprocessableExceptionStrategy and might be especially helpful in scenarios "Add or Update entity".

Domain.Execute(
    ExecuteActionStrategy.UniqueConstraintViolation,
    session =>
        { 
            var counter = session.Query.SingleOrDefault<Counter>(ID);
            if (counter == null) {
                counter = new Counter(session, ID);
            }
            counter.Value++;
        });

The default instance of HandleUniqueConstraintViolationStrategy can be accessed via ExecuteActionStrategy.UniqueConstraintViolation static property. Default number of attempts is 5.

NoReprocessStrategy

This strategy doesn't support reprocessing. It should be used in scenarios where reprocessing is not desirable or might lead to erroneous results, for example, a task operates with non-transactional objects or services:

Domain.Execute(
    ExecuteActionStrategy.NoReprocess,
    session =>
        { 
            // on transaction rollback results of these methods can't be reverted
            SendMail();
            CreateAFile();
            DeleteAFile();
            // database-related actions
        });

The default instance of NoReprocessStrategy can be accessed via ExecuteActionStrategy.NoReprocess static property.

Custom reprocessible strategies

To utilize your own reprocessible strategy, use this pattern:

Domain.Execute(
    new MyFancyReprocessibleStrategy(),
    session =>
        { 
            // some actions
        });

The implementation of custom reprocessible strategy might be based on ExecuteActionStrategy class which is the base class for the above-mentioned strategies and is included into DataObjects.Net Extensions or can be made from scratch.

Enjoy the power of DataObjects.Net Extensions, employ server-side batches and task reprocessing in your projects.


P.S.
How to install the extension is described in the first part.

Setting default values for persistent properties

While DataObjects.Net 4.4.1 & 4.3.8 Final installers are being prepared and tested, here is one more post about one not so well known feature of effective domain modelling and how that feature is extended in the upcoming release.

DataObjects.Net includes a set of rules for defining the default value for a persistent property.
The rules are the following:

Let T is the type of a persistent property, then:

1. T is a primitive type

If T is a primitive type, Guid or String then the default value for the property is default(T) and the type of the underlying column is T.

public class Animal : Entity {
...
[Field]
public int Age { get; set; }

CREATE TABLE Animal (
...
[Age] [int] NOT NULL

2. T is Nullable<>

If T is Nullable<> then the default value is NULL and the type of the underlying column is Nullable T.

public class Animal : Entity {
...
[Field]
public int? Legs { get; set; }

CREATE TABLE Animal (
...
[Legs] [int] NULL

3. T is a reference type

If T is a reference type then the default value is NULL and the type of the underlying column is the type of primary key of the referenced Entity.

public class Animal : Entity {
...
[Field]
public Person Owner { get; set; }

CREATE TABLE Animal (
...
[Owner.Id] [int] NULL

These rules are more or less obvious and straightforward but what if the overwhelming majority of animals in your application universe have 4 legs and you don't want to set this property manually again and again? Or there should not be homeless animals? Then I guess, you should have the opportunity to override these rules.

This can be done with the help of [Field] attribute.

1. Setting default value for a primitive persistent property

public class Animal : Entity {
...
[Field(DefaultValue = 4)]
public int? Legs { get; set; }

CREATE TABLE Animal (
...
[Legs] [int] NULL
...
ALTER TABLE [dbo].[Animal] ADD CONSTRAINT [DF_Animal_Legs]  DEFAULT ((4)) FOR [Legs]

2. Changing nullability of a reference field.

public class Animal : Entity {
...
[Field(Nullable = false)]
public Person Owner { get; set; }

CREATE TABLE Animal (
...
[Owner.Id] [int] NOT NULL

This setting leads to NOT NULL column which means that the value of the property must be set prior to any Session.Persist() call while constructing the Animal entity. Therefore, the right way to set such properties is inside entity's constructor, before executing any queries.

3. Setting default value for a reference persistent property.

Note, this feature is implemented in the upcoming version of DataObjects.Net (by upcoming I mean 4.3.8 or 4.4.1 branches).

public class Animal : Entity {
...
[Field(DefaultValue = 1)]  // 1 here is the key of the default animal owner
public Person Owner { get; set; }

CREATE TABLE Animal (
...
[Owner.Id] [int] NULL
...
ALTER TABLE [dbo].[Animal] ADD CONSTRAINT [DF_Animal_OwnerId]  DEFAULT ((1)) FOR [Owner.Id]

This makes sense in a scenario when the key of the default referenced entity is already known on a compilation step or it is a well-known constant identifier so there is an Entity with that key in the domain.

I hope these little tricks will help you in modelling your domains with even less efforts.

DataObjects.Net Extensions, part 1

I'm writing this post on behalf of Alexander Ovchinnikov who is the author of DataObjects.Net Extensions project. Earlier Alexander developed LINQPad provider for DataObjects.Net, which is included into standard DataObjects.Net installation package starting from version 4.5, so he is deservedly one of the most productive contributors in DataObjects.Net community.

This project is officially called DataObjects.Net Extensions and is distributed under MIT license. Source code is available on github. The latest version of DataObjects.Net Extensions contains 2 main set of features:
- Batch server-side update and delete operations
- Operation reprocessing

In this post I'll describe the first part, batch server-side update and delete operations.

How to install the extension

1. The extension is distributed in a form of NuGet package, so to use it you should install NuGet Manager if you haven't installed it already. As far as I know, Visual Studio 2010 SP 1 already includes this manager by default.

2. Open the desired solution and in the context menu select "Manage NuGet packages..."

3. Search for "DataObjectsExtensions" package

4. Click "Install", choose projects to add the reference to and click "OK".

5. Check that the package is successfully installed and start using it.

Sample domain model

This model will be used in code samples.

[HierarchyRoot]
    public class Bar : Entity {

        public Bar(Session session) : base(session){}

        [Field, Key]
        public int Id { get; set; }

        [Field]
        public string Name { get; set; }

        [Field]
        public int Count { get; set; }

        [Field]
        public string Description { get; set; }
    }

    [HierarchyRoot]
    [Index("Name", Unique = true)]
    public class Foo : Entity{

        public Foo(Session session) : base(session){}

        public Foo(Session session, int id) : base(session, id){}

        [Field, Key]
        public int Id { get; set; }

        [Field]
        public string Name { get; set; }

        [Field]
        [Association("Foo")]
        public Bar Bar { get; set; }
    }

Batch server-side operations

Why they are important?

Well, Captain Obvious to the rescue, sometimes there could be a scenario when a set of records needs to be updated without fetching them on client. There might be various reasons for that such as performance issues, limitations of business logic, etc. In such cases users have to utilize old good plain SQL to execute "UPDATE" or "DELETE" commands via underlying ADO.NET providers. But while this is not always convenient, this also could lead to potentially erroneous results because as soon as domain model changes, it becomes out of sync with these SQL commands.

DataObjects.Net Extensions sorts out the problem providing a set of IQueryable extension methods that are translated to the desired UPDATE or DELETE commands. For instance:

Query.All<Bar>()
  .Where(a => a.Id == 1)
  .Set(a => a.Count, 2)
  .Set(a => a.Name, a => a.Name + "suffix")
  .Update();

Listed below are some scenarios which you might encounter while using the package. Every case is followed by the corresponding translation to SQL.

Updating persistent property with constant value

Query.All<Bar>()
  .Where(a => a.Id == 1)
  .Set(a => a.Count, 2)
  .Update();

is translated to:

DECLARE @p0 Int SET @p0 = 2
UPDATE [dbo].[Bar]
SET [Count] = @p0
FROM [dbo].[Bar] AS j0 INNER JOIN (
SELECT
  [a].[Id]
FROM
  [dbo].[Bar] [a]
WHERE
  ([a].[Id] = 1)
) AS j1 ON (j0.[Id] = j1.[Id])

Updating persistent property with expression, computed on server

Query.All<Bar>()
  .Where(a => a.Id==1)
  .Set(a => a.Count, a => a.Description.Length + a.Count * 2)
  .Update();

is translated to:

UPDATE [dbo].[Bar]
SET [Count] = ((DATALENGTH([Description]) / 2) + ([Count] * 2))
FROM [dbo].[Bar] AS j0 INNER JOIN (
SELECT
  [a].[Id]
FROM
  [dbo].[Bar] [a]
WHERE
  ([a].[Id] = 1)
) AS j1 ON (j0.[Id] = j1.[Id])

Setting a reference to an entity that is already loaded into Session

// Emulating entity loading
var bar = Query.Single<Bar>(1);

Query.All<Foo>()
  .Where(a => a.Id == 2)
  .Set(a => a.Bar, bar)
  .Update();

is translated to:

DECLARE @p0 Int SET @p0 = 1
UPDATE [dbo].[Foo]
SET [Bar.Id] = @p0
FROM [dbo].[Foo] AS j0 INNER JOIN (
SELECT
  [a].[Id]
FROM
  [dbo].[Foo] [a]
WHERE
  ([a].[Id] = 2)
) AS j1 ON (j0.[Id] = j1.[Id])

Setting a reference to an entity that is not loaded into Session, 1st way

Query.All<Foo>()
  .Where(a => a.Id == 1)
  .Set(a => a.Bar, a => Query.Single<Bar>(1))
  .Update();

In this case DataObjectsExtensions are smart enough to extract the value of key from Query.Single method argument and use that value in command translation:

DECLARE @p0 Int SET @p0 = 1
UPDATE [dbo].[Foo]
SET [Bar.Id] = @p0
FROM [dbo].[Foo] AS j0 INNER JOIN (
SELECT
  [a].[Id]
FROM
  [dbo].[Foo] [a]
WHERE
  ([a].[Id] = 1)
) AS j1 ON (j0.[Id] = j1.[Id])

All overloads of Query.Single, Query.SingleOrDefault methods are also supported.

Setting a reference to an entity that is not loaded into Session, 2nd way

Query.All<Foo>()
  .Where(a => a.Id == 1)
  .Set(a => a.Bar, a => Query.All<Bar>().Single(b => b.Name == "test"))
  .Update();

is translated to:

UPDATE [dbo].[Foo]
SET [Bar.Id] = (SELECT TOP 1 [a].[Id] FROM [dbo].[Bar] [a] WHERE ([a].[Name] = N'test'))
FROM [dbo].[Foo] AS j0 INNER JOIN (
SELECT
  [a].[Id]
FROM
  [dbo].[Foo] [a]
WHERE
  ([a].[Id] = 1)
) AS j1 ON (j0.[Id] = j1.[Id])

All overloads of Queryable.Single, Queryable.SingleOrDefault, Queryable.First, Queryable.FirstOrDefault methods are supported.
Note, this way always leads to a subquery, so if key of referenced entity is known, it is strongly recommended to use the 1st way.

Constructing update expressions of the fly

Set extension method can be used in scenarios when the update expression is constructed in runtime, for example:

bool condition = CheckCondition();
var query = Query.All()<Bar>
  .Where(a => a.Id == 1)
  .Set(a => a.Count, 2);

if(condition)
  query = query.Set(a => a.Name, a => a.Name + "test");
query.Update();

Updating lots of properties at once

In a scenario when many properties must be updated at once, the alternative version of update extension syntax might be more convenient to use. The main method is called Update instead of Set and it takes a constructor with object initializer as argument:

Query.All<Bar>()
  .Where(a => a.Id == 1)
  .Update(a => new Bar(null) { Count = 2, Name = a.Name + "test", dozens of other properties... });

Note, while you have to populate the constructor with arguments because compiler requires that, it will never be called, only the object initializer will be processed. So it is safe you set fake arguments to the constructor. In all other ways these 2 kinds of syntax are equal, just choose the one that is convenient for you.

Deleting entities

Delete syntax is pretty straightforward, no new rocket science is here. It is almost the same as Update syntax, but use Delete instead.

Query.All<Foo>()
  .Where(a => a.Id == 1)
  .Delete();

is translated to:

DELETE [dbo].[Foo]
FROM [dbo].[Foo] AS j0 INNER JOIN (
SELECT
  [a].[Id]
FROM
  [dbo].[Foo] [a]
WHERE
  ([a].[Id] = 1)
) AS j1 ON (j0.[Id] = j1.[Id])

Known limitations

1. This version supports Microsoft SQL Server only.
2. When updating a reference field, you can't refer to the entity that is being updated, for instance:

Query.All<Foo>()
  .Where(a => a.Id == 1)
  .Set(a => a.Bar, a => Query.All<Bar>().FirstOrDefault(b => b.Name == a.Name))
  .Update();

3. Session.SaveChanges() method is executed before any batch server-side operation. This might lead to validation of entities, depending on validation configuration.
4. Every batch server-side operation cleans Session cache to provide data consistency.
5. Batch server-side operations don't support compiled LINQ queries.

Credits

All credits go to Alexander Ovchinnikov. The extension is a must-have, I'm absolutely sure that it will be very popular among DataObjects.Net community.

Thanks, Alexander!

P.S.
The second part is coming soon. Stay tuned.

Precise control over indexes

This is a detailed description of the index-related features from the upcoming release mentioned in the previous post.

Database indexes were invented to improve the speed of data retrieval operations on a database table. But as always, they have the cost: slower writes and increased storage space. So database architects have to keep the balance between faster data retrieval and slower writes. Ideally, indexes should be applied only on columns that are used in filtering, ordering, etc.

1. Control over indexes on reference fields

Versions of DataObjects.Net prior to the upcoming one were pretty straightforward and applied indexes on every reference property, for example, in this model index would be placed on Owner property automatically.

public class Penguin : Entity {

  [Field]
  public Person Owner { get; set; }
}

DataObjects.Net behaved so in assumption that such indexes would boost performance in queries like:

var PoppersPenguins = session.Query.All<Penguin>().Where(a => a.Owner == MrPopper);

But what if the application doesn't have such queries? And the index that is created and maintained by the database is absolutely useless, moreover, it makes the performance worse? The upcoming version of DataObjects.Net has the answers. Here is what you can do to prevent automatic index creation on a reference field:

public class Penguin : Entity {

  [Field(Indexed = false)]
  public Person Owner { get; set; }
}

2. Control over index clustering

There are 2 main approaches in physical organization of data tables: unordered and ordered. The first one means that the records are stored in the order they are inserted, the second implies that the records are pre-sorted before storing. Both of them have advantages and disadvantages, depending on structure of primary key, usage scenarios, etc. Some database servers supports both cases, others - only one. Microsoft SQL Server supports both: unordered approach is a "heap table (file)" and ordered one is based on "clustered index" when data rows are physically sorted according to position of primary key in internal index structures (B+ tree).

For Microsoft SQL Server DataObjects.Net always chose "clustered index" as an organization of physical storage because this is quite efficient in most cases with only few exceptions: when your primary key is not integer auto-incremented data type but uniqueidentifier or char/varchar, clustered indexes become less efficient on data inserts. Until now there was no way to say that there is no necessity to make primary index clustered, but that has changed: DataObjects.Net provides easy way to control clustering of indexes. Here is how:

// Penguin table should not be clustered
[HierarchyRoot(Clustered = false)]
public class Penguin : Entity {

  [Field]
  public Person Owner { get; set; }
}

Note, removing clustering from hierarchy root you remove it for all its descendants.

Additional feature is the ability to define an index that is not primary and then use it for clustering, like this:

// Person table should be clustered by Name column
[HierarchyRoot]
[Index("Name", Clustered = true)]
public class Person : Entity {

  [Field]
  public string Name { get; set; }
}

This can be done for each class separately except the case when SingleTable inheritance scheme is used. Another obvious restriction is that there can be only one clustered index per table.

3. Support for partial (filtered) indexes

A partial index, also known as filtered index is an index which has some condition applied to it so that it includes a subset of rows in the table. This allows the index to remain small, even though the table may be rather large, and have extreme selectivity.

Our goal was not only to add the support, but to make it easy to use and prevent users from the necessity to write database server-dependent expressions in index definitions. Therefore, we decided to implement the following technique that gives us compile-time validation, type-safety and the power of standard .NET expressions:

[HierarchyRoot
[Index("Owner", Filter = "OwnerIndex")]
public class Penguin : Entity {

    public static Expression<Func<Penguin, bool>> OwnerIndex()
    {
      return p => p.Owner != null;
    }

    [Field]
    public Person Owner { get; set; } 
}

Note that depending on inheritance scheme applied, partial indexes can or can't contain expressions with columns defined in ancestors/descendants.

In case you want to move filter expression to a separate class, you should use the following construct:

[HierarchyRoot]
[Index("Owner", Filter = "PenguinOwnerIndex", FilterType = typeof(FilterExpressions))]
public class Penguin : Entity {

    [Field]
    public Person Owner { get; set; } 
}

public static class FilterExpressions {

    public static Expression<Func<Penguin, bool>> PenguinOwnerIndex()
    {
      return p => p.Owner != null;
    }
}

Partial indexes are supported in Microsoft SQL Server and PostgreSQL providers.

In the next post I'll describe other features of the upcoming release, stay tuned. In the meantime, we are preparing the new builds, installers and stuff.

More flexibility to database schema upgrade

In this post I'm going to describe one of the most advanced and appreciated features of DataObjects.Net — automatic database schema upgrade.

You know, automatic database schema generation for Code-First & Model-First ORMs is a vital requirement, however while the task is not trivial by itself, it is not so difficult in comparison with continuous domain model & database schema synchronization. One can imagine all kind of operations on domain model: creating, renaming, moving, splitting, merging, deleting types, fields, associations, field options, index options, validation constraints, etc. All these types of changes must be propagated by ORM to database schema more or less transparently and without any possible loss of data.

In DataObjects.Net this goal is achieved with the help of mature upgrading framework that had been polished for years in version 3.x branch (starting from 2003 year) and then was migrated to version 4.x one with numerous core updates and extensions.

This could be surprising, but the core component of the upgrade framework is not translation of upgrade actions to SQL or making changes to a database schema, but the effective comparison of 2 abstract models. I'm talking about Xtensive.Modelling that was invented for describing and comparing models of any thing. In case of DataObjects.Net it is being used to compare 2 models of a storage.

Here is how the whole process is constructed. We build domain model and convert it to a unified model of storage. In parallel, we extracting metadata about database schema and converting it to the model of existing storage. After that we compare these two and additionally pass to the comparison procedure a set of upgrade hints that are provided by a user. As a result, we get a difference between 2 models and a sequence of upgrade actions that must be executed in order to convert the existing storage to the new one.

Upgrade actions are taken place only when a domain is being built in DomainUpgradeMode.Perform or PerformSafely. These two have one principal distinction: while Perform silently alters whatever is required even with data loss, PerformSafely guarantees that nothing will be removed unless is explicitly declared by a user by using special upgrade hints and attributes. The hints are nonetheless important and useful in both scenarios as they provide additional information for the comparison routine on what changes are made to domain model.

What are these hints?

• RenameTypeHint — for renaming a persistent type
• RenameFieldHint — for renaming a persisting field
• RemoveTypeHint — for removing a persistent type
• RemoveFieldHint — for removing a persistent field
• ChangeFieldTypeHint — for a situation when type of a persistent field is changed
• MoveFieldHint — for moving a persistent field from one type to another
• CopyFieldHint — for copying a persistent field from one type to another

The hints mechanism is intended to be used in custom implementation of UpgradeHandler class. This can be included into an assembly that is going to be upgraded from version "1.0.0.0" to another one and can look like this:

public class MyUpgradeHandler : UpgradeHandler
{
  public override bool CanUpgradeFrom(string oldVersion)
  {
    return oldVersion == "1.0.0.0";
  }

  protected override void AddUpgradeHints()
  {
    var hintSet = UpgradeContext.Hints;
 
    hintSet.Add(
      new RenameTypeHint("MyProduct.Model.Customer", typeof (Person)));
    hintSet.Add(
      new RenameFieldHint(typeof (Person), "Name", "FullName"));
  }
}

More about the hints and usage examples in our manual.

The upgrade hints infrastructure serves well for the overwhelming majority of scenarios but there are relatively rare situations where a change in domain model can't be described with these hints. We were gathering information and analyzing such cases and afterall, decided to extend the interface of UpgradeHandler class to provide an additional point where the upgrade routine can be corrected by user. On the picture above there is the last point, where upgrade actions are generated based on the storage models comparison result. Until now, these actions were automatically applied to a database schema and there was no way to control this process.

We are adding a method to UpgradeHandler class that exposes a sequence of upgrade actions before thay are applied to database scheme:

public class UpgradeHandler : IUpgradeHandler

    // ...

    public virtual void OnBeforeExecuteActions(UpgradeActionSequence actions)
    {
      // In overridden method actions can be added, edited, removed, etc.
    }
}

UpgradeActionSequence contains all actions that will be played against a database scheme, split in groups. An action is a regular SQL command. In this method any action can be removed, edited, moved from one group to another, new actions can be added to the sequence and so on.

The actions are executed in the following order:

01. NonTransactionalEpilogueCommands
02. Open transaction
03. CleanupDataCommands
04. PreUpgradeCommands
05. UpgradeCommands
06. CopyDataCommands
07. PostCopyDataCommands
08. CleanupCommands
09. Commit transaction
10. NonTransactionalEpilogueCommands

Note the transaction boundaries: all commands except those that don't support transactional execution are run in one transaction so on any error the scheme will stay valid and integral.

The new bits are being tested now and will be available soon as nightly builds. After the thorough testing, the updated DataObjects.Net will be available as usual on our website.

Thanks for your attention.

Making connection strings secure

Recently, Paul Sinnema from Diartis AG asked on the support website how to make connection strings, listed in application configuration files (app.config or web.config), more secure.

For that time the only thing we could suggest was to use a workaround like that:

1. Encrypt the required connection string with encryption method you prefer and put it somewhere in web.config/app.config file in encrypted form.

2. Load DomainConfiguration through standard API:

var config = DomainConfiguration.Load("mydomain");

3. Set manually config.ConnectionInfo property with decrypted connection string

config.ConnectionInfo = new ConnectionInfo("sqlserver",
  DecryptMyConnectionString(encryptedConnectionString));

4. Build Domain with the config.

Paul answered that although the workaround is useful, he'd prefer that DataObjects.Net could be integrated somehow with standard .NET configuration subsystem that provides easy encrypting/decrypting of connection strings.

Let me explain the approach, Paul was talking about. For example, if plain connection string configuration looks like that;

  <connectionStrings>
     <add name="cn1" connectionString="Data Source=localhost\SQL2008;Initial Catalog=DO40-Tests;Integrated Security=True;MultipleActiveResultSets=True" />
  </connectionStrings>

then after executing "aspnet_regiis.exe -pef "connectionStrings" %PATH_TO_PROJECT%" the encrypted one will look like the following:

  <connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">
    <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"
      xmlns="http://www.w3.org/2001/04/xmlenc#">
      <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />
      <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
        <EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">
          <EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />
          <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">
            <KeyName>Rsa Key</KeyName>
          </KeyInfo>
          <CipherData>
            <CipherValue>TJETPde0GVrSAKk0VpUs3EsV8XFRQrVU1lGlrX8aEhvfa4MaMpN3O6KLo3I9e5oujXBImKtmXHWxnF6KRcSWteuQXD4I37Dq6yE1cZ4qDHqNMkcQK+lgi6jbRLcPzYQdFuOLagsAVpif8OvY0dJkiKd+Srqjwz7ZO225VCipaQs=</CipherValue>
          </CipherData>
        </EncryptedKey>
      </KeyInfo>
      <CipherData>
        <CipherValue>6bQyCUdH9rqdNAD4IwmSXtAQIEuRfSjpQ/Py5ovz2ZpwJdEXa4W3OKWMm52g1ARXPqMYw1thfQae//+1JWTAPQwrE3JTZEdcgu47KE2x8uqdWeJfoXSecp6berSPc4qAa4F2B5x8weUGPiAsBldVfBXwUBPniKWbnnXViJWmcSn16kaAtIkq5GtNOp7YhPt9WKZG0QkFRpEaRAWqLgxK6nXUwK8BgbGEBI4B27QSFj2ME1/JI+o//RrchntEuW6VolEA7PAejMujsIn1xb0hqg==</CipherValue>
      </CipherData>
    </EncryptedData>
  </connectionStrings>

As a result, the connection strings get encrypted and therefore, much more secure. Working with them is as simple as usual: they can be accesses through standard ConfigurationManager without any special tricks:

ConfigurationManager.ConnectionStrings["cn1"].ConnectionString

And this simplicity makes the whole approach extremely useful.

So, what could be done to get DataObjects.Net compatible with all this shiny stuff, keeping in mind that connection strings section is outside DataObjects.Net configuration?

After thinking for a while on the problem, we've got the following idea: what if we put in domain configuration not a connection string itself, but a reference to the connection string, that is actually defined in connectionStrings section? For instance:

<Xtensive.Orm>
    <domains>
      <domain provider="sqlserver" connectionString="#cn1" ... />
    </domain>
  </domains>
<Xtensive.Orm>

Where:

• cn1 is a name of a connection string defined in connectionStrings section;
• # sign is used to inform that actually this is a reference to a connection string and DataObjects.Net must resolve it through ConfigurationManager.

Having this feature implemented, DataObjects.Net will provide support for encrypted connection strings out-of-the-box.

And now, we'd like to ask you some important questions:

• What would you say about the feature?
• Will it be useful for you and should it be implemented?
• Is the "#name" notation for setting a reference to a connection string is more or less clear or wouldn't it better to change it to something else?

Dear users of DataObjects.Net, we need your feedback. Please, post a comment or two.

Thanks in advance.

DataObjects.Net vs NHibernate: conceptual differences and feature-based comparison

I written the document that might help the people choosing among DataObjects.Net and NHibernate to make a decision. Currently its second part related to feature-based comparison (feature matrix) is actually a preliminary one (that's explained in the document), so later I'll rewrite it. But anyway, I hope this will be helpful.

As usual, any comments and suggestions are welcome.

Preliminary document: ORM feature matrix

I'd like to share an early link to feature-based ORM comparison we're working on: "The Most Comprehensive Feature-Based Object-Relational Mapping Tool Comparison Ever :)™"

The document is incomplete yet:

• some cells are empty - i.e. their content is currently unknown;
• there can be some mistakes (it wasn't checked by community yet);
• as you might suspect, a copy of this document is edited by ORMBattle.net participants, so its version including most of the tools tested there must also appear soon. It won't appear "as-is" at our own web site, but we'll use ~ the same columns from it (direct comparison with commercial competitors in marketing materials is normally not acceptable).

On the other hand, the feature map is already quite comprehensive: there are about 270 features organized into hierarchical structure. It is far more detailed then any other ORM comparison we were able to find (likely, this one is the most detailed, but really ancient predecessor).

Likely, the document is currently a bit biased toward DataObjects.Net from the point of selected features, but I feel this will be "automatically fixed" by the community shortly: vendors are allowed to add any non-duplicating features and sections there, as well as propose to exclude the non-important ones.

On the other hand, it's clearly much less biased document as e.g. this one (although I understand it doesn't pretend to be a real comparison). I.e. it can be hardly called as promotional material.

Our final goal is to develop a feature map including major ORM tools and features that are mutually agreed by various ORM vendors, where each vendor is responsible for contents of his own column (i.e. cheating is possible, but I suspect users & competitors won't accept this well); the table you see is our initial investment into this process.

Availability of such comparison should help developers to choose the tools they need based on their own requirements, as well as understand the relationships between features better (hierarchy seems really helpful here - I already got few quite positive comments related to the structure of the document).

An accompanying document commenting each section there and describing DataObjects.Net advantages / disadvantages in comparison to other tools should also appear soon.

Don't forget to study the comments at the bottom of the first sheet, as well as "Remarks" sheet.

DataObjects.Net-based application example: "Single Window" system for SeverRegionGaz (Gazprom subdivision)

This post actually starts a sequence of posts dedicated to DataObjects.Net design - more precisely, to its design goals. I decided to start the cycle from the practical example, since picture frequently worth more then thousands of words. Certain amount of advertisement of our outsourcing team is just side effect of this post, although if have some serious project for these guys, you're welcome.

SeverRegionGaz is regional natural gas provider. This is a big organization, that, although being a subdivision of Gazprom itself, has a set of branches as well. There is a bunch of legacy software systems there, varying from pretty old to new. Some of them have very similar functionality: earlier SeverRegionGaz branches were independent from each other, and thus they use partially equivalent software.

Most of data they maintain is related to:

• Billing - obviously.
• Equipment. E.g. they know exactly what's installed at each particular location (home, office, etc.).
• Incidents and customer interaction history.
• Bookkeeping. Unfortunately for us, they wanted to see certain information from two different instances of 1C Enterprise 7.5 there as well.

The main goal of "Single Window" system is to provide a single access point allowing to browse all this data, and, importantly, search for any piece of information there. 

Let me illustrate the importance of this goal: earlier, to find necessary piece of information (e.g. billing and interaction history for a particular person), they should identify one or few of these legacy systems first, and then request necessary information from appropriate people (nearly no one precisely knows all these systems). In short, getting the information was really complex and long process -- and we were happy to change this.

There were few other, minor goals - e.g. it was necessary to:

• Provide web interface allowing customers to report the values of natural gas consumption counters and interact with support staff.
• Integrate with external payment processing system provided by their bank and automatically process the payments made by customers.
• Implement reporting. Only a part of reports needed by SeverRegionGaz was available in legacy systems, so we should implement the missing ones.

To stress this, we should develop an application allowing to browse really huge database (I'll explain this later). Its editing capabilities should be pretty limited - mainly, because:

• Most of this information must be imported from external sources. If we'd be asked to support editing, it would bring the complexity to a completely different level. In fact, we should either be capable of syncing back all the changed (that's really hard, taking into account that none of legacy systems is ready for syncing, and almost no one knows how these systems exactly work at all), or, alternatively, replace all the legacy systems there by our own (hard as well).
• That fact that different systems are still necessary for editing (mainly, data entry) is acceptable for SeverRegionGaz. The people working with them are used to them; single person there normally deals with a single system. Having "Single Window" there, they should study just one more system to be capable of accessing all the data - that's much better then ~10.
• Full editing support (likely, a complete replacement of most of legacy systems) is primary goal for "Single Window v2" - and the idea of movement to this big goal step-by-step is really good. For now it's ok if we allow to edit only the data we fully control.

That was a story behind "Single Window" project. Now some facts about its implementation:

• Time: 7 months -- February 2010 ... July 2010. First 1.5 months were spent almost completely on specifications.
• People: initially -- 3.5 developers + 1.5 managers (Alex Ustinov was playing both roles there); closer to completion -- 6 developers.
• Database: ~ 12 GB of data, 507 tables, 440 types! (all are unique, i.e. there are no generic-based tables)
• External data sources: 8,  full data import is implemented for all of them; in additional, continuous change migration is implemented for 3 of them.
• Other elements: hundreds of lists, forms and reports. I suspect, totally - almost 1 thousand.
• Complexities: lots of, but mostly they were related to ETL processes, starting from some funny ones and ending up with real problems.
• Used technologies:
  - DataObjects.Net 4 - btw, it's our first really big application based on it. And that's why I write this post :)
  - LiveUI - it would be really hard to generate that huge UI without this framework. Btw, Alex Ilyin adopted its core part to WPF pretty fast. We also intensively used T4 to
  - WCF - we've implemented 3-tier architecture relying on WCF as communication protocol.
  - WPF - an obvious choice for UI,
  and lots of other stuff, ending up with pretty exotic COM+ (used for integration with 1C).

Screenshots

The main tab is designed in very minimalistic fashion:

That's what happens when user hits "Search" button:

As you see, we use full-text capabilities of DO4 in full power here. In fact, we index all of objects, which content is interesting from the point of search. Full-text indexing here is implemented in nearly the same way as it was in v3.9 - i.e. there is a single special type for full text document, per-type full-text content extractors running continuously in background, and so on.

Here is a typical list (take a look at grid settings and search box):

Some forms:

 

The list of actions in left panel is actually pretty long - i.e. you see may be 30% of it. There is no scrollbar, but you may find "Up" and "Down" arrows indicating the list will automatically scroll up or down when mouse pointer approaches its top and bottom edges.

Integration services control list:

Web site, customer's home page:

Pages for registration of natural gas consumption counter value and interaction with support staff:

And finally, a single screenshot exposing the complexity of domain model:

As you see, our team made a huge job, that is directly related to DataObjects.Net. 

The main point of this post is: DataObjects.Net is designed to develop really complex business applications fast. Why? Well, that's the topic for my subsequent posts, but for now I'd like to touch key points:

• Code-only approach allows developers to work fully independently without caring about schema changes at all - even in different branches.
• Integrated schema upgrade capabilities are ideal for unit testing (and testing in general). It's easy to launch unit tests for any part of your application.
• Rich event and interaction model simplifies development of shared logic, such as full-text indexing and change tracking.
• Excellent LINQ support brings significant advantages, when you start writing reports. Queries there might require really good translator.
• Can you imagine dealing with 500+ types in EF? Actually, I just tried to find some reports about this on the web, but found only "avoid this" statements, with tons of reasons. The most funny one is about performance and usability of IntelliSence when you type "dataContext.".

To be frank, of course I got lots of issue reports during these months from our "Single Window" team, and actually, still get them. Mostly they were related to LINQ and schema upgrade. So if you use DO, you can say "thanks" to Alex Ilyin - he simply tortured me and Alexis Kochetov, and still does this. E.g. mainly because of him:

• DO4 translates really complex LINQ expressions involving DTOs (custom types and anonymous typs).
• Schema upgrade works really well and fast now. Domain.Build(...) performance is 2-3 times higher there (Alex hates waiting for launch). E.g. now our 440-type Domain requires ~ 4 seconds to be built in Skip mode on my moderate office PC (Core 2 Duo). To achieve this, I should parallelize some stages of build process.

Let me finish with one more screenshot:

Download DataObjects.Net v4.3.5 build 5887 (just published, not yet announced!) and test the newest build of our framework by your own. Btw, IMO we've fixed all known bugs related to schema upgrade in this version.

P.S. I'm really interested in examples of complex applications relying on popular ORM tools. If you know some, please share the link. I got an impression that people still avoid using ORM tools in such cases (there are opinions like "ORM is not for enterprise!"). So I'd like to "measure the length" in terms of model complexity (count of tables, types, etc.) - just for fun, of course. You should know we like to measure various features of ORM tools.

A good post about TPT ("table per type") inheritance mapping and its performance-related concerns in EF

Link to the post: http://goo.gl/xQEE

The article is interesting itself, but I'd like to highlight some differences related to support of TPT ("table per type" or "table per class") inheritance mapping in DataObjects.Net:

1. EF uses "greedy" queries joining all the tables in hierarchy. DO joins minimal set of tables necessary to run the query.

EF is not initially designed for wide usage of lazy loading, so it should fetch the whole entity state, and thus - join all the tables in hierarchy.

DO behaves differently: when you query for Dog type in Animal hierarchy, it will join only Animal, Mammal and Dog tables. That's exactly what's necessary to fetch Dog type properties. If actual instances are descendants of Dog declaring their own fields, we imply you'll use .Prefetch to additionally fetch their own fields, if this is necessary; otherwise, we'll anyway load them on demand, but this will lead to SELECT N+1 issue.

.Prefetch API, in fact, fetches entity states individually - by splitting entities to process into groups of entities having the same type, and fetching their state by batched queries having Id IN (@Id1, @Id2, ...) in their WHERE clause (or its equivalent with (... OR ... OR ...) for composite keys). We've chosen this approach, because it allows us to:

• Ignore the entities with already cached state - i.e. if they were passed to .Prefetch, nothing will happen.
• Fetch the state from global cache first, when it will appear. So .Prefetch won't load the database server at all, if state of all the entities is cached in global cache.
• Issue "ideal" SQL queries with quite predictable cost: any SQL query sent by .Prefetch is, in fact, translated to a set of primary index seek operations.
• .Prefetch queries are batched. Technically we can fetch pretty large number of entities by each of such batch (up to ~ 1000K entities per batch because of SQL Server query parameter limit), but actually we fetch 256 entities in each batch. Such limit is chosen because it's optimal: that's anyway a bit more than we can materialize during execution of such batch, on the other hand, queries we send are shorter, and thus simpler for optimizer.

Why having less joins is important? JOIN order optimization is computationally hard: currently most of RDBMS use a heuristic algorithm allowing to produce nearly-optimal join sequence using n! steps instead of 4^n (where n is count of JOINs). But even n! is implies quite fast growth of complexity with n:

• 5! = 120
• 10! = 3 628 800
• 15! = 1 307 674 368 000
• 20! = 2.43290201 × 10^18

Hopefully, it's clear now that query with 15 JOINs is nearly impossible to optimize well, even with use of described heuristics: 1300 billions of join sequences must be considered to handle this. Actually, I don't know what SQL Server will do in this case, but the best it can do is to use the best plan it could produce in some limited time. And this plan won't be optimal with 99.99...% probability, since 99.99...% of other possible plans were simply rejected.

That's why I constantly write EF simply can't be used in case you have large inheritance hierarchy: 16 types = 15 JOINs in any query to this hierarchy.

2. EF uses LEFT OUTER JOINs to join the tables in hierarchy. DO always uses INNER JOIN to do the same.

LEFT OUTER JOIN is performance killer in many many cases:

• The "right part" (related to joined table) table may contain NULL values not just because they're actually are in that table, but because they appeared after join there. And if you'll use NULL such values in query somewhere (e.g. is such columns are used in ORDER BY clause, or compared with NULL in WHERE clause), SQL Server will need to execute the whole JOIN first, and only then - filter or sort the result! In fact, in this case indexes on descendants' tables won't be used at all. We implemented INNER JOIN usage optimization for reference properties just because of this, so now DO uses INNER JOIN in almost any case (exception is traversal of nullable reference property).
• Worse: I just discovered SQL Server doesn't try to reorder any OUTER joins at all (see "Limit using Outer JOINs" section)! Looking at EF's JOIN order in SQL, this implies only indexes from hierarchy root table may actually be used by SQL Server - since join comes first there, and indexes become useless after this is done (cost of hash join is ~= cost of linear scan) - at least, as far as I can judge. So SQL Server simply can't produce an optimal plan for your query in this case - it will crunch all the data in nearly straighten-forward fashion instead.

The good thing is that part 1 (above) isn't applicable now - at least, in case with SQL Server. But this doesn't mean everything is better because of this - actually, everything is even worse.

What about NHibernate?

NHibernate seems more clever here (in comparison to EF ;) ): this post by Ayende shows it uses LEFT OUTER JOIN only when you query for non-leaf type; otherwise, it uses INNER JOIN. So:

• queries for leaf types (or, more likely, JOINs related to superclasses) lead to nearly the same SQL as in DO,
• but queries for non-leaf types (or, more likely, JOINs related to subclasses, if any) lead to nearly the same SQL as in EF.

So we have an intermediate case here. If you query for leaf types, NH works nearly as well as DO from the point of generated SQL. But if you query for types having several subclasses (descendants), you're facing the same issues as in case with EF.

Alex Yakunin's blog:

• Simple audit logging with DO4: code + some analysis of DO4 brains :)
• Default logging mode in DO4 is changed
• Thoughts: what must be done ASAP to improve v.4.2?
• Link: "Volatile keyword in C# – memory model explained"
• DataObjects.Net v4.2 is released!
• Help Server v3.0.1 is released!
• News & links to new topics in furum
• Alternative DataObjects.Net download location
• Versions and locking tests (code samples for Manual): the links
• DataObjects.Net v4.2 RC2 is available
• My Google Buzz feed
• X-tensive.com is updated (warning: beta version!)
• DataObjects.Net v4.2 RC is available
• Test pass results
• Support for connection strings and new way of setting default schema
• I'm back ;)
• The purpose of ProxyKeyGenerator
• Quest of the day
• New chapter in Manual: "Hacker's guide to DataObjects.Net"
• One more Manual update
• Why there is no [DebuggerSkipThrownException(typeof(...))]?
• Preliminary version of DataObjects.Net Manual in PDF
• DisconnectedState, OperationSet, events and operation logging
• Happy New Year!
• LINQ is nice: the same problem solved by different LINQ queries
• Testing new version of Help Server

Dmitri Maximov's blog:

• Denis Krjuchkov vs. Oracle
• DataObjects.Net 4.2 RC’s health status
• Google URL shortener for everyone
• Microsoft Sql Server CE 3.5 provider for DataObjects.Net 4
• File sync problem and possible solutions
• A code quality assessment concept
• Ayende on Dependency Injection
• Updates on the development process
• A frank confession
• Query preprocessors, Inversion of control & Localization support LINQ translator extension
• Logging, part 3. Configuring logging through log4net
• Logging, part 2. Architecture & configuration
• Logging, part 1. Introduction

Alex Kofman's blog:

• Persistent Hierarchies and Key Generators
• New WPF sample
• WPF applications and DataObjects.Net

Denis Krjuchkov's blog (new!):

• LINQ
• Unified storage level exceptions
• PostSharp is going closed source
• Practice of functional programming
• Oracle challenge
• Yep, I'm here

Alex Ilyin's blog (new!):

• WPF Resources
• CSLA – First impression
• Code quality test
• Inventing Log module
• Web forms and ASP.NET MVC
• Between Tirany and Democracy

P.S. Guys, sorry, but I just decided to share your Skype pics here - people should know your real nature :)

// Actually I did this just to add some colors. And I know they don't read comments.

New posts in blogs

My blog:

• DataObjects.Net and SQL Azure
• Poll: ConnectionUrl vs connection string
• ORMBattle.NET scorecard is updated
• Our coding standards and style: links
• Pushing the changes to DataObjects.Net source code repository
• DataObjects.Net and 64-bit Windows
• ASP.NET MVC Sample (NerdDinner port) for DataObjects.Net
• Some pleasent benefits of hosting the sources at Google Code
• Building DataObjects.Net from source code
• Migration from Subversion to Mercurial: issues and workarounds
• DataObjects.Net source code is uploading to Google Code
• Weird issue with accessing Web.config section on 64-bit Windows Server 2008 (IIS 7)
• Functional programming, monads: links
• Shame to me: DO4 installer does not properly sets up everything for IIS 7 and 64-bit Windows
• Great tool: yUML
• 100% score on LINQ tests at ORMBattle.NET!
• The official launch of Visual Studio 2010 and .NET Framework 4 is March 22nd, 2010
• Recommended article: The Rx Framework (LINQ to events)
• Upcoming DisconnectedState: preliminary example code
• Issues in v4.1 RC
• DataObjects.Net v4.1 RC is updated
• DataObjects.Net v4.1: easy logging and uber-batching in action
• DataObjects.Net v4.1 RC is available
• Nightly builds are back!
• "Death by PowerPoint, and how to fight it" - the link
• DataObjects.Net v4.1: Release Candidate (RC) instead of Release on Monday
• Adding DataObjects.Net to .csproj: the new way
• New installer: Install.bat
• Should we share our own private keys for signing DataObjects.Net assemblies?
• Migrating to Mercurial
• LiveUI: screenshots of upcoming web site
• Disappointing news: I can't merge everything to a single assembly

Dmitri Maximov's blog:

• DataObjects.Net goes to Google Code
• DataObjects.Net v4 manual
• Arbitrary keys & hierarchies. Complete reference set
• Arbitrary keys & hierarchies, part 7. Custom key generators
• Arbitrary keys & hierarchies, part 6. Identity fields
• Arbitrary keys & hierarchies, part 5. Key providers
• Arbitrary keys & hierarchies, part 4. Working with keys
• Arbitrary keys & hierarchies, part 3. Evolution of Key
• How to detect whether application is being run in Debug mode?
• NET Reflector 6 EAP
• ReSharper 5.0 is coming!
• 97 Things Every Software Architect Should Know
• Arbitrary keys and hierarchies, part 2