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.