This is the fifteenth in a series of posts on how to build a LINQ IQueryable provider. If you have not read the previous posts you might try searching for the audio tapes on. That would be a lot easier than reading. You won't find any, but you'll feel better for having tried.
Complete list of posts in the Building an IQueryable Provider series
Getting this version of the toolkit together has taken a lot of sleepless nights. Thank goodness for Netflix or I'd have had to spend those sleepless nights actually working on the Toolkit.
Okay, enough with the flavor text, let's get to the crunch.
What's inside:
The full source code and redistributable DLL's can be found at:
More Providers
- MySQL -- With the stewardship of MySQL in doubt after the purchase of Sun by Oracle, I was leery of taking on the challenge of making a MySQL provider for the toolkit. Yet, the benefits of doing so turned out to be very significant and not just for the MySQL users, as it forced me to challenge some of my very assumptions about SQL which in turn made some of my meager testing better, which in turn made the toolkit better as a whole.
I was surprised when I went to download the free and open-source database product that I had to fill out a bunch of information on myself and wait for a formal approval (that took many days) before I was *allowed* to access the product. This seemed like an awful lot of hassle and odd for an open source project; way too corporate. Fortunately, I persisted and got the server up an running with no problem. Next I searched high and low for MySQL version of Northwind so I could have something to run the tests against. If only Bing were available then! I had to settle for plain ol' Live Search. Fortunately I found what I was looking for, even if it didn't offer me any cash back.
MySQL seems to me what you get if you take a bunch of C programmers and tell them to make a SQL database product and yet offer no guidance. This is not criticism, just a bit of snarky wit over many of the underscore endowed API's that make up the product. Luckily, you'll not need to worry yourself about the manual dexterity needed to type such awkward function names, as you'll be using LINQ and the MySQL provider will do the work for you. On the positive side, the function library of MySQL is quite large and feature rich. I had no problem finding appropriate translations. MySQL even had many interesting features that I'd like to go back and refactor the toolkit to take advantage of. Next release maybe.
There were a few problems I uncovered while trying to get MySQL to pass the test suites. I took the opportunity to fill out the execution suite that has languished for a few releases now, so I could actually assert what the correct output ought to be for specific queries. In almost all the cases MySQL did the right thing (as far as I was expecting.) In most of the cases that it did not appear to do the right thing, it often turned out to be bad assumptions on my part about collation ordering and so removing those assumptions from the tests fixed everything up. The remaining problem still boggles my mind.
In a test designed merely to prove that the translation of simple joins succeeded to generate the right query, the database did not return the correct number of rows.
from c in db.Customers
from o in c.Orders
from d in o.Details
select d.ProductId
which executes a MySql query that looks like this:
SELECT d.ProductId
FROM [Customers] AS c
LEFT OUTER JOIN [Orders] AS o ON o.CustomerID = c.CustomerID
LEFT OUTER JOIN [Order_Details] AS d ON d.OrderID = o.OrderID
I expected to get a number of rows corresponding to the total number of order-details in the database. But I didn't. I got a whole lot less. So I started experimenting with different forms of the query. If I selected o.OrderID instead of d.ProductID, I got a different number from either of the other two. If I selected c.CustomerID, again an entirely different number of rows. It was only when I chose to select the entire order-detail object did I get the number of rows I was expecting.
Something strange seems to be going on. On nobody's definition of SQL should I be getting a different number of rows depending on the columns in the selection (except if that SELECT has a DISTINCT operator specified.) It was as if all queries were getting a default DISTINCT operation. When I took a look a the results when I selected out only ProductID, sure enough, all I got back were distinct product-id's. MySQL has an 'ALL' keyword that is the semantic opposite of DISTINCT, so I tried adding that to the query, but to no avail.
I don't know if this is a problem in the query engine or the transport layer, or if the MySQL folks actually think it is appropriate behavior. As far as I'm concerned it is catastrophically bad; E_FAIL. For this problem alone I would recommend not using MySQL. Still, it may not be as bad as it sounds. It is likely very rare that you'd ever actually write a query that was expected to retrieve duplicate rows of data. So it may not ever impact you at all. I do recommend testing all your queries (and your application) before you put it into production.
- SQLite - This is an open-source database product that is similar in nature to MS Access and SQL Compact, since it runs in-proc and is not really a server. After wrangling with MySQL, I thought I knew all I needed to know about different forms of SQL, until Jonathan Peppers sent me his go at making a SQLite provider. ( See, I told everyone that I'd add new providers if they would send them to me.) Of course, SQLite had a few problems of its own.
While MySQL has a huge library of function, SQLite has a tiny one; many .Net API's don't have supported translations. (This is of the set I had translated for MS SQL. To be truthful, MS Access falls short on some of these too.) So queries using API's other than some simple string manipulation or equality testing probably won't work against SQLite.
In addition one of SQLite's big drawbacks is its lack of a rich type system. SQLite's developers claim this to be a feature, and it may afford great flexibility, but is often a death sentence for LINQ queries, which are all strongly typed. SQLite does have types, but only a few of them, like number, text and binary. Problems arise when you try to use DateTime's, as they are not really their own type in SQLite, but a text layout. For example, if you were trying to find all orders for a customer that happen in January, you might write this query.
from o in db.Orders
where o.CustomerID == cust && o.OrderDate.Month = 1
select o
this would produce this query.
SELECT o.OrderID, o.OrderDate
FROM Orders AS o
WHERE o.CustomerID = @cust AND STRFTIME('%m', o.OrderDate) = 1
The date function to extract the month out of the OrderDate column is really a text formatting function that extracts the month portion of the date-time text layout, which in this case is the text '01', since the date-time format is always padded to a specific width. When this is compared against the number (1), another subtle difference crops up. For all other SQL's I've come across, text is considered the weakest form of type, so when two types are compared for equality text is always converted to the other form. Yet, in SQLite, the opposite it true (which truthfully is more like C# and Java). So when the two types are compared they are found incompatible, because the number (1) is turned into the text '1' and that is not the same as the text '01'. Adding insult to injury, there appears to be no type conversion functions at all, so I can't even work around the problem by injecting a conversion. My ignorance of SQLite may just be showing here, or a lack of sufficient documentation.
So my recommendation if you are using SQLite, to stay away from most API functions in your where clauses and such. API calls in the projection are okay, because these get executed on the client.
Transactions
I'm surprised no one's called me on this before. The DbQueryProvider and its ilk have been suspiciously lacking in support for transactions. The providers work, LINQ queries are converted into ADO Commands and executed, yet those ADO Command objects are never assigned an ADO transaction, even if you started one explicitly.
Of course, the official word from Microsoft is to stop using the ADO transactions altogether and instead use the System.Transactions.TransactionScope object, that is newer, better and enables automatic use of distributed transactions, etc, etc, etc. And if you did use TransactionScope, then the problem I'm referring to would not be a problem. SqlCommand object's would implicitly enlist in the transation without me having to specify anything.
Unfortunately, TransactionScope is mired with many problems and is not supported by all ADO providers so ADO transactions are still a necessity. You can now use ADO transactions with query providers in a manner similar to LINQ to SQL.
provider.Transaction = provider.Connection.BeginTransaction();
// use the provider here to execute queries and updates, etc.
provider.Transaction.Commit();
provider.Transaction = null;
The provider will use whatever transaction object you give it when it creates new ADO command objects.
Entity Providers
I decided to formalize the pairing of query providers with a Table object that enables updates and other facilities. The definition of an entity provider is now defined by these three interfaces.
public interface IEntityProvider : IQueryProvider
{
IEntityTable<T> GetTable<T>(string tableId);
IEntityTable GetTable(Type type, string tableId);
}
public interface IEntityTable : IQueryable, IUpdatable
{
new IEntityProvider Provider { get; }
string TableId { get; }
object GetById(object id);
int Insert(object instance);
int Update(object instance);
int Delete(object instance);
int InsertOrUpdate(object instance);
}
public interface IEntityTable<T> : IQueryable<T>, IEntityTable, IUpdatable<T>
{
new T GetById(object id);
int Insert(T instance);
int Update(T instance);
int Delete(T instance);
int InsertOrUpdate(T instance);
}
You can now always get to a table directly from a provider. The two concepts are coupled together. An entity table also has explicit CRUD methods and implements IUpdatable, so no more separation between normal tables and updatable tables. In my mind this simplifies things quite a bit.
Of course, this caused me to want to rename DbQueryProvider. Ooops. This will likely cause you some grief as any of your existing code that was using DbQueryProvider directly is now not going to compile. The new name for this class is now DbEntityProvider. It might not matter so much now that there is a nifty IEntityProvider interface.
Entity Sessions
One thing missing from the Toolkit so far has been all of that context stuff that LINQ to SQL and LINQ to Entities have. When you use LINQ to SQL you have a change tracking service that detects when your objects change, and sends the updates for you all at the same time when you call SubmitChanges.
An entity session is all of this change-tracking, deferred updating stuff packaged up together. It is distinctly different from an entity provider in these ways, yet similar to one in many others.
An entity session is defined below:
public interface IEntitySession
{
IEntityProvider Provider { get; }
ISessionTable<T> GetTable<T>(string tableId);
ISessionTable GetTable(Type elementType, string tableId);
void SubmitChanges();
}
public interface ISessionTable : IQueryable
{
IEntitySession Session { get; }
IEntityTable ProviderTable { get; }
object GetById(object id);
void SetSubmitAction(object instance, SubmitAction action);
SubmitAction GetSubmitAction(object instance);
}
public interface ISessionTable<T> : IQueryable<T>, ISessionTable
{
new IEntityTable<T> ProviderTable { get; }
new T GetById(object id);
void SetSubmitAction(T instance, SubmitAction action);
SubmitAction GetSubmitAction(T instance);
}
public enum SubmitAction
{
None,
Update,
PossibleUpdate,
Insert,
InsertOrUpdate,
}
public static class SessionTableExtensions
{
public static void InsertOnSubmit<T>(this ISessionTable<T> table, T instance)
{
table.SetSubmitAction(instance, SubmitAction.Insert);
}
public static void InsertOnSubmit(this ISessionTable table, object instance)
{
table.SetSubmitAction(instance, SubmitAction.Insert);
}
public static void InsertOrUpdateOnSubmit<T>(this ISessionTable<T> table, T instance)
{
table.SetSubmitAction(instance, SubmitAction.InsertOrUpdate);
}
public static void InsertOrUpdateOnSubmit(this ISessionTable table, object instance)
{
table.SetSubmitAction(instance, SubmitAction.InsertOrUpdate);
}
public static void UpdateOnSubmit<T>(this ISessionTable<T> table, T instance)
{
table.SetSubmitAction(instance, SubmitAction.Update);
}
public static void UpdateOnSubmit(this ISessionTable table, object instance)
{
table.SetSubmitAction(instance, SubmitAction.Update);
}
public static void DeleteOnSubmit<T>(this ISessionTable<T> table, T instance)
{
table.SetSubmitAction(instance, SubmitAction.Delete);
}
public static void DeleteOnSubmit(this ISessionTable table, object instance)
{
table.SetSubmitAction(instance, SubmitAction.Delete);
}
}
As you can see, an entity session has tables, just like a provider. Yet, those tables are not directly updatable. Instead you can assign entity instances submit actions. These are the actions that take place later when you call SubmitChanges. There are a bunch of extension methods defined to add the LINQ to SQL like InsertOnSubmit() methods to the interface. These simply call the SetSubmitAction() method for you.
Also note that a session is not a provider. It is a service used in conjunction with a provider. You can use multiple different sessions with the same provider instance.
There is one current implementation of an entity session in the Toolkit called (you guessed it) DbEntitySession. You create a DbEntitySession by giving it an existing DbEntityProvider. The DbEntitySession hooks the provider in such a way that it gets first crack at all materialized objects before they are returned to you. In this way, the DbEntitySession can employ an identity cache so queries that retrieve the same entity will always return the same entity instance, and it can start automatic change tracking on all entities returned.
You are also not locked into the session's behavior. At any time you can interact with the underlying provider instead for retrieving entities without passing through the identity cache or being changed tracked. You can even get to the provider's table directly off a session table.
Provider Factory
Now with so many providers and one single way to write queries you'd think it would be easy to switch between them. In reality it is not. You have to pick the provider you want, reference its library (IQToolkit.Data.XXX), reference its corresponding ADO library (System.Data.XXX), create the ADO connection, the mapping object and construct the provider.
var connection = new SqlConnection("...");
var mapping = new AttributeMapping(typeof(Northwind));
var provider = new SqlProvider(connection, mapping, QueryPolicy.Default, null);
var db = new Northwind(provider);
You can hide this all inside your database context class (or whatever you want to call yours), so you only have to write it once, but then your context class is tied to a specific provider. Instead, you could wrap this code up into a factory method of your own devising, but then calls to the factory would be spread throughout your codebase. There no good way to defer all this work to some configuration setting. Until now.
Introducing the new factory methods built into DbEntityProvider.public static DbEntityProvider FromApplicationSettings();
public static DbEntityProvider From(string filename, string mappingId);
public static DbEntityProvider From(string provider, string connectionString, string mappingId);
These methods allow you to get up and running with only knowing a few bits of information. You don't have to hard link you application to any particular provider.
The FromApplicationSettings method creates you a new instance of a provider from information found in the config file. It looks for the "Provider", "Connection" and "Mapping" properties in the configuration and feeds them to the other factories. It is also possible to look this information up in web settings, but I have not formalized that one yet.
The provider argument is a string that refers to the name of an assembly that contains the query provider. These are generally of the form IQToolkit.Data.XXX. If that assembly is not loaded, it will be loaded dynamically. This assembly can be in the assembly cache or in the same directory as your app (or other places that the runtime might look.) From this assembly it will look for a type in the same namespace (as the name of the assembly) that derives from DbEntityProvider.
The connectionString and filename arguments are really the same thing. You can specify either the name of a database file or a full ADO connection string. If a file is specified, a correct connection string is obtained by calling the static GetConnectionString(string) method on the provider. A provider may be inferred from the file extension of a database file if none is specified.
The mappingId can either refer to the name of a context class (like Northwind) that has mapping attributes on it or the name of an xml file.
So now you can write code like this to get your provider.
var db = new Northwind(DbEntityProvider.From(somedbfile, somemapfile));
Or better yet, you can use the FromApplicationSettings() method in the constructor of your context and still be configurable at runtime.
Madness
Of course, it wouldn't be a new toolkit release without some additional crazy changes. One significant change is namespaces again. This time its not going to conflict with your code too much. Most of the classes that where in IQToolkit.Data have been demoted into the namespace IQToolkit.Data.Common. This includes most all classes that are implementation detail or base classes. Mapping attributes and the like are now in IQToolkit.Data.Mapping. This makes the namespace clean and obvious when you start looking for things via intellisense.
DbEntityProvider and DbEntitySession are the only classes sitting in IQToolkit.Data, as these are the ones you'll likely need to reference when writing code. IEntityProvider and IEntitySession are in IQToolkit namespace, because they are not specific to ADO (System.Data classes).
I hope you find this version feature rich enough to either build application directly on top of it, or model your own provider or data layer by using these techniques.
Don't forget the audio tapes.
Checkout for a widget to convert your blog posts to audio. Works fairly well in my experience.
Excellent article, this is really cool – I enjoy reading your posts, keep up the good work.
Regards
Lee
I noticed the System.Data.SQLite and MySql.Data assemblies were missing in the source. Where can these be found and are specific assemblies required?
MySQL can be downloaded from. I’ve downloaded MySQL 5.1 Community Edition for Windows. You’ll have to sign up and get approved to actually download the files. You’ll also need to download the .Net connector separately from the server. This is what the IQToolkit.Data.MySql project is trying to link to.
SQLite.Net can be downloaded at. Since this source code is public domain there appears to be a variety of implementations and ADO providers at different places on the web.
I see that I goofed by giving out source code with a solution & projects that reference DLL’s that not many people have. 🙂 You’ll have to remove the references from the Test project to build and remove the projects from the solution that don’t build.
That’s more than meets the guess..
Keep transforming!
Actually, you don’t need to register on mysql site to download it, at least for the community edition.
From the homepage, click download, and you’re lead to from which you can choose the correct version/platform.
As usual, it’s really a great series to follow. Keep going !
Sorry for double posting, just to inform that the last version of the mysql connector (v6.0) as an initial support for Entity Framework and so Linq To EF.
Hi Matt – I have two questions.
First, I’m having trouble putting an "orderby" into a compiled query. I think the orderby changes the result type to a straight IEnumerable<>, which conflicts with the IQueryable<T> return type the compiler expects to emit. Do you know how to do this?
Second, you mention transactions, but we also need lock hints (nolock and holdlock to be specific). We’ve implemented lock hints now by using extension methods and a custom provider class to stick the lock hint at the end of our queries. But as you can imagine, it’s fragile. Have you considered adding lock hints to the project?
Thanks!
– Erik
Erik, you have discovered a bug in the Aggregator.GetAggregator() method. It is supposed to figure out the extra code that is needed to make the result type of the query match the expected type. It currently does not have a case for IOrderedQueryable.
Normally you would not have hit this problem because non-compiled queries only ever return an enumerator when executed. Compiled queries on the other hand need to return the exact expected return type of the delegate.
i don’t like attribute mapping define in IQToolKit, so i change to like this:
public class Category : Entity
{
[PrimaryKey]
public Guid ID { get; set; }
public string Name { get; set; }
public Guid ParentID { get; set; }
public Category Parent { get; set; }
[Related ("ParentID")]
public List<Category> Children { get; set; }
}
public class Customer : Entity
{
[PrimaryKey]
public Guid CustomerID { get; set; }
[NotNull]
public string CustomerName { get; set; }
public string CityCode { get; set; }
[Related("CityCode")]
public City City { get; set; }
[NotNull]
[Default(false)]
public bool IsAgent { get; set; }
}
it’s easy to write and clearly, how about this?
Hi Matt,
Thanks for another great update – and the Bing product placement 😉
There’s a small bug in the IQToolkit handling of Boolean fields for the Access database that I’d like to bring to the party.
Currently "true" equates to "1" and "false" to "0". While this makes perfect sense for SQL Server (and the majority of mankind) it is not compatible with Access. In Access Boolean fields ("Yes/No" Data Types) "true" equates to "-1". The following articles confirm this:
>> "A True value is equal to -1 while a False value is equal to 0."
Testing against Northwind, the following LINQ:
var discontinued = from p in db.Products
where p.Discontinued == true
select p;
Results in the following SQL which returns no rows:
SELECT t0.[Discontinued], t0.[ProductId], t0.[ProductName]
FROM [Products] AS t0
WHERE (t0.[Discontinued] = 1)
However this query using the value -1:
SELECT t0.[Discontinued], t0.[ProductId], t0.[ProductName]
FROM [Products] AS t0
WHERE (t0.[Discontinued] = -1)
or, more clearly using the "true" keyword:
SELECT t0.[Discontinued], t0.[ProductId], t0.[ProductName]
FROM [Products] AS t0
WHERE (t0.[Discontinued] = true)
both return 8 rows as expected.
The code fix is a simple one – you need to add an override in the AccessFormatter class to deal with Access boolean constants:
protected override void WriteValue(object value)
{
if ((value != null) && (Type.GetTypeCode(value.GetType()) == TypeCode.Boolean))
{
this.Write(((bool)value) ? "true" : "false");
}
else
{
base.WriteValue(value);
}
}
While we’re in pursuit of the "true", in NorthwindTransalationTests:
public void TestWhereTrue()
{
TestQuery(db.Customers.Where(c => true));
}
the test results in the following SQL that will clearly just return all the rows from the Customers table:
SELECT t0.[City], t0.[CompanyName], t0.[ContactName], t0.[Country], t0.[Customer
ID], t0.[Phone]
FROM [Customers] AS t0
WHERE 1 <> 0
If we change the test to query a boolean (Access Yes/No) field using something like:
public void TestWhereTrue()
{
TestQuery(db.Products.Where(p => p.Discontinued == true));
}
The generated SQL makes much more sense as a test case:
SELECT t0.[Discontinued], t0.[ProductId], t0.[ProductName]
FROM [Products] AS t0
WHERE (t0.[Discontinued] = 1)
Similarly, TestWhereFalse() could become:
public void TestWhereTrue()
{
TestQuery(db.Products.Where(p => p.Discontinued == false));
}
Keep up the good stuff!
Cheers,
Dave
Hi. IQToolkit is wonderfull, but I have problem. I would like to have some readonly fields in entities, but It looks that IqTookit doesn’t like them. Any ideas?