Chain v4.3

Version 4.3

Updated dependency to Anchor 4.1.

Features

#88 Simple Aggregators

The "simple aggregators" agument the AsCount method. Each returns a single value for the desired column.

• AsAverage(columnName)
• AsMax(columnName)
• AsMin(columnName)
• AsSum(columnName, distinct)

These all return a ScalarDbCommandBuilder with which the caller can specify the return type. They are built on the AggregateColumn model, which overrides the usual column selection process.

For more complex aggregation, use the AsAggregate method. This accepts a collection of AggregateColumn objects, which can be used for both aggregegate functions and grouping.

The original AsCount methods were reworked to fit into this model.

#89 Declarative Aggregators

Attributes can now be used to declare aggregations directly in a model.

[Table("Sales.EmployeeSalesView"]
public class SalesFigures
{
	[AggregateColumn(AggregateType.Min, "TotalPrice")]
	public decimal SmallestSale { get; set; }

	[AggregateColumn(AggregateType.Max, "TotalPrice")]
	public decimal LargestSale { get; set; }

	[AggregateColumn(AggregateType.Average, "TotalPrice")]
	public decimal AverageSale { get; set; }

	[CustomAggregateColumn("Max(TotalPrice) - Min(TotalPrice)")]
	public decimal Range { get; set; }

	[GroupByColumn]
	public int EmployeeKey { get; set; }

	[GroupByColumn]
	public string EmployeeName { get; set; }
}

To use this feature, you need use either of these patterns:

datasource.FromTable(tableName, filter).ToAggregate<TObject>().ToCollection().Execute();
datasource.FromTable<TObject>(filter).ToAggregate().ToCollection().Execute();

In the second version, the table or view name is extracted from the class.

#92 ToObjectStream

Previously, Chain would fully manage database connections by default. Specifically, it would open and close connections automatically unless a transaction was involved. In that case, the developer only needed to manage the transactional data source itself.

However, there are times when a result set is too large to handle at one time. In this case the developer will want an IEnumerable or IAsyncEnumerable instead of a collection. To support this, the ToObjectStream materializer was created.

When used in place of ToCollection, the caller gets a ObjectStream object. This object implements IEnumerable<TObject>, IDisposable, IAsyncDisposable, abd IAsyncEnumerable<TObject>. (That latter two are only available in .NET 6 or later.)

This object stream may be used directly, as shown below, or attached to an RX Observable or TPL Dataflow just like any other enumerable data structure.

//sync pattern

using var objectStream = dataSource.From<Employee>(new { Title = uniqueKey }).ToObjectStream<Employee>().Execute();
foreach (var item in objectStream)
{
	Assert.AreEqual(uniqueKey, item.Title);
}

//async pattern

await using var objectStream = await dataSource.From<Employee>(new { Title = uniqueKey }).ToObjectStream<Employee>().ExecuteAsync();
await foreach (var item in objectStream)
{
	Assert.AreEqual(uniqueKey, item.Title);
}

It is vital that the object stream is disposed after use. If that doesn't occur, the database can suffer from thread exhaustion or deadlocks.

#98 Dynamic Materializers and Desired Columns

Allow the use of WithProperties or ExcludeProperties to be used with...

• .ToDynamicObject
• .ToDynamicObjectOrNull
• .ToDynamicCollection

Bugs

#490 Command Timeout is not being honored in PostgreSQL and MySQL

See the ticket for an explaination for why this was broken.

Technical Debt

#488 Add IAsyncDisposable support

Added support for IAsyncDisposable to transactional data sources.

Version 4.2

Version 4.2

Features

#463 ISupportsDeleteByKeyList should have the same overloads as ISupportsGetByKeyList

#464 ISupportsDeleteByKey should have the same overloads as ISupportsGetByKey

Allow an object to be used for determining which table to delete from instead of explicitly providing a table name.

#471 Add Scalar and List options for Char

Adds

• ToChar(...)
• ToCharOrNull(...)
• ToCharList(...)
• ToCharOrNullList(...)

#475 Add ToCharSet and ToByteSet materializers

Adds

• ToCharSet(...)
• ToByteSet(...)

#24 Improve column name support for list based materializers

When using ToXxxList/ToXxxSet, you can specify a column name. If multiple columns are returned, which can happen with a stored procedure, it will only read the named column.

Bug Fixes

#469 Tortuga.Chain.MappingException: 'Cannot map value of type System.String to property Gender of type Char.'

Adds mapping between string columns and char properties. Previously the property had to be a string.

Technical Debt

#400 Better Upsert Pattern for SQL Server

Hint UPDLOCK and SERIALIZABLE when using MERGE to perform an upsert. This reduces, though not elimintates, the need to perform an upsert in a transaction.

#474 Remove duplicate code in list/set based materialzers

Removed roughly 60 lines of code in each column based materializer.

Version 4.1

Features with Breaking Changes

#440 Default Sorting for WithLimits

If you use WithLimits without a sort order, then it will act non-deterministically. This is because the database could sort the results in any random order if not constrained.

The fix is to default to sorting with primary key when using WithLimits. If there are no primary keys and no explicit sorting, then an exception will be thrown if in strict mode.

This change also affects table-valued functions. Except these cannot infer the sort order as they do not have a primary key.

For reflection-based scenarios, the method TableOrViewMetadata<TObjectName, TDbType>.GetDefaultSortOrder(int) can be used to get a table's default sort order.

Features

#459 Add TimeSpan support for Access and OleDB

Access doesn't understand TimeSpan at all and treats it as a DateTime.

OleDB for SQL Server is worse. It returns time(7) columns as strings with the column type set to object.

#445 Add support for DateOnly and TimeOnly

On the parameter builder side, DateOnly and TimeOnly need to be converted into DateTime or TimeSpan. Which specific conversion is used depends on the database/driver combination.

On the materializer side, a new class called MaterializerTypeConverter will be used. Moving forward, this will handle type conversions from the result set to the object.

The MaterializerTypeConverter is owned by a DatabaseMetadata object, allowing additional conversions to be registered at runtime.

#451 Add support for CommitAsync, Save(savepointName), SaveAsync(savepointName), Rollback(savepointName), and RollbackAsync

We are only exposing these for .NET 6 and later.

#443 GetByKey, UpdateByKey, and DeleteByKey should support System.Int16

This was done to improve support for lookup tables with small keys.

Bug Fixes

The OleDB version of SQL Server was truncating fractional seconds when the parameter type is time(n) and n>0. To fix this, we have to force it to use DateTime/DBTimeStamp instead of TimeSpan/DBTime.

#465 OleDbSqlServerTableFunction doesn't support sorting with table limits

Using either works, but there is an error if both are used.

Performance Enhancements

#439 Use SqlCommand.EnableOptimizedParameterBinding in SQL Server MDS.

Technical Debt

Added test case for command timeout.

Created ExecutionToken.PopulateCommand method. This eliminates a lot of copy & past text from the various Execute/ExecuteAsync methods.

Chain v4.0

Version 4.0

Architecture

Rather than using #if in shared files, we are now using a trait based system that simulates multiple inheritance. Tortuga.Shipwright provides this capability using a C# Source Generator. Here is an example,

[UseTrait(typeof(SupportsDeleteAllTrait<AbstractObjectName, AbstractDbType>))]
[UseTrait(typeof(SupportsDeleteByKeyListTrait<AbstractCommand, AbstractParameter, AbstractObjectName, AbstractDbType>))]
[UseTrait(typeof(SupportsUpdateTrait<OleDbCommand, OleDbParameter, AccessObjectName, OleDbType>))]
[UseTrait(typeof(SupportsDeleteTrait<OleDbCommand, OleDbParameter, AccessObjectName, OleDbType>))]
[UseTrait(typeof(SupportsSqlQueriesTrait<OleDbCommand, OleDbParameter>))]
[UseTrait(typeof(SupportsUpdateByKeyListTrait<AbstractCommand, AbstractParameter, AbstractObjectName, AbstractDbType>))]
[UseTrait(typeof(SupportsInsertTrait<AbstractCommand, AbstractParameter, AbstractObjectName, AbstractDbType>))]
[UseTrait(typeof(SupportsUpdateSet<AbstractCommand, AbstractParameter, AbstractObjectName, AbstractDbType>))]
[UseTrait(typeof(SupportsDeleteSet<AbstractCommand, AbstractParameter, AbstractObjectName, AbstractDbType>))]
[UseTrait(typeof(SupportsFromTrait<AbstractCommand, AbstractParameter, AbstractObjectName, AbstractDbType, AbstractLimitOption>))]
[UseTrait(typeof(SupportsGetByKeyListTrait<AbstractCommand, AbstractParameter, AbstractObjectName, AbstractDbType>))]

partial class AccessDataSourceBase : ICrudDataSource

As you can see, the common functionality is added via the UseTrait attribute. In cases where additional logic is needed, the trait can make calls to partial methods in the database-specific class.

Features with Breaking Changes

#429 Realign database classes

The IClassXDataSource interfaces are gone. Now we have interfaces based on specific feature sets such as ISupportsInsert or ISupportsScalarFunction. Rollups such as ICrudDataSource are used to combine related feature interfaces.

Other Features

#327 Enabled SQL Dependency in Tortuga.Chain.SqlServer.MDS Materializers and Appenders SQL Server

Previously there was a bug preventing this feature from working with Microsoft.Data.SqlClient. Now that the bug is fixed, we can enable the feature in Chain.

#411 Improve the default behavior when only one constructor exists.

If only one constructor exists, then default to using that constructor even if InferConstructor is not set.

#408 TableAndView and GetByKey Command Builders

If you use the TableAndView attribute, then GetByKey doesn't work because it doesn't know what the primary key is.

When that happens, ask the table for its primary key.

#422 Add Truncate Command Builder Command Builders

This will only be exposed for databases that have a native truncate command. For other databases, use DeleteAll instead.

Note that in SQLite, Truncate and DeleteAll will have have the same effect. This is due to SQLite's internal optimization strategy.

#430 Add DeleteAll command

As noted above, calling DeleteAll on a SQLite database will actually perform a truncate.

Bug Fixes

#406 Delete, TableAndView, and Strict Mode Command Builders

When using the TableAndView attribute, some column will be mapped to the view but not the table.

Those unmapped columns should be ignored when performing a Delete operation. We only care about the columns that feed into the primary key.

#407 Records and Strict Mode Materializers and Appenders

The main problem was a bug in Tortuga.Anchor, which was treating protected properties as if they were public properties. Once fixed, we could check the property.CanRead field to avoid pulling in the hidden property called EqualityContract that all records expose.

#418 PostgreSQL GetTableApproximateCount returns -1 for empty tables Command Builders PostgreSQL

A weird effect in PostgreSQL is that if the table has never had any rows, GetTableApproximateCount will return -1 instead of 0.

Performance Enhancements

#402 SQL Server Parameter Lengths Performance and Optimization SQL Builder SQL Server

When sending in variable length parameters (e.g. nVarChar), make sure the parameter length is based on the column's max length. This is to avoid creating a bunch of different plans, each with slightly different parameters.

#415 Default to not using CommandBehavior.SequentialAccess Materializers and Appenders

Make using CommandBehavior.SequentialAccess an opt-in. According to Microsoft, "In most cases using the Default (non-sequential) access mode is the better choice, as [...] and you will get better performance using ReadAsync."

Technical Debt

Cleanup NuGet package code

Use the <Version> tag instead of the individual AssemblyVersion and FileVersion tags. This elimiantes the need for the ReadPackageVersionFromOutputAssembly target.

#416 Tag unmapped data types Metadata PostgreSQL

Added new items to the list of PostgreSQL data types that are used internally and not exposed via the database driver.

This is going to be an ongoing issue.

#427 Use global usings

The purpose of this change was to reduce the copious amounts of #if statements in the shared files.

Other Breaking Changes

#423 Remove String Names

Object names (e.g. tables, views, procs) will need to be referenced via:

• Using the database specific ObjectName class
• Using a class that is mapped to a table
• Via the IDataSource interfaces (which will continue to accept strings)

The reason for this change is to simplify the data source API. Currently there are too many overloads that perform the same action.

It was originally added to make working with F# easier. But with the improvements to table name inference using generics, it is no longer a pressing need.

Chain Version 3.4

#365 Rework GetByKeyList to not require a table name Command Builders
#378 SQL Server: SqlServerEffectiveSettings? m_ServerDefaultSettings should be inherited Data Source Metadata SQL Server
#382 Can't exec sql server system procedures like sp_sequence_get_range SQL Server
#366 Missing InsertBatch overload Command Builders
#376 MySQL: Approximate count for a table Command Builders MySQL
#287 Approximate count in PostgreSQL Command Builders Enhancement PostgreSQL
#246 Approximate distinct count in SQL Server Command Builders Enhancement SQL Server

Chain Version 3.3

Features

#357 Missing default for SoftDeleteRule Audit Rules
#354 CreateOpenDataSource should have a no-parameter overload Data Source
#353 ITransactionalDataSource Should Implement IOpenDataSource Data Source
#348 Support the TableAndViewAttribute Command Builders
#347 CompiledMaterializers need ToObjectOrNull Materializers and Appenders
#346 ToObjectOrNull should always use AllowEmptyResults Materializers and Appenders
#345 DeleteWithFilter needs to be reworked to not need explicit table names Command Builders
#344 DeleteByKey needs to be reworked to not need explicit table names Command Builders
#343 GetByKey should NOT call GetByKeyList Command Builders
#342 GetByKey needs to be reworked to not need explicit table names Command Builders

Chain Version 3.2.1 for PostgreSQL

Bug Fix

• #355: PostgreSQL Server_Version Fails

Chain Version 3.2

Tortuga Chain v3.2

Features

• Differentiate between .ToObject and .ToObjectOrNull #323: Better support for C# 8 and nullable reference types
• From<TObject> Improvements #330: Reduce the places where you have to specify both a table name and an object type
• DeleteOptions.CheckRowsAffected #329: Optionally throw an exception when deleting a row fails because the row doesn’t exist.
• SortExpression should be a struct, not a class #331: Removes a small memory allocation. F
• Allow sort expressions to have “DESC” suffix.
• Add WithNotifications to PostgreSQL Bulk Insert Bulk-Insert Command Builders PostgreSQL
  #324: Allows progress tracking for long bulk inserts
• Add WithNotifications to MySQL Bulk Insert Bulk-Insert Command Builders MySQL
  #325: Allows progress tracking for long bulk inserts
• Change SQL Server to use the same notification class as other bulk inserts
• Added ability to abort to PostgreSQL and MySQL bulk inserts. (This is separate from triggering a cancellation token.)
• All of the ToXxxList materializers need ToXxxOrNullList variants Materializers and Appenders
  #334: This lets you read back a List<T?> where T is a value type or string.

Bugs

• UpdateOptions.IgnoreRowsAffected Documentation is Wrong Documentation
  #328
• Fix bug in MySQL Bulk insert

Known Issues

• OleDB drivers are not fully working in .NET Core. They do work in .NET Framework.
• SQLDependency doesn’t work when using Microsoft.Data.SqlClient in .NET Framework while using MSTest. This does work fine if you use .NET Core OR run the test from the command line. Bug was filed with MS.

Packages

Chain Version 3.1

Chain 3.1 focuses on batch and bulk insert options for SQL Server, PostgreSQL, MySQL, and SQLite.
• Batch inserts use a normal INSERT statement with multiple rows being inserted atomically. The number of rows in one batch is limited by the max number of parameters supported by the database.
• Multi-batch performs a series of batch inserts. Each sub-batch is atomic, but the operation as a whole is not unless a transaction is used.
• Bulk inserts use the database-specific alternative. This usually offers the best performance, but as limitations such as in inability to return newly created keys.

Features

• Partial Object Loads #286: Only load specific properties OR exclude specific properties from being loaded
• Add Max Parameters to Metadata #297: This is used by batch inserts to ensure we don’t use too many properties in a single statement
• Add ValueNonNullLink appender
• Core functionality for batch and multi-batch inserts #303
• Batch insert for SQL Server #299: Single-statement and multi-statement batch inserts in SQL Server without the need for a table parameter.
• Batch insert for SQLite #235: Single-statement and multi-statement batch inserts for SQLite
• Batch insert for PostgreSQL #300: Single-statement and multi-statement batch inserts for PostgreSQL
• Batch Insert for MySQL #308: Single-statement and multi-statement batch inserts for MySQL
• Bulk Insert for MySQL #229
• Bulk Insert for PostgreSQL #232
• WithUser should accept nulls #317: This makes it easier for scenarios where the 'user' object is null because the user isn’t logged in.

Bugs

• Bug in NonNullLink #301: Stack overflow in the NonNullLink appender.

Packages

Tortuga.Chain.Access.3.1.7296.34685
Tortuga.Chain.CompiledMaterializers.3.1.7296.34522
Tortuga.Chain.Core.3.1.7296.34418
Tortuga.Chain.Core.Odbc.3.1.7296.34522
Tortuga.Chain.Core.OleDb.3.1.7296.34523
Tortuga.Chain.MySql.3.1.7296.34684
Tortuga.Chain.PostgreSql.3.1.7296.34685
Tortuga.Chain.SQLite.3.1.7296.34684
Tortuga.Chain.SqlServer.3.1.7296.34683
Tortuga.Chain.SqlServer.MDS.3.1.7296.34685
Tortuga.Chain.SqlServer.OleDb.3.1.7296.34684

Chain Version 3.0

• Upgrade to C# 8 and nullable reference types.
• Redesigned the project layout.
• Support for Microsoft.Data.SqlClient (MDS) as an alternative for System.Data.SqlClient
• Tortuga.Chain.SqlServer.OleDb is no longer dependent on Tortuga.Chain.SqlServer
• Moved OleDB and ODBC datasources from Tortuga.Chain.Core to their own libraries

Breaking Changes

• You can only use one of the Tortuga.Chain.SqlServer variants in a single project.
• [DB]DataSource.CreateFromConfig is no longer available, as it assumed a specific configuration manager.

Packages

• Tortuga.Chain.Core.3.0.7223.40476
• Tortuga.Chain.Core.Odbc.3.0.7223.40605
• Tortuga.Chain.Core.OleDb.3.0.7223.40607
• Tortuga.Chain.SqlServer.3.0.7223.40609
• Tortuga.Chain.SqlServer.OleDb.3.0.7223.40658
• Tortuga.Chain.SqlServer.MDS.3.0.7223.42438
• Tortuga.Chain.Access.3.0.7225.1361
• Tortuga.Chain.Core.3.0.7226.35389
• Tortuga.Chain.MySql.3.0.7226.35445
• Tortuga.Chain.Core.3.0.7226.39852
• Tortuga.Chain.PostgreSql.3.0.7227.25792
• Tortuga.Chain.SQLite.3.0.7227.38228
• Tortuga.Chain.CompiledMaterializers.3.0.7228.18237