Just confirming and announcing availability of what I proposed in this post.

So far, Simple.Data has only supported equality comparisons in queries with the FindBy* methods. Also, query operations were only possible against a single table. Obviously this is not going to get very far in real-world applications, and more complex queries across more than one table must be supported.

Complex criteria

As of the current version, the Find method on tables within a Simple.Data database will accept an expression similar to this:

db.Customers.Find(db.Customers.MoneyOwing > 0);

The (SQL Server) SQL that is generated for this looks like:

SELECT [Customers].* FROM [Customers]
WHERE [Customers].[MoneyOwing] > @p1

And the command has a parameter with the value 0. No SQL injection here!

Currently, the library supports equal, not equal, and greater/less than (or equal) operators. For strings, the LIKE operator is always used, so wildcards are supported.

Joining tables

In the SQL Server ADO adapter in version 0.1.4, joining is only possible using “natural joins” defined by referential integrity. This is a design decision, and it’s because I want the library to support NoSQL data sources where explicit joins are not generally supported. However, if you are using, or are considering using, Simple.Data and this is a blocker for you, please let me know by raising an issue at the Github project or sending me a message on Twitter.

Assuming that you have the primary and foreign keys defined in your SQL database, this Find operation:

db.Customers.Find(db.Customers.Invoices.Paid == “N”)

will produce this query:

SELECT [Customers].* FROM [Customers]
JOIN [Invoices] ON [Customers].[CustomerId] = [Invoices].[CustomerId]
WHERE [Invoices].[Paid] = @p1

Object name resolution

This release also provides better support for resolving object names. Often, database naming conventions are different from those used in application code; also, most databases support things like spaces and other special characters in object names.

Simple.Data will attempt to resolve names by removing all characters except for alpha-numerics and underscores from object names, and forcing all names to lower case. It will also try the plural or singular forms of names in its quest for a match.

So, for example, OrderItems.ItemName will match all of:

  • OrderItems.ItemName
  • OrderItem.ItemName

And other such variations. Obviously this causes a problem if the database contains names that will be ambiguous without the formatting, case and special characters, but I consider that [a] an edge case, and [b] the product of a bloody stupid database designer.

Inserted records returned from Insert

The ADO adapter for SQL Server will now return an record from the Insert operation, with any values set or modified by the database in it, as long as the table has an IDENTITY column.

Type specification in XmlMockAdapter

You can now specify the type of a value in the XML document supplied to XmlMockAdapter:

<data><Users Id=”System.Int32”><User Id=”1” …/></Users></data>

Coming in 0.1.5

The main aims for the 0.1.5 release are improved, more informative exception handling, and performance enhancements with caching. This will then, hopefully, become the stable 0.2 release which will be released via NuPack and OpenWrap.