There is a new pane in the Data Builder for VistaDB 4.1.  The LINQ query pane is a very handy way to build and test LINQ queries against a simple Entity Framework model.  First understand that this pane is a work in progress.  It was designed to be an easy way to write LINQ queries (similar to LINQPad) for your VistaDB database.  The feature is of limited usefulness since the model cannot be customized at generation, or loaded from your own assembly.  But this is where we were with the feature when we had to ship, so here it is.  We have been using it a lot to help write LINQ queries, we hope you find it useful too.

The current implementation only supports C# (sorry VB guys, it was on the list).  You can right click on the Entity Model tree and Script the main query window with all your basic CRUD operations.

By default the dialog is grayed out until you generate a model.  The code shown will still be present once the model is generated.

Generate a Model

By default the dialog is grayed out until you generate a model.  The code shown will still be present once the model is generated.

Click the Generate Model button to have Data Builder call the EDMGEN tool and build a default model for the current database.  The default is to output one type per table.  This is the same as if you add a new model to your application and accept all the defaults.  If any errors occur during the generation of the model they will be output in the Log window at the bottom of the screen.

When to generate a model

Each time Data Builder loads you will have to regenerate a new model (they are not saved across application runs).  The models are kept loaded after the initial generation.  If you make schema or foreign key changes you should regenerate the model in order for the changes to be present in the model.

How to view the current model

The current model is displayed on the second tab (after a model has been generated).

 

Intellisense

Sorry, there is no intellisense in the current version for the LINQ queries.  It REALLY does help to have it, but there just was not time to get it implemented.  Use the Entity Model tab to see what the entity names look like.

Right Click for Scripting

Right clicking on an entity (the table name) will allow you to generate some default LINQ queries for the Query pane.

 

Each of the basic CRUD operations are demonstrated for the object.  Selecting them will put the script in the query window.

Putting queries into your code

Once you have a query working the way you want, you can copy and paste it into your code.  The only requirements are that you have a model that matches the default model, and that you have a data context of your model called context (or you can rename the internal context to be what your variable is named).

A best practice for this code might look something like this:

using( RealModel.ModelEntities context = new RealModel.ModelEntities(connection))
{
   var query = from i in context.OrderDetails select new { i.OrderID, i.Orders.OrderDate };
   // Your custom code to work with the LINQ results
}

Example LINQ Queries

The example script code will not all execute by default.  They are templates for you to edit.  For example the delete template doesn’t know what your criteria is for the delete.  It leaves a string in the place for you to change to your actual criteria.

The idea was to eventually give the user an interactive wizard type of experience to build up their LINQ queries against the model.  But what is there today is still useful.

Summary

The LINQ panel in Data Builder 4.1 is a very useful way to write LINQ queries against a default EF model quickly with VistaDB.  It is not feature complete, but is still useful enough for us to release it with this version.

We hope you find it useful for writing LINQ queries, and possibly to interest you enough to learn more about Entity Framework.

For anyone coming from a background using T-Sql as their primary query language, Linq to Entities can be quite daunting. I, for one, am a huge fan of the Entity Framework.  I consider it another step towards keeping data driven application developers inside the .Net framework. And LINQ is a perfect way for programmers to think about data queries.  Sql is another language for most developers to learn, and each database has slightly different syntax for operations.  LINQ and Entity Framework make it much easier for programmers to think in their native programming language while writing queries.

In this article I am going to be showcasing a few common Sql query patterns, and explaining their Linq to Entities (EF) equivalents.  This will hopefully show some of the power of LINQ to developers who have yet to start learning the LINQ syntax.

Single Column T-Sql query

First we are going to take a look at a simple T-Sql query that selects one column from a table named Employees. This query is used in a DataAdapter to fill a DataTable then bound to a WinForms GridView.

select Age from employees

The column “Age” is of type Int, so the query will return a list of all Age’s(int’s) from the employees table. This simple query can be edited in several ways to return different results to the naming of the Age context.

select AGE from employees
select employees.Age as NewAge from employees

T-Sql of course is for the most part is case insensitive, but when it comes to LINQ column projection, case is taken into consideration. The first query (above) will return a list of Int’s in a column named “AGE”, the second will return a  column named “NewAge” that contains the same result.

This process is called projection. All of the queries return the same results but the projected schema naming has been altered dynamically.

Linq to Entities Projection

Projection in Linq to Entities (Linq with Entity Framework models) is very similar to the way it is done in T-Sql, a column or columns are queried and set to return naming different than the original column name. The great thing about Linq to Entities is the query can be debugged to see what exactly is being returned. We will start off again with a simple single column query.

var query = from emps in context.Employees select emps.Age;

By examining the query above you would think for sure its the same as our first T-Sql query. The Linq query does return the same results, a list of Int’s, but the result set does not contain any contextual information. The query above will not bind directly to a GridView because it is only a list of Int’s, not a list of type “Age” (Int’s). 

The image to the right is what the result view looks like in Visual Studio of the executed query. The result is of type System.Data.Objects.ObjectQuery<int?> which you can see has no reference to the Age column at all. There is a different approach needed to project columns into a new value.

Anonymous Type Projection

var query = from emps in context.Employees select new { emps.Age };
var query = from emps in context.Employees select new { NewAge = emps.Age };

Both queries above will perform the proper projection, similar to the T-Sql queries. The First will return a list of Int’s under the column “Age” and the second “NewAge”. The Select new syntax is how Linq to Entities handles projecting data into an anonymous type.

The image to the right is what the result view looks like for the new projected query. The results are returned not only as Int’s, but still contain their contextual information which will allow them to be bound directly to a GridView. The returned type has also changed to System.Data.Objects.ObjectQuery<<>f__AnonymousType0<int?>>. The anonymous type would be “Age” which is of type int?.

Summary

To developers that are new to LINQ and the Entity Framework, functional differences like these can be confusing. With enough research and testing almost any T-Sql query can be converted to Linq to Entities and its often very rewarding.

Entity Framework is here to stay and it is definitely something you will want to become well versed in.  Microsoft is continuing to pour huge amounts of resources into Entity Framework for the future.  Take to time to become versed in the basic concepts and terms will serve you well for the future.

Yes, VistaDB 4.1 is nearing release.  VistaDB 4.1 will be released before the end of the month.  For the most part this is a drop in replacement of 4.0 for everyone, but the License system has been totally removed. 

There are enhancements in the release though, take a look at the Dynamic Entity Framework model generation capability for Data Builder.  It is a really cool feature for writing LINQ queries within Data Builder.

Source or Binary

Yes, there will be a source release for 4.1 (free to those who have recently purchased the 4.0 Source), and a binary version as an upgrade to current users of 4.0. 

The upgrade price will be 60% off the list, as a minor upgrade fee.  Those who have 3.x can upgrade as well, but the discount is less since they are not running the most recent version.

This license is effectively a $5,000 site license for every user.  Since I seriously doubt anyone will be coming back to buy more licenses after they have an unlocked version. 

License System

The entire licenses.licx system has been removed, and the installer is being modified to not require the Activation Server in order to run.  This is an unlocked dll runtime. 

Subscribers

There are very few subscribers.  If you have a current subscription, then yes this is included for free (honestly, when have I not done that?).

Dynamic Entity Framework Model

In VistaDB 4.1 Data Builder has been changed to allow for dynamic LINQ Queries of a custom built Entity Framework (EF) model for your current database. 

It is a simple EF default generation, one type per table.  There is nothing fancy about the generation, but it gives you a real quick way to write LINQ queries in Data Builder.  This is the same thing as going into Visual Studio and generating an Entity Framework model by just clicking next, next, next.  It is a default model as defined under Visual Studio 2010.

The code generation and dynamic runtime only works with C# (sorry VB guys).  It was on our list to try to get more language support, but we have obviously run out of time.

I also really wanted to get the ability to Visualize the EF model as a part of the query pane, and intellisense.  Neither of those made it in either.  We do have an Entity Model tab next to the query that allows you to see the list of Entity Types in the model.  It makes it easier to see the column names, and their case (since case does matter for their types).

The idea for this feature was to initially allow people to play with EF, without having to go build a big model, write an application to use it, etc.  And one of the things I don’t like in LINQPad is that after I have a good working query then pasting it into my code requires a lot of changes because the default in LINQPad is a context-less model.

Bug Fixes

There are number of bug fixes and code improvements.  CLR Procs have been improved in Data Builder, and some of their capabilities improved internal to the engine.  There are also some more low level strongly typed changes in the engine. 

Dot Net 4 Version of Code (source only)

There is a 100% Dot Net 4 version of the code now as well.  It will only compile under .Net 4, but this will not be part of the binary release.  Only the source code users will get this code.  It is not packaged to handle side by side installation in the GAC, it has not been fully tested, etc, etc.  It is a work in progress to convert the code to .Net 4 native code.  This is one of the things that I was spending time on as well, and will continue in the future if able.  It will probably turn into a VistaDB 5 since a lot of interfaces are going to change in this process. 

The code is currently still 100% file level compatible with the .Net 2 version of the engine, so I decided to include it for source users who want to move to .Net 4.

What’s not included

Sync Provider – no where near production ready.  There are a ton of engine changes made to support it, and they are included.  But there is no way we could ship or release the current providers themselves.

Datatype changes – I am still testing these.  The type changes were all done a while back by another developer, but I have not reviewed his changes in the type system.  If they end up looking safe I will include them, but right now there are a couple big sticking points that would leave them out.  So this is one that is marked “if we have time”. 

There is obviously a deadline involved here that we have to get this done quickly, but I will not release untested or unsafe code.

I will keep pushing as hard as I can, and we will see what else can get done safely.

This is a summary of the email to all VistaDB 4 users.

Hard decisions have to be made

This is a very painful note for me to write. I have poured literally everything I had into VistaDB; time, energy, and money. But there are some realities that I have to face up to. If you cannot charge what it costs you to build something, then you shouldn’t be building it.

Our costs as a business have climbed quite rapidly, but income has gone down. In the past three years the following have happened: Health Insurance for employees is up 500%, corporate taxes are up 22% due to new laws in the US, unemployment insurance is 160% higher now, credit card merchant fees are double; and have much higher rates for international sales, business insurance is now totally out of our reach, server hosting is almost double, the list goes on and on. How much more are we making? Actually, I am making less today than three years ago.

Closing the Company

I cannot afford to work on VistaDB full time anymore, and I am in negotiations with a third party to acquire the product.

The office will be closing August 1, 2010.

Not the product or website yet, but read on for more details.

There are several different scenarios that may play out as a part of this, and I want to try to explain them. I had hoped some of the options listed below would have already completed their cycle by now, but they are taking much longer in negotiations than I originally expected. 

Option – VistaDB Acquired

If this third party acquires VistaDB then anything could happen. They could pull it from the market, and make it an internal tool. They could change the license to be based upon royalties. They could drastically change anything they want, it would be their product. I hope this happens, only because it would mean more resources into the product as a whole.

I am seriously considering an offer from this company to go work for them full time. While I would not be working on VistaDB full time, I may still retain some measure of influence on the product direction as an advisor.  The company seems quite sincere in their offer, but the devil is always in the details.

Option - No One Acquires VistaDB

If I can’t come to terms with anyone, then I may hold on to VistaDB, but it will be relegated to a nights and weekends type of activity. There will be no more full time work on VistaDB from me.  There will be little maintenance on the current product, as I am planning to spend my free time on a more advanced engine. Items that don’t interest me, or are too expensive to support will be dropped like a hot rock (Medium Trust for example). I still have a ton of ideas for the engine, and know I could improve performance probably 10x over what it is today, but not without massive design changes. If this is a hobby / research project then I will make those changes. I will be no longer worried about backward compatibility, or all the crazy upgrade paths.  I actually have a complete engine designed and protyped that is actually faster than SQL CE, but without a way to make money on it there is little point in developing it as a commercial product.

If I hold on to VistaDB, I would try to keep the websites and forums up as long as I can, but the server costs are not cheap. I would probably continue to sell the product, but as a company only type of sale (including source) without official support.  There would of course be community support.

Some business friends suggested I keep the product selling as is, but outsource everything and just let it sell until I recoup my costs. That is not my style. The product is complex, and I seriously doubt anyone could provide support who is not a programmer.

Option – Terminate the Product

This could happen if no one acquires the product, and I accept full time employment somewhere that forbids me from working on things in my free time. Many companies have intellectual property and non compete clauses in employment contracts these days.   This could happen, as I am actively discussing going to work for two large companies within this industry.  Neither would probably take lightly to me continuing research on a product that could eventually compete with something they sell.

Option – Open Source the Product

No, Not really an option at all. Who would work on it? Sure lots of people love to consume open source projects, but very few people contribute to them. And I have put a LOT of money into this product, I am not going to just give it away until I can at least break even. I have to put my kids through college, hopefully reclaim part of the money I have put into the company, etc.  And lets face it donation type projects never, ever make money.  Advertising on the site, etc are all pointless wastes of time.

New User Options

I am going to be changing the SKUs to a source only license in a few days. That will give everyone a chance to buy the 4.1 product and source at a reasonable price. It will ensure you can continue to run the 4.1 product as long you want / need it. No matter what happens to VistaDB.

What about activation and Visual Studio plugins?

I am going to release VistaDB 4.1 without licensing built in. This will allow all source users to continue to install and run in the event that everything is shut down.

It will not be a free upgrade unless you own the source; there will be some fee for it. User who don’t purchase the upgrade will not have access to it.

It will not have everything in the 4.1 I had hoped to release, but because there are breaking interface changes and the license system is different (gone), I have to bump the minor version number.

There will be no Sync Provider, for example. The Sync Provider has had a ton of time poured into it, but it is not production ready (not even close).   There will be no model first in Entity Framework.  Some EF extension methods will not be implemented in the current 4.x product (Skip / Take).

We do have some other new features that did get into 4.1, but that is a different post. There are also some changes in requirements, Data Builder now requires .Net 3.5 SP1 present on the developer machine.

The VistaDB 4.1 runtime is still .Net 2.0 SP1. This will be the last release for the 4.x line in all likelihood.

My recommendation

I seriously recommend that everyone purchase the source. It is a cheap insurance policy against whatever the future may hold. Existing customers will see the source in the Upgrades section of their account.  New users can purchase the 4.1 product with the source and continue using it no matter what happens.

I will probably keep the Infinite Codex site as a personal blog (again if the company I go to will let me).

It has been a fantastic ride, but one with a lot of regrets on my part.  Ah, to look back things always look so clear. 

FAQ

What about CornerstoneDB?  CornerstoneDB.com will continue as a stand alone entity.  Matthew McDonald will take that over and run it as his own.

LINQ is fantastic for the ability to write queries that express intent much more clearly than the same SQL, or structured code.  One problem that I have run into though is handling NULL database values that are part of a group by statement. 

Grouping by ProductSKU

Grouping in LINQ allows you to return sets of data from a collection for a given key value.  The group by clause is what the key ends up being in the result set.  Lets take a grouping of the Products by the SKU.

from p in Products
    group p by p.ProductSKU

Enumerable IGrouping collection

This results from the group by are enumerable groups ( IGrouping<String, Product> ) with the String being the Key for the groups (the ProductSKU field from the table).  The typical way you walk through this result is a nested for loop.

var groups = from p in Products
    group p by p.ProductSKU;
    
foreach( var groupentry in groups )
{
    Console.WriteLine( "Group: {0}", groupentry.Key );
    
    foreach( var groupitem in groupentry )
    {
        Console.WriteLine("Product: {0}", groupitem.ProductSKU);
    }
}
    

I end up with a list that looks something like this:

Group: VDB4DBA
Product: VDB4DBA
Group: VDB4DMW
Product: VDB4DMW
Group: VDB4PARTNER
Product: VDB4PARTNER

This works, but not what I really wanted.  In this case the first four characters of the SKU are the same per product family (VDB4 for all VistaDB 4 SKUs).  I would like to be able to group by only those first four characters instead of the complete ProductSKU.  You can do this with the following code.

from p in Products
    group p by p.ProductSKU.Substring(0, 4)

What if there are NULL entries?

But what happens if there is a NULL entry in the ProductSKU?  You get a ConstraintException: The property cannot be set to a null value.

Ternary and Null Coalescing Operators to the rescue

There are two operators you can use to modify the null values into something you can use.  In SQL you would use the COALESCE or ISNULL operations, these are pretty close matches.

The ternary operator is a shortcut for:

if( condition ) then (true code) : (false code)

The null coalescing operator is used to define a default value if the variable is null.

variable = ( condition ) ?? ( defaultvalue)

The code to use both of these follows.

var groups = from p in Products
    group p by p.ProductSKU == null ? "<null>" : p.ProductSKU.Substring(0, 4);

var groups2 = from p in Products
    group p by p.ProductSKU.Substring(0, 4) ?? "<null>";  // FAILS

In this case the ternary operator is the only one that will work.  This is because the test is independent of the operation.  The second example above will crash with the same constraint exception because the ProductSKU.Substring is attempted to be evaluated first, and substring on a null doesn’t work!

The null coalescing operator would work if we only wanted to test if the ProductSKU was null, but in this case the ternary is the only way to get the desired result.

Final Result

So the final result after the ternary operator looks like this:

Group: VDB3
Product: VDB3SRC
Group: VDB4
Product: VDB4DMW
Product: VDB4PROB
Product: VDB4CORE
Product: VDB4ASPPAK
Product: VDB4DBA

Now I have cleaner groups like I wanted without having to write string parsing after the query.

Summary

LINQ has a very expressive syntax that allows you to do some amazing queries without resorting to SQL. 

Group by can also be used on composite keys (more than one column) by projecting into an anonymous type.  Maybe I will leave that for another post.

ClickOnce applications have many benefits including ease of deployment, optional automatic updates and framework requirement checks.The only real complicated issue with ClickOnce is how to deploy the database. I can’t imagine trying to deploy Sql Server as a part of your application! VistaDB is a perfect fit to be embedded in a ClickOnce application due to the ease of XCopy deployment. Our 100% managed engine means you don’t need any permissions on the client side, no installs or registry permissions are required.  VistaDB can also deploy one dll to either a 32 or 64 it machine, there is no need to target a specific CPU type in advance of the deployment.

This article explains how to get a simple Windows Forms application (databound using Entity Framework) up and running with VistaDB and ClickOnce deployment.  There are a few manual steps, but almost all of these will apply to any client side xcopy deployable database. They obviously do not apply to SQL Server!

Databound WinForms Project

The Windows Forms project used in this example is very simple and consists of the following:

• A VistaDB 4 Database containing one table named Employees with the columns and data shown in the grid to the right.
• An ADO.NET Entity Model (EF model) of the VistaDB database, built using the Visual Studio wizards.
• One Form (shown on the right) that contains a grid and a button that populates the GridView with a simple linq query against the EF model.

Get Employees Code

private void button_GetEmployees_Click(object sender, EventArgs e)
{
    VistaDBEntities context = new VistaDBEntities();

    var query = from emps in context.Employees
                select emps;

    gridview_Employees.DataSource = query;
}

The code above is executed when the Get Employees button is clicked.  This is very simple, grab all the employees and put them in the grid.

Application setup for ClickOnce deployment

This simple Winform application is not intended to be a showcase product. The point of this article is to explain the process of deploying a data driven application via ClickOnce. There are a few steps needed to ensure that the VistaDB database is included in the download and it that functions properly client side after deployment.

Database Location

The database has been added to the Visual Studio solution, and set to Copy always to the output directory, and the Build Action as Content.

Copy Always options ensures that the database will always be copied to the data directory which is very important to the ClickOnce environment.  You can’t rely on a specific path being present on the end users machine, everything needs to be deployed together.

The Build Action needs to be set to Content for the database to show up in the Application Files list.  This is important as it allows you to automate the deployment of the database with the application.

Project Publish Options

Within Visual Studio 2010 (C# Winform projects) the Publish tab is a part of the project properties.  Right click the name of the project and select properties.  Most of the changes needed to make this application ClickOnce can be made in the publish section of the project properties.

 

Application File changes

The Application Files section of the Publish properties manages all of the project files that need to be included for deployment. Both the VistaDB4 database and Vistadb4 assembly should be in this list (because we added them to the project and changed the flag to be content, copy local), and need to be set to required in the download group. The application database will also need to be set to Data File under the Publish Status column.

By setting the database to the Data File, the ClickOnce system knows to ensure it is put into a folder that has read and write permissions for the user.

Application Prerequisites

The next changes need to be made to the Prerequisites section of the publish options. This section handles all requirements needed on the client machine to run the application. This portion can vary due to the requirements of your application, but in this case the .Net framework 3.5 SP1 will be needed to support Entity Framework.

 

Application Updates

The following changes are optional, but can prove to be very handy when it comes to shipping new versions of a product to users. Applications deployed by ClickOnce can be configured to automatically test for a newer version when they run.  The user will then be prompted to update automatically.

 

Publish the Application

After the above changes you can save the property window and make sure the project still builds. To publish the application, right click the project in Visual Studio and select the Publish option. Follow the steps in the Publish Wizard. After the application is published you can open it at your specified location.  This will mimic the end user experience.   You will need to move the files to a public facing server if you want end users to be able to use the application.

On the same Publish tab for the project you can specify the current Publish Version, and a very handy checkbox to automatically bump this version after each publish.  This makes it very easy to push a new version of the application, and not have to remember to rev the version in the AssemblyInfo.

View the Click Once sample now!

We have published this sample as a ClickOnce in our tutorial area of the VistaDB main site.  This is the default publish page.  You can customize this if you want, but we left it at the default so you can see what the normal experience looks like after publication of the application.

VistaDB Clickonce Sample – Go to the site now and install the sample for yourself.  A start menu shortcut will also be created.

We didn’t sign this package with our code signing certificate, so you will see a security warning dialog when you run the application.

Launch the application, and click the GetEmployees button to see the data from the database.  There will be a start menu item, and a desktop shortcut.  Each time you run the application it will go to our site and look for a newer version.  If one is present it will prompt you to download it.

Summary

ClickOnce is a new an exciting way to deploy software to end users.  It allows for an online model to the application, but with offline capabilities (the user does not have to be on the Internet to use the application).

Companies currently shipping desktop applications should to take a look at ClickOnce, it can be a great way to quickly deploy updates to applications with very little work.

I have been studying the new PLINQ and Parallel Task Library in .Net 4 looking for various ways to do things that we can’t do in .Net 2.0.  PLINQ is huge, and there are a lot of new ways to do multi threaded programming using .Net 4.  In this article I want to cover a particular problem I have had many times over the years.  How do you speed up multithreaded apps that are bound by blocking functions, or long running I/O operations?

I started looking at this method to speed up some long running file I/O routines deep in the VistaDB engine.  Most of time we are blocked in reads from disk before we can continue working, but usually we have part of the blocks we need.  So we could start working, and then continue when the rest of the blocks are loaded.  Adding that logic is complex and prone to error with traditional threading code.  Fortunately PLINQ has a way to make some of these types of operations very simple. 

Reading multiple websites

For this example I am going to read the first page of 8 websites and then act on that information afterwards.  This is the type of very simple parallel operation that splits up really well.  But these types of long running reads are very similar to what happens in many applications.

Side Note on C# 4.0 In a Nutsell Book

I actually adopted this example from one given in Joseph Albahari’s book C# 4.0 In a Nutshell (he is also the author of the excellent LinqPAD).  Weighing in a 1000 pages is not exactly a Nutshell, but it is a fantastic book for developers who already know C# and just want to go through C# and CLR 4.  The concepts in the book cover older versions of .Net as well, but the juicy parts for me were all the new changes.

LINQ Expression

Ok, this expression will go to 8 websites in this list and get the first page of each.  The content length of the page and the content type are then stored in a variable to be used outside of the parallel computation later.

        static void Main(string[] args)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();

            var results = from site in new[]
            {
                "http://infinitecodex.com",
                "http://www.vistadb.net",
                "http://stackoverflow.com",
                "http://cornerstonedb.com",
                "http://www.bing.com/",
                "http://www.linqpad.net",
                "http://www.cnn.com",
                "http://www.microsoft.com"
            }
            let p = WebRequest.Create( new Uri(site)).GetResponse()
                select new
                {
                    site,
                    Length = p.ContentLength,
                    ContentType = p.ContentType
                };

            foreach (var result in results)
            {
                Console.WriteLine("{0}:{1}:{2}", 
                    result.site, result.Length, result.ContentType);
            }

            sw.Stop();

            Console.WriteLine("Total Time: {0}ms", sw.ElapsedMilliseconds);
          
        }

The initial runs were done with no Parallel extensions being used.   Just go through each site and get the first page, storing the ContentLength and the ContentType in the temp variable p.  Afterwards I foreach over the results to output them to a command line.  If you take this step out nothing actually happens because of deferred execution in LINQ (you have to do something with the collection before it is really run).   I wrapped all of this in a Stopwatch so I would know how long it took.  The graph at the top of this article are the 3 fastest times I received after running each method 10 times.

Three fastest times normal execution (ms):  1916, 2103, 1992

Adding Parallel (PLINQ)

Now, lets make this use PLINQ and see if it runs faster.

The only change we have to make is to add a single line of code above the let statement like this.

        }
        .AsParallel()
        let p = WebRequest.Create( new Uri(site)).GetResponse()

That’s it, and the entire LINQ query will now run parallel.  It is faster, but not as fast as we can get it.

Three fastest times with AsParallel() (ms): 745, 790, 814

What PLINQ under the hood is doing is creating a thread pool and spinning up 4 threads on my 4 core machine.  But what it doesn’t know is that each of these operations are blocking waiting on I/O from the website.  PLINQ assumes that each thread will have a moderate amount of CPU work to do, so it prevents spinning up a lot of threads that would just overwhelm the CPU.

How can we tell the .Net framework that each of these parallel operations are not CPU intensive?

WithDegreeOfParallelism

From the MSDN help:  WithDegreeOfParallelism<TSource> - Degree of parallelism is the maximum number of concurrently executing tasks that will be used to process the query.

Now that doesn’t exactly explain in plain English that you can use this to tell the framework the task is not CPU intensive. Technically you are overriding the default behavior of PLINQ and telling it you know how many of these should be allowed to run concurrently. 

In this case I am going to set 8 because I know that two of these objects per CPU core is not going to tax my system at all.  The maximum you can set is 64.  Now each of these thread pools will attempt to run more than 1 thread at a time.  Why can we do this without incurring a lot of task switching overhead?  Because the objects are all blocked in I/O.  The OS will put them to sleep and release the CPU for other tasks to run anyway, we are just going to give each of those tasks more work to keep them a little busier.

Again, a single line change to the first query is all that is needed:

        }
        .AsParallel().WithDegreeOfParallelism(8) // HERE
        let p = WebRequest.Create( new Uri(site)).GetResponse()

Three fastest time with 8 Parallelism set (ms): 543, 578, 589

That is 3.5 time faster than the original query with one line code changed!

Summary

PLINQ and the .Net 4 framework give you a lot of power to speed up parallel operations very easily.  In my page manager application I was able to get a 4.5x improvement in the page cache manager through the techniques listed in this article.  And through changing my queue mechanisms over to the new Concurrent classes I was able to eliminate a lot of dead time wasted on locking and gained even more performance, but that is another blog post at some point in the future.

Plugins are used in data applications to support more than one provider from a single codebase.  The Provider Factories in ADO.Net is one such example, but you cannot work with the provider specific features when you use these factories since they only implement generic ADO.Net functions.  If you need to be able to use provider specific functions (like VistaDB’s DDA Pack routines), you have to load the provider somehow.  In most cases you put that logic and bindings into a separate assembly and load it when that ability is needed.  By taking this approach a little further and building interfaces you can abstract your logic to support more than one database provider using this model.

In this article I will explain more of how to code works to use VistaDB in a plug-in model previously explained in building a plug in model for VistaDB - Part 1. The application used to demonstrate the plug-in model is a simple windows form that allows users to deposit, withdrawal or check the balance of an account that is stored in either a VistaDB or Sql Server database.

We will implement the VistaDB custom plugins in this example.  You could implement other plugins for any other database you wish to support through the same interface.  In each case these plugins are hard bound against the database provider, but the actual application has NO binding to the database directly.  This is to allow it to run on machines where the database provider has not been installed.

Factory Classes in Main Application

The following classes are all contained within the applications main assembly.

Plug-in Factory

The plug-in factory is an abstract class that each custom plugin will need to inherit from to load from the singleton factory.

public abstract class PluginFactory
{
    #region public members
    public abstract BankModel.Provider ProviderName { get; }
    #endregion

    #region public methods
    public abstract BankModel GetModel(string connectionString);
    #endregion
}

ProviderName will return the name of BankModel provider, VistaDB or SqlServer. The GetModel method takes a database connection string and returns the abstract BankModel for that provider.

Plug-in Factories Singleton

This singleton class is used to reflection load one of the BankModel concretes when asked for by the consumer.

public static class PluginFactories
{
    static PluginFactory LoadFactory(BankModel.Provider provider)
    {
        System.Reflection.Assembly assembly;
        Type assemblyType;
        PluginFactory factory;

        switch (provider)
        {
            case BankModel.Provider.VistaDB:
                try
                {
                    assembly = Assembly.LoadFrom(string.Format(@"{0}\VistaDBBankModel.dll",
                        Directory.GetCurrentDirectory()));
                }
                catch
                {
                    throw new Exception(string.Format(@"{0}\VistaDBBankModel.dll",
                        Directory.GetCurrentDirectory()));
                }
                assemblyType = assembly.GetType("PluginSample.Plugin.BankModelPlugin");
                factory = Activator.CreateInstance(assemblyType) as PluginFactory;
                return factory;

            case BankModel.Provider.SqlServer:
                try
                {
                    assembly = Assembly.LoadFrom(string.Format(@"{0}\SqlServerDataModel.dll",
                        Directory.GetCurrentDirectory()));
                }
                catch
                {
                    throw new Exception(string.Format(@"{0}\SqlServerDataModel.dll",
                        Directory.GetCurrentDirectory()));
                }
                assemblyType = assembly.GetType("PluginSample.Plugin.BankModelPlugin");
                factory = Activator.CreateInstance(assemblyType) as PluginFactory;
                return factory;
                    
                default:
                return null;
        }
    }

    public static PluginFactory GetFactory(BankModel.Provider provider)
    {
        return LoadFactory(provider);
    }
}

Each concrete implementation must be contained within its own assembly and be located in the applications running directory. Using reflection to load the assembly insures that the hard bound reference is only used when asked for by the code.  You do not need any hard bound references to the actual provider.

This means you can have a VistaDB plugin on your system, but if the engine is not found it will fail to load – rather than your entire application failing to load.

The Abstract BankModel class

This class is implemented by every provider using that provides concrete implementation code.

public abstract class BankModel
{
    public enum Provider { VistaDB, SqlServer };
    public string Connection { get; set; }

    public BankModel(string connection)
    {
        Connection = connection;
    }

    public abstract bool Deposite(int accountNumber, int pinNumber, decimal amount);
    public abstract bool Withdrawl(int accountNumber, int pinNumber, decimal amount);
    public abstract decimal GetBalance(int accountNumber, int pinNumber);

    public abstract bool TestConnection();
}

The Plug-in Consumer

We will use a Windows Form for users to interact with the bank model by specifying which underlying provider they wish to use.

public void Deposit(int AccountNumber, int PinNumber, decimal amount)
{
    string connection = "Data Source = C:\\VistaDBBank.vdb4";

    BankModel model = null;

    try
    {
        Plugin.PluginFactory factory = Plugin.PluginFactories.GetFactory(
            BankModel.Provider.VistaDB);

        model = factory.GetModel(connection);
    }
    catch (Exception e)
    {
        MessageBox.Show(string.Format("Failed to load provider {0}, ERROR: {1}",
            "VistaDB", e.Message));
        return;
    }

    try
    {
        model.TestConnection();
    }
    catch (Exception e)
    {
        MessageBox.Show(string.Format(
            "Connection failed, check connection string and try again, ERROR {0}",
            e.Message));
        return;
    }


    bool s = model.Deposit(AccountNumber, PinNumber, amount);

    if (s)
    {
        MessageBox.Show(string.Format("Deposite made, new balance [{0}]",
            model.GetBalance(AccountNumber,
            PinNumber)));
    }
    else
    {
        MessageBox.Show("Failed to make deposite, check account information and try again.");
    }
}

In this code, I use the Factory singleton to load the VistaDB provider, then return the VistaDBBankModel from my factory by passing a VistaDB connection string. The VistaDBBankModel can now be used generically to execute the BankModel methods without knowing the underlying provider specifics.  You could implement Maintenance, and Backup functions that are called periodically without knowing how each is performed in the application itself.

Plug-in Assembly Per Provider

Each provider implementation will need to contain a concrete Factory and BankModel. Each of these assemblies can be hard bound against the provider because they will only be loaded by the consumer.  If the dependencies don’t exist, only the plug in will fail to load.

VistaDB Factory

public class BankModelPlugin : PluginFactory
{
    #region public members
    public override BankModel.Provider ProviderName
    {
        get { return BankModel.Provider.VistaDB; }
    }
    #endregion

    #region public methods
    public override BankModel GetModel(string connectionString)
    {
        return new VistaDB.BankModel.VistaDBBankModel(connectionString);
    }
    #endregion
}

This class implements the PluginFactory class for the VistaDB provider.

VistaDB Bank Model

public override bool Deposit(int accountNumber, int pinNumber, decimal amount)
{
    using (VistaDBConnection connection = new VistaDBConnection())
    {
        connection.ConnectionString = Connection;

        try
        {
            connection.Open();

            using (VistaDBCommand command = new VistaDBCommand())
            {
                command.Connection = connection;

                StringBuilder sb = new StringBuilder();
                sb.Append("UPDATE Accounts ");
                sb.Append("SET balance = balance + @balance ");
                sb.Append("SELECT balance FROM Accounts ");
                sb.Append("WHERE accountnumber = @accountNumber ");
                sb.Append("AND pinnumber = @pinNumber ");

                command.CommandText = sb.ToString();

                command.Parameters.AddWithValue("@balance", amount);
                command.Parameters.AddWithValue("@accountNumber", accountNumber);
                command.Parameters.AddWithValue("@pinNumber", pinNumber);

                decimal s = Convert.ToDecimal(command.ExecuteScalar());

                if (s == null)
                    return false;
                else
                    return true;
            }
        }
        catch (Exception e)
        {
            throw e;
        }
    }
}

This is a concrete implementation of the BankModel Deposit method that is using strongly typed VistaDB classes. At this point since I am hard bound against VistaDB, the DDA interface could also be used for many of the database operations. That is one thing ADO.NET provider factories can not do.

Summary

This has been an example of how to use VistaDB with other providers in a plug-in model where only the plug-in assemblies are hard bound to their provider. You may also want to read the first part of the plug-in database provider article.

In this article I will explain how to build an application that uses VistaDB and Microsoft’s SQL Server, without being hard bound against their providers directly.

Many companies desire to offer the choice from several database providers in a single product or API.  In many cases if you were directly bound, but the provider not installed, you would get a dll not found exception at startup. 

There are two ways to handle this scenario.  The most common method is to use ADO.Net Provider Factories and load the provider dynamically at runtime.  We have covered this in other blog posts.  This works great if you don’t need to use any DDA code, or any other provider specific functions.  But it is not really possible if you want to use provider level abilities.

The alternative is to put the provider specific code in an external dll, and load the code as a part of a plugin model.  Plugins can then be loaded individually and removed from consideration if the assembly fails to load (like when the provider is not actually present).  This allows for your code to continue to run, within needing the main executable to be bound against the provider.

Main Assembly Loads Plugins

For this example I am using a simple banking scenario application that can use either SQL Server or VistaDB as the backend database. The main executable assembly can not contain any provider level references directly. The main assembly for this example contains an abstract class called BankModel, a Windows Form  as the plugin Consumer, an abstract factory called PluginFactory, and the singleton PluginFactories.

The PluginFactories singleton class is in charge of loading and passing back provider specific implementations of the PluginFactory class. This ensures the provider is only loaded when requested by the user. At this level exceptions can be caught to inform users who request a provider that the provider could not be loaded.

Plugin Assembly per Provider

Each provider implementation must be contained within its own assembly, and these can be hard bound to their specific provider. This assembly implements the abstract bank model for VistaDB to handle bank transactions to a VistaDB database.

Using this separation allows for a consumer to request from any data provider, through a well known interface.

Summary

This is an overview on how to implement a plug-in model to encapsulate the bindings for database providers. There will be a follow up article explaining more about how the code works.

Part 2 of the article is over here

 

This topic comes up frequently when users are writing CLR Procs and Triggers.  How do I debug the code that is being hosted by the database?

There is a very easy way to do it through the System.Diagnostics.Debugger namespace to launch the just in time debugger for Visual Studio.

Prompting the user for the debugger

You can prompt the user to attach a debugger at runtime using the System.Diagnostics.Debugger. You would NEVER want to do this at runtime in a production environment! Only for use on your development machines. I usually surround the block with an #IF DEBUG to ensure it gets compiled out at release time.

Notice the screen shot above, the user is prompted for which debugger to use.  The CLRProcSample is the correct project in this case because that is what I want to debug.

Notice that it shows all the editions of Visual Studio on my machine (2005, 2008, 2010) and all open projects.  This makes it really handy for debugging CLR Procs, Triggers, just about anything that might be hosted external to a process you control.

Launch Visual Studio Just in Time Debugger

Put this code at the top of your method to ensure that a debug breakpoint is hit. I usually keep this code as a snippet and add / remove it as I need it.

 
#if DEBUG
                // If we are not debugging force the debugger to attach
                if (!System.Diagnostics.Debugger.IsAttached)
                    System.Diagnostics.Debugger.Launch();
                else
                    // Otherwise just force a break in the debugger
                    System.Diagnostics.Debugger.Break();
#endif

This code asks the debugger if it is attached, if it is not the user is prompted. You can say No when the dialog comes up and the execution will continue like normal. If you are in the debugger it will force a break on that line.

This is very handy to debug clr procs where the Data Builder, or some other process, is running the database commands and you want to be able to step into the CLR Proc and see what is happening in real time.

After reading Scott Hansleman’s article on exposing OData for Stack Overflow I thought it would be nice to update the previous post I did on Ado.net data services to include the new WCF Data Services.  WCF Data Services (formerly called Ado.net Data Services, and “Astoria”) can expose OData to callers through a very simple interface. LINQPad was not available to query the interface at the time, so I will also discuss how to use LINQPad to write queries against a Data Service.

For my example I am going to expose a VistaDB test database that shows SQL Commands, and examples of their syntax.  It is a very simple model, but provides interesting data to query against (other than Northwind!).  You can use any Entity Framework provider to perform these steps, they are not specific to VistaDB.

Being able to consume data across the web in a rest-ful manner is part of the power of OData, lots of applications that are powered by .Net are going to be able to consume OData services very easily.  But the OData protocol is not just for .Net, PHP, Java, Javascript and others also have the ability to consume the data.

What is OData?

The Open Data Protocol (OData) is an open web protocol started by Microsoft to expose data using existing web technologies.  HTTP, AtomPub (similar to RSS), and JSON are all supported.  The protocol matches very closely the way web technologies work, and the URL is the primary operator on the data query.  The HTTP verbs match very closely their CRUD operations.  The URL has a very descriptive syntax that makes it easy to build queries by hand, or with any programming language.  OData is not unique to .Net, although .Net sure makes it easy to expose and consume OData through WCF.

WCF Data Service

To expose OData we will build a WCF Data Service and expose our VistaDB EF model.  I am using Visual Studio 2010 and .Net 4 for this example.  The WCF Data Service item template in Visual Studio makes it very easy to expose an Entity Framework model over a service based interface.  You don’t have to use Entity Framework, but doing so makes it really easy to build and deploy.  I believe you could expose a custom collection through the data service as well, but I have not tried this yet.

Creating the Entity Framework Model

I first created a Visual Studio 2010 Web Application targeted to .Net 4.   Then through right clicking on the project Add - New Item and then choose the Ado.Net Entity Data Model.

This is a simple model against a VistaDB 4 database named CommandToolDB.vdb4.  We have been using this internally to build up samples of SQL code for VistaDB and SQL Server, then flagging the differences in the database.  This is not a completed project, so I am only including a sample of the dataset with this service. 

We would like to eventually have this service exposed online and queryable through Data Builder.  That would allow people to look up snippet examples of SQL Syntax and see the differences between VistaDB and SQL Server. 

Creating an OData / WCF Data Service

Right click on the project, Add – New Item – WCF Data Service.  I named the service VistaDBCommandService.svc.  To add any class to be exposed through OData all you have to do is change the class name in the DataService< ClassNameHere > definition.  The default class generated by the template includes a comment in the class definition where you put your class name.

public class VistaDBCommandService : DataService< VistaDBCommandsEntities >

Since we are exposing the EF Model, I put the name of the entities class as the type to be exposed.

public static void InitializeService(DataServiceConfiguration config)
{
    // Give readonly access to all of the entities
    config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
    
    // Pagesize will change the max number of rows returned
    config.SetEntitySetPageSize("*", 25);

    config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
}

View the Feed Output

Right click the service and choose View in Browser. You will see the following output (well, it will look like an RSS Feed if your browser knows how – so you may need to view source to see this ).   The PORT number will be different on your machine.  Visual Studio changes the localhost port, so pay attention to the URL shown when you run the service.  If you never run the service the asp.net webserver will not have started and you will get an error.

<service xml:base="http://localhost:1883/VistaDBCommandService.svc/"
    xmlns:atom="http://www.w3.org/2005/Atom" 
    xmlns:app="http://www.w3.org/2007/app" 
    xmlns="http://www.w3.org/2007/app"> 
  <workspace> 
    <atom:title>Default</atom:title> 
    <collection href="Commands"> 
      <atom:title>Commands</atom:title> 
    </collection> 
    <collection href="Databases"> 
      <atom:title>Databases</atom:title> 
    </collection> 
    <collection href="Statuses"> 
      <atom:title>Statuses</atom:title> 
    </collection> 
  </workspace> 
</service>

The links are all relative, and can be used to query the entities in the database.  Pick one of those links to see the details below.  In my model there is a Commands table, so the commands entry below will be valid.  If you are using a different model, pick on of your entity names.

Browse to the Commands Entity

Pointing by browser to the commands entity ends up looking something like this in my browser.

http://localhost:1883/VistaDBCommandService.svc/Commands

Not very pretty is it?

But now you can write some queries against the data just from the URL parameters.

http://localhost:1883/VistaDBCommandService.svc/Commands()?$top=5&$select=SQLCommand,ExampleSyntax

That will give me the First 5 commands (top=5) and only return the SQLCommand, and ExampleSyntax instead of the entire object.

Ok, but who wants to write queries in the browser?  We want LINQ!

LINQPad 4 Beta and OData

Grab the most recent LINQPad 4 beta for .Net 4 and follow along.

In LINQPad click the Add Connection at the top of the left panel.  The dialog will appear that allows you to choose what type of connection you want, and if you want an automatic data context built for you.

Choose the WCF Data Services option from that dialog and click Next.  The LINQPad connection dialog then appears.  Choose the Data Services radio button, and then enter the local service.  In my case it was the URL listed in the box.  I found this by looking the browser window launched when I clicked view in browser for the service item of the website.  Clicking test should bring up the Connection OK dialog.

Data Service in LINQPad

Now our data service is in LINQPad and looks like the image show here.  The entities are exposed, and so are their relationships.

We can now write LINQ queries against our data service and see the results in a nice graphical way inside of LINQPad.

LINQPad knows how to query the WCF Data Service and dynamically built up a local model for querying against.  The SQL output from LINQPad will now show the URL it used to query the service.

This is very similar to how Silverlight loads data exposed on remote servers without knowing anything about ADO.Net.

LINQ Query Against the Data Service

(from c in Commands
where c.Status.Description.Equals("Implemented") &&
    c.Database.Description.Equals("VistaDB")
select new { c.SQLCommand, c.ExampleSyntax }).Take( 5 )

This command queries the database to find those entries that are flagged as implemented, for the VistaDB Database, and gets the first five entries SQLCommand and ExampleSyntax columns.

Notice how I was able to drill down into the entities (c.Status.Description) and interact with the data very much like I would a local EF model.

The results look like this in the Results pane.

Viewing the SQL in LINQPad shows the following URL:

http://localhost:1883/VistaDBCommandService.svc/Commands()?$filter=(Status/Description eq 'Implemented') and (Database/Description eq 'VistaDB')&$top=5&$select=SQLCommand,ExampleSyntax

The entire LINQ statement is running on the server through that URL.

Consuming the Data through a .Net Application

Consuming the data feed through a .Net application is very easy.  In your .Net application right click and use the Add Service Reference, then point the dialog to your same service.

Adding the service reference will actually generate a client side proxy for your application to communicate with that looks like a full blown entity framework model.  You can call it using code like a normal EF entities context, but the initialization must point to your Url.  I hard coded it in the code below, but in a normal app you would put this in the app.config to allow for easier management of the service endpoint.

static void Main(string[] args)
{
    VistaDBCommandsEntities cs = new 
                       VistaDBCommandsEntities(new 
                               Uri("http://localhost:1883/VistaDBCommandService.svc"));


    var result = (from c in cs.Commands
                  where c.Status.Description.Equals("Implemented") &&
                      c.Database.Description.Equals("VistaDB")
                  select new { c.SQLCommand, c.ExampleSyntax }).Take(5);

    foreach (var r in result)
    {
        Console.WriteLine(r.SQLCommand + " : " + r.ExampleSyntax);
    }

}

Note that the entities are not IDisposable, so you cannot put them in a using statement.

Visual Studio 2010 makes OData easy

The combination of WCF Data Services and Entity Framework makes it VERY easy to expose your data in a rest-ful manner over the web.  Take a look at the DataService options and you will find a very deep system for controlling who can query data, update, how many rows they can pull at once, etc. 

In this article I will continue to explain how to build a dynamic LINQ to Entities compiler for any database provider that supports the Ado.net Entity Framework.  See part 1 of building a dynamic linq to entities compiler for background information. This part of the series will cover using the .Net CodeDom Compiler to dynamically execute LINQ queries against an EF model.

We are working on a dynamic LINQ query mechanism for the next major release of VistaDB.  Our goal is to provide a dynamic LINQ execution panel (like LinqPad does for Linq to Sql) in Data Builder.  Users will be able to write LINQ to Entities queries against the database without having to first build an EF model.  We include a default data context object that can be used to write the queries the same way they will appear in your code.

Allow users to write a LINQ query

The first step needed in the process of compiling the query is to allow the user to supply me with the database and query they wish to execute. In this example the user must return their result set to a var named query and use the name “context” for the EF model context.

Compile the LINQ query

There are many ways to perform this step but i have chosen to use the System.CodeDom.Compiler built into .Net. I will need to create a class around the users query and compile it with both classes created from the edmgen in part I. 

Create new compiler and parameters objects.

ICodeCompiler compiler = new CSharpCodeProvider().CreateCompiler();
CompilerParameters parameters = new CompilerParameters();

Add needed reference assemblies for the compile class to work. (this is only a snippet)

parameters.ReferencedAssemblies.Add("System.dll");
parameters.ReferencedAssemblies.Add("System.Windows.Forms.dll");

Design my dynamic class that contains the instantiation of the dynamic EF model and LINQ query to return a result set.

string source = @"
using System;
using System.ComponentModel;
using System.Linq;
using System.Windows.Forms;
using System.Data;
using System.Diagnostics;
using VistaDB.Provider;
using VistaDB;

[assembly: VistaDB.UseVistaDBDesignTimeLicense]

namespace VistaDBScratchPad
{
public class SourceClass
{
public object DynamicCode(string connection)
{
" + ModelInstanciation + "" + Query +
" return query as Object;}}}";

Compile the above created dynamic class along with both EF model classes and return the results.

CompilerResults results = compiler.CompileAssemblyFromFileBatch(parameters,
new string[]{string.Format("{0}\\Dynamic.cs", Directory.GetCurrentDirectory()),
string.Format("{0}\\{1}.ObjectLayer.cs", Directory.GetCurrentDirectory(), ModelName),
string.Format("{0}\\{1}.Views.cs", Directory.GetCurrentDirectory(), ModelName)});

If the compilation passes, we can create an instance of the assembly and invoke the dynamic class by passing in the EF connection string and use the returned object to bind to a WinForms grid view.

object queryResult = sourceClass.GetType().InvokeMember("DynamicCode",
BindingFlags.InvokeMethod, null, sourceClass, new object[] { EDMXConnection });
dataGridView_Results.DataSource = queryResult;

Testing with VistaDB Northwind Sample

This article series has explained how to use Microsoft’s edmgen tool to dynamically create an EDMX model which can then be compiled using CodeDom along with a new dynamic class containing a LINQ query to test LINQ syntax against a VistaDB database on the fly. These are the results returned from the VistaDB Scratchpad sample using the VistaDB Northwind sample database.

Summary

We have this working in an internal prototype to build up the EF model dynamically and allow for execution.  Of course making it work in the general purpose case is always a lot harder than in a controlled test.  But we do expect this type of ability to be in the next major release of VistaDB.

It is hard to write LINQ queries in a complex product when you have to go through a compile, load the application, step through to the correct point, get an exception, and then modify it and start all over.  This ability to write your LINQ in a dynamic execution context increases developer productivity with LINQ and Entity Framework dramatically.

 

 

In this article I will explain how to build a dynamic LINQ to Entities compiler for any database provider that supports the Ado.net Entity Framework. Due to the wide range of technologies used this article, it will be broken up into two parts as listed below.

We are working on a dynamic linq query mechanism for the next major release of VistaDB.  Our goal is to provide a LinqPad type of environment in Data Builder for users to write LINQ queries against the database without having to first build an EF model.

Blog Article Sections

• Part I. How to use edmgen command line tool to generate an EF model.
• Part II. How to use CodeDom to dynamically compile a LINQ query. See blog post

Technologies Used

• Ado.net Entity Framework (EF) – EF is an Object Relational Mapping (ORM) technology from Microsoft that is built into the .Net framework 3.5 SP1 and higher.
• VistaDB 4 – Commercial embedded SQL database that supports EF
• edmgen Tool (.Net Framework) – Included in the .Net framework, this tool is used to generate the models used by the EF runtime.
• CodeDom.Compiler (.Net Framework) – CodeDom is also built into the .Net Framework and provides the way to dynamically compile code
• LINQ to Entities (.Net Framework) – This is the query mechanism against the EF runtime, it is how you ask questions of the EF model.

Part I. How to use the edmgen command line tool

There are several steps needed in the process of dynamically testing a LINQ to entities query, first of which being the EDMX model itself. Visual Studio has a great set of wizards built in to handle generating an ADO.NET data model.  These wizards handle creating the necessary files for the EF model, and adding the connection strings to the app.config.

These wizards are not available at runtime, and the model generation becomes slightly more complex. There is no API available to generate an EDMX but Microsoft does include a command line tool called edmgen which can be used to generate an EDMX from any database provider that supports Entity Framework. You can find the edmgen tool under the 3.5 and 4.0 .net framework folders (C:\Windows\Microsoft.NET\Framework\).

Edmgen.exe Parameters

Running edmgen with /help will list the available options or you can view them on MSDN. The tool offers a lot of functionality like full or partial generation and the ability to name the three separate parts of the EF model files.

In this example I am using VistaDB, but with very slight changes this sample can work with other providers. Below is an example of how I used edmgen to dynamically generate an EDMX model using a database connection string entered from the user.

StringBuilder sb = new StringBuilder();
sb.Append(@"/mode:fullgeneration ");
sb.Append(@"/prov:System.Data.VistaDB ");
sb.Append(string.Format(@"/c:""{0}"" ", ConnectionString));
sb.Append(string.Format(@"/project:{0} ", ModelName));
sb.Append(string.Format(@"/entitycontainer:{0}Entities ", ModelName));
sb.Append(string.Format(@"/namespace:{0}Model ", ModelName));
sb.Append(@"/language:CSharp ");

Process myproc = new Process();
myproc.StartInfo.CreateNoWindow = true;
myproc.StartInfo.UseShellExecute = false;
myproc.StartInfo.FileName = "edmgen.exe";
myproc.StartInfo.Arguments = sb.ToString();
myproc.Start();

The above code simply builds up the argument string and calls a process to run the edmgen tool with those arguments, this will produce the files that make up my EDMX and put them in my running directory.

The FullGeneration mode produces all three EF meta data files: msl, csdl, ssdl, and both classes needed to query the entity objects. These three files are combined in Visual Studio’s wizards to make the EDMX file (you can open it and look, it is just an XML document).

The classes contain the public partial  methods for the entities, and the EdmRelationshipAttributes telling the EF runtime how the relationships map to the classes.

The Test.Views.cs contains all of the handling of the database views.  These are not handled as entities by default, but as methods on the entities.

The whole point of this process though is that the user will not need to look at the generated source.  So we will not cover it in this article.  The source is compiled dynamically for the user to allow writing LINQ queries against the database.

Edmgen Needs a VistaDB License

Any assembly that tries to open a VistaDB database or in this case create an EDMX model must be licensed to used VistaDB. This will cause a licensing exception when the edmgen tool attempts to talk to VistaDB, and requires an extra step be taken to insure that the edmgen tool uses the local VistaDB user license. To get edmgen to work you will need to include a new config file for edmgen in the same directory.

Below is what the contents of the new file edmgen.exe.config file needs to look like.  The file is placed in the same directory with the edmgen you want to use with VistaDB.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <add key="VistaDBUseDesignTimeLicense" value="true"/>
  </appSettings>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.VistaDB" />
      <add name="VistaDB 4" description="VistaDB 4 ADO.NET Provider for .Net 2.0-3.5"
 invariant="System.Data.VistaDB"
type="VistaDB.Provider.VistaDBProviderFactory, VistaDB.4, 
Version=4.0.0.0, Culture=neutral, PublicKeyToken=dfc935afe2125461" />
    </DbProviderFactories>
  </system.data>
</configuration>

This allows the edmgen tool to use the app.config file, and if there is a VistaDB license present on the local user account, it will use it to generate the model.

This step is only needed with VistaDB due to how the VistaDB 4.0 product licensing works.

Summary

This concludes Part I of the process on how to use Microsoft’s edmgen tool to dynamicly build an EDMX model. Part II will include information on how to use CodeDom to compile a LINQ query against an EDMX model and how it all works together.

Wouldn’t it be handy if you could use regular expressions (RegEx) in your SQL code?  You could do some really advanced queries through the ability to specify regular expressions rather than just the simple like % matching you normally use.

SQL CLR can do that!

Yes, you can use the .Net regex library using a SQL CLR method registered as a SqlFunction.  This example uses VistaDB 4, but the same code will work with SQL Server 2005 / 2008, by changing the using namespaces.  I will demonstrate the same code in SQL Server in a later blog post.

Example SQL Query

Here is what the query will look like when we are done.  This is selecting all the orders where the ShipPostalCode is exactly 5 digits only (no letters, no spaces, etc).

SELECT * FROM ORDERS WHERE LIKEREGEX
                 ( ShipPostalCode,  '^[0-9][0-9][0-9][0-9][0-9]$') = 1

CLR Method

Here is the complete C# method I will be using for the SQL REGEX. It is very simple, but that is part of the power of .Net. Imagine trying to write a regex parser using only SQL... No thanks!

[SqlFunction]
public static bool RegExMatch(string inputValue, string regexPattern )
{
    // Any nulls - we can't match, return false
    if (string.IsNullOrEmpty(inputValue) || string.IsNullOrEmpty(regexPattern))
        return false;
    
    Regex r1 = new Regex(regexPattern.TrimEnd(null));
    return r1.Match(inputValue.TrimEnd(null)).Success;
}

SQL to load the Assembly into the database

The following SQL code can be run in Data Builder and used to register the assembly and function with the name we want.  See the actual vsql4 file in the sample project for nice error handling, and how to update the assembly if it already exists in the database.

CREATE ASSEMBLY [RegExCLR] FROM 'RegExCLR.dll';

-- Add the REGEX function.  We want a friendly name 
-- LIKEREGEX rather than the full namespace name.
-- Note the way we have to specify the Assembly.Namespace.Class.Function
-- NOTE the RegExCLR.RegExCLR 
-- (one is the assembly the other is the namespace)
CREATE FUNCTION LIKEREGEX ( @inputCalue NVARCHAR(4000), 
    @regexPattern NVARCHAR(4000) ) RETURNS BIT
    AS EXTERNAL NAME RegExCLR.RegExCLR.ClrClass.RegExMatch;

Now that we have it registered, we can call it.  Take a look at the Example Calling script for examples of how to call the routine.

CLR Proc in under 45 minutes

This entire sample took less than 45 minutes to build.  I hope it inspires you to look for more ways to use the CLR within your databases.  The entire code can be found on the VistaDB Public Downloads site.

Watch the YouTube Video

I recorded a video of me walking through the steps above and demonstrating how the code works in VistaDB.

Using RegEx library from CLR Proc in VistaDB

You can also go directly to YouTube to watch the CLR Proc example using Regex in HD.

CLR Assemblies are loaded into VistaDB databases for use as CLR Procedures, CLR Triggers, and CLR Functions.  The methods are just static methods with special attributes set on them to tell the database their intended use.

SQL CLR assemblies are Class Libraries (dlls) written in either C# or VB.Net. Visual Studio 2008 or Visual Studio 2010 may be used to build the assembly.

We rebuilt the way assemblies are loaded and used in VistaDB 4 to make it more compatible with SQL Server 2005/2008.  Our attributes now make it possible to have a single codebase for building clr procs and functions for both VistaDB and SQL Server with only a recompile.

#if USEVISTADB
using VistaDB.Provider;
using VistaDB.VistaDBTypes;
using NUnit.Framework;
using VistaDB.Compatibility.SqlServer;
#else
using Microsoft.SqlServer.Server;
#endif

Since the attribute definitions are the same you can use the above IF block to include the correct namespaces in your code at compile time.  Of course VistaDB is still a subset of SQL Server, so if you are using things that VistaDB does not support they will not compile.  As always, the best strategy here is to use the VistaDB subset as your base so it will scale up to SQL Server later if needed.

Three types of attributes

[SqlFunction]
public static bool MyFunction();

[SqlProcedure]
public static void MyProcedure();

[SqlTrigger(Name = "MyTrigger", Target = "TargetTable", Event = "FOR UPDATE")]
public static void MyTrigger()

SQL Function Attribute

A SqlFunction is used when a return type is desired from the function that has meaning outside of the number of rows modified. Functions are intended to perform calculations and operations, not modify data rows.

Good uses include complex calculations, pulling data from external sources, and processing values, performing calculations on a per row basis, and using procedural logic to evaluate tabular data expressed as a part of the FROM clause of a select statement.

Think of sql functions just like a function in your normal CLR Code. A static function should not modify the global state (should be stateless) and should only do one thing.

Values from the database are usually passed in as arguments to the function.

T-SQL Functions are often a good candidate to replace with CLR Functions because the logic can be expressed more easily in CLR Code, and will usually perform better in VistaDB for complex operations.

Calling a Sql Function is done through SELECT. You cannot EXEC a function, it is meant to return a value.

SQL Procedure Attribute

SqlProcedure should not have a return type unless it is just the number of rows modified.

Procedures are meant to be used when modifing rows of data in the database. Return values from a SqlProcedure should be accomplished through the use of OUTPUT parameters. The only return value that is accepted is an integer to represent the number of rows modified (should return 0 if nothing was done). This is to enable the CLR SQL Procedure to operate the same as a subquery in an SQL statement.

Calling a SqlProcedure can be accomplished through EXEC. You are executing the procedure. SELECT on a procedure will results in only the number of rows affected being returned, this is usually not the behavior you wanted. Return values from the OUTPUT parameter can then be processed after the EXEC call.

SQL Trigger Attribute

CLR Sql Triggers are a specialized type of function that is called during an insert, update, or delete operation. These are triggered by the database internally, and the trigger function can be used to validate the changes and cancel them if the operation is not desired.

Good uses include complex ranges of information validation on specialized fields in the database, updating internal tracking or audit logs, writing out sync entries for remote systems, and much more.

VistaDB only supports DML (Data Manipulation Language) triggers on data modification events in the engine. SQL Server also supports DDL (Data Definition Language) triggers for events on create, alter and drop of database schema.

Triggers can determine the data that has been changed as a result of the operation, and reference data that was present prior to the deletion event.

There is a special context available only to triggers called the TriggerContext. This is the way to get access to the special tables INSERTED and DELETED.

Since each trigger operation is essentially a self contained transaction the trigger may cancel the event to prevent it from occurring.

More Information

We are working on new pages for the website and help covering SQL CLR Procs and Functions in VistaDB 4.  I expect this to be a multi part series that will include pages here on the blog that are mostly similar to the help and website.  But a lot more people read this than actually read the help. (sad really)

See the CLR Procs tutorials page for more information.

 

Intro to CLR Stored Procedures and CLR Functions

CLR Stored Procedures and Functions are a relatively new way to build extensions for your database.  Traditionally stored procedure logic has been written in SQL, but SQL Server 2005 introduced the ability to use CLR code for procedures.   Microsoft sometimes calls this SQL CLR as the technology used to load CLR assemblies into SQL Server.  Prior to SQL CLR you could only extend SQL Server using C++ dlls that were difficult to build and maintain.

We implemented SQL CLR assemblies very early in the VistaDB 3 development cycle to allow users to extend their databases using the same language they wrote their application (C# or VB.Net).  During the upgrade to VistaDB 4 we had identified a number of small changes we wanted to make to CLR Procs to make them more compatible with SQL Server, and to make it possible to build an assembly that would work with both VistaDB and SQL Server with only a recompile (no major code changes).  We have achieved that goal in VistaDB 4 through the addition of a new namespace: VistaDB.Compatibility.SqlServer.

High Level Overview

CLR Procedures and CLR Functions are both just public static methods in an assembly.   The entire assembly is loaded into the database and the procedure runs internal to the engine.

Note that any external dependencies to your assembly will NOT be loaded into the database.  SQL Server does this, but we do not as it leads to complex issues around the Digital Copyright act that we don't want to get involved in.

Your application can still be xcopy depolyed without needing the assembly that contains the methods.  This is very useful for deployment, but can be confusing during development.  Just because you rebuilt the dll does not mean the database has the most recent version.  After each compile you must update the assembly in the database in order to load it.

Typical Uses of CLR Procs

Need special math functions not supported by SQL?  Want to handle a storage type not handled by SQL?  Want to write a trigger to do things beyond what a traditional SQL Trigger can accomplish?  Want to lookup a domain name in DNS?

Use a CLR Proc to handle things like specialized encryption, archive, remote connections, specialized business logic, pull an RSS feed, request data from another database, authenticate a user against a domain, I think you get the idea.  Anything that you can do in managed code can be done in a CLR Proc.

CLR procs are still viewed as somewhat suspicious among a lot of DBAs.  I think this is mostly because they can't see what the function is going to do, so there is some fear of losing control.  CLR integration has to be enabled by the admin on SQL Server for it to work.  VistaDB always supports CLR Procs because we live within your application, there is no security concerns for us.

More Information

We are working on new pages for the website and help covering SQL CLR Procs and Functions in VistaDB 4.  I expect this to be a multi part series that will include pages here on the blog that are mostly similar to the help and website.  But a lot more people read this than actually read the help. (sad really)

See the CLR Procs tutorials page for more information.

Due to the recent very public problems with VistaDB 3 database applications that don’t do maintenance we have built a free command line tool that can be used to pack, repair and export to data and schema to XML using VistaDB 3.3, 3.4 or 3.5 database.

Who is this tool for?

If you have a VistaDB 3 application you can use this tool to pack the database.  It does not require VistaDB 3 to be installed on the machine.  It is a totally standalone tool.

Do you have a VistaDB 3 database that is growing out of control?   Did the developer never implement a pack or maintenance into their app?  This tool will help you.

Why use this tool?

All databases need regular maintenance.  The pack operation is the most basic of operations that should be performed periodically by the application, but many VistaDB 3 applications decided not to implement pack.  Packing ensures indexes are up to date, reclaims pages on the disk, etc. 

If your database is regularly adding data, updating, and deleting, you need to pack to ensure proper health of the data.  One Graffiti CMS user took a 4 GB database and after pack it was 171 MB.  The data had never been packed (3 years of use).  Obviously the site ran much better after the pack was performed.

Can I Export data?

Yes, the /e command will export all the data and schema to an XML file.  This should give you the most flexibility in taking the data wherever you need it to go without requiring a VistaDB 3 license.

What does repair do?

Repair will attempt to drop indexes, foreign keys, relationships in order to repair a database.  If the data pages themselves are corrupt it will not be able to fix them.  Always make regular backups of your data.

Output from the application

Running the tool from a command prompt will result in this help being output.

VistaDB Database Tool Options
*******************************************
VDB3 files for VistaDB 3.3, 3.4, 3.5 ONLY
*******************************************
VistaDBRepairTool /path:vdb3FilePath [/p] [/r] [/e] [/b]
/path:C:\myDB.vdb3 include to set database file path, this is not optional
/p include to pack the database
/r include to repair the database if pack fails
/e include to export database schema and data to local xml file (databasename.xml)
/b include to make database backup
*******************************************
EXAMPLE:
Pack and Backup database:  VistaDB3DatabaseTool.exe /path:C:\northwind.vbd3 /p /b /e
Export Database to XML:  VistaDB3DatabaseTool.exe /path:C:\northwind.vbd3 /e
*******************************************
VistaDB IS NOT REQUIRED TO USE THIS TOOL
Any VistaDB 3.3-3.5 file will be converted to the file format of 3.3.
NOTE: 3.0-3.2 will be upgraded by this tool automatically.
This tool includes an embedded VistaDB 3.3 engine that will pack any database from 3.3, 3.4 and 3.5.

Example Command Line

C:\>VistaDB3DatabaseTool.exe /path:c:\northwind.vdb3 /p /b /r /e
Starting backup of database...
Backup of database complete
Attempting to pack database
Pack database complete
Attempting to export database schema and data
Export schema and data complete
Done.  Press any key to exit.

That command line did the following commands: Backup the database (northwind_backup.vdb3), export the XML data and schema (northwind.xml), and pack the database.

Downloading the free VistaDB 3 Database Tool

The download is on our Public Downloads page. 

Terms of Use

The terms of use for this tool are pretty simple. 

You cannot redistribute it, you cannot claim it as your own, and you cannot use it to build anything else. 

This tool is for personal use only to pack and export your data from VistaDB 3.3, 3.4, and 3.5 databases.

We have updated the Account Manager website with some minor, but important, changes in the display of information.  These changes should make it a little easier to see some things in your account.

View Serial Numbers

The main page for viewing serial numbers has been simplified, and a new grid added.  Many customers were confused about inactive serial numbers in their account, so we moved them to the bottom.  After a serial number has been upgraded (4.0 Professional in the image) it was flagged as upgraded.  The new VDB4CORE license was added and is the current serial number for this account.

Existing customers upgrade support balance

If you had a 4 Pro subscription before your support has been updated to reflect the balance of any support from the previous license.  This means that as long as your support is still valid you will continue to get free updates to 4.x until the subscription ends.  After that you will be required to purchase updates.

Note that you should start using the new serial number for all future activations since the old one is now inactive.  It will not deactivate any existing installs, but it cannot be used for future activations.

New Purchase Support

All new purchases come with 30 days of installation and setup support.  The support listed in the above screen is to cover those issues, but will also cover if a minor release came out within 30 days of your purchase (you would get it for free).

All other support issues should be posted on the community forums, or through a paid support ticket.

Serial Install History

Clicking the View link from the Install History column in the above will lead you to this screen.

The history of installations against a serial number now also includes the display of reclaimed serial numbers. You can now see the currently installed machines, and older non active installs. 

Deactivated

A deactivated machine is one that you ran the revoke against to recover it’s license, or you opened a support ticket for us to reclaim it.

Reclaimed

Machines are reclaimed automatically six months after installation.  We reclaim the install so you can use it again without needed to recover or revoke the previous one.  This is to avoid the hassle when you lose a laptop, or your hard drive crashes.  We know that some people will abuse it, but the idea is to make life a little easier for the vast majority of legitimate customers.  Your machine is still valid if it is reclaimed, but it means you now have another install available for this user. 

NOTE: In the above example there are multiple user names on the serial because it was setup that way at the time of purchase as a multi developer 4.0 license.  Most users will only have one developer license per serial, and that means only one user name is valid as well.  Whatever user connects and consumes the license for the first time is the one that the license is tied to permanently.  Even if that machine is reclaimed, the user is still tied to that serial.  The only way to reset it is to contact support.

More installs per developer

We have also added more installs to each developer credential under the new VDB4CORE SKU.  It is more than two, but we are not disclosing the exact number as we want to wait and see how many people run up against the new limit. 

Between the increased installs per developer, and the auto reclaim of licenses after six months I can’t imagine anyone complaining about hardware installs.  We try really hard to be as fair as possible in our licensing, but some users seem to think that we should just give everything away.

Remember that each activation is tied to a developer, and every developer is required to have a license.  There is no license for the entire company just because the “database guy” built a DAL.  If we were to do that we would have to charge a lot more per developer because we would obviously be making a lot fewer sales.  The whole idea of the license is that the larger the project the more we should make, it allows us to keep the price as low as possible for the one man MicroISV.

Concurrent Use

Developer License installs are not valid for concurrent use.  You should not have two machines running at the same time using the same license.  The license multiple install was intended for a developer to use a desktop and a laptop.  Not five machines all in use at the same time logged in as the same developer credential.  We don’t currently block concurrent running, but we might in the future. 

Remember we charge nothing for the runtime distribution – the only way we make money is from the developer licenses.  If we relax the developer licenses we would have to charge something for the runtime.

Upgrades

The upgrades panel has been updated to show any available upgrades for the users account.

Pricelist / Shopping

Clicking on the pricelist will take you to the online store, already logged in and ready to purchase.  No need to login again (finally).  It should make adding additional products or developers to your account easier.

Downloads

The download list was sorting by release date – per product SKU.  Not so great to look at when you have more than one product in your account.  We updated the list to sort by release date across all SKUs.  Should make finding the most recent releases to products easier.

Schema Search is an API from CornerstoneDB that allows users to search multiple database providers schema.

This tool is very useful for finding all stored procedures or views that reference a column through an API that works across multiple database vendors.  This is quite common when you want to update the schema for a database and you want to ensure you don’t break any existing uses of that column in the database. 

We also include a sample user interface to allow for interactive use of the API, and to serve as an API sample application.

Schema Search Sample UI

The Schema Search Tool sample WinForms UI ( and the C# source code) are included with every developer license.  This UI showcases the possibilities behind the API, and allows for quick interactive sessions when you don’t want to write code.

You can easily search database schema across the following providers:  Microsoft SQL Server, VistaDB, Microsoft SQL Compact (SQLCE) or MySQL.

The sample UI is very functional, and showcases the powerful Schema Search API in less than 500 lines of code.

Schema Search API Example

All CornerstoneDB Tool API’s are designed with the same requirements. Small learning curves, allow flexible application of the API, and solve one well defined developer need. Too often tool API’s resemble Swiss Army Knives with huge learning curves and functionality that is seldom used.  We are striving to provide small, complete APIs for functionality most developers need.

The following is an example of using the Schema Search API to connect to a VistaDB database and search for a keyword across views and stored procedures.  We could also have searched within Foreign Keys, Tables, and Columns.

Create a new SearchTool object

A new SearchTool object can be created using a provider specific connection string and the provider name.

SearchTool searchTool = new SearchTool(@"Data Source = C:\Database.vdb4", "vistadb");

Execute a search

Using the SearchTool object, we can then execute a search with the keyword, search filter options and a search type to get the results back in a new SearchResult object.  Searches can be exact matches, wildcard match, and regex expressions.  This allows for a wide range of matching scenarios.

SearchResult result = Tool.Search("CompanyID", new List<SearchTool.SearchFilters>() 
            { SearchTool.SearchFilters.StoredProcs, SearchTool.SearchFilters.Views },
                SearchTool.SearchType.ExactMatch);

Iterate over the results

The result object contains database schema objects that match the search.  This collection could then be used to make changes to the database, write out change logs, anything that you need.  We plan to use this functionality internally in some up coming tools ourselves.

Console.WriteLine(string.Format("Views matched search {0}", result.Views.Count));
foreach (View v in result.Views)
{
    Console.WriteLine(string.Format("View Name: {0}", v.Name));
}

How can you get the Schema Search tool

Currently Schema Search and many other database tools are available for beta trial signup here.

We have spent a lot of time on our Entity Framework provider over the past 18 months or so.  There is a sample EF provider from Microsoft that illustrates basic points, and if you look at all the open source databases they pretty much all used the default code in their implementations.  But what if that sample code doesn’t implement something?  Or if it is just plain wrong?

Unexpected Results

I have to tell you we didn’t expect the results we got from this test at all.  We expected that we probably had some room for improvement and that SQL Server would ace all the tests.  Didn’t turn out that way.

Entity Framework Compliance?

The short answer to this question is that there IS NO compliance test to make sure your EF provider works correctly, or matches some baseline specification.  We really wanted some way to see if we matched up against the SQL Server provider, because we try really hard to be as compatible with SQL Server as possible. 

This post is about the journey we have taken so far in trying to figure out what that compliance might look like, how do we score today, what tests are missing, and what do others do to test their providers?

Standard LINQ test?

Well, the first thing I thought of was the LINQ 101 Samples from the MIX 2007 conference that Microsoft produced.  I have seen a number of other providers implement them as “proof” of their compliance, so I thought that would be a good starting point.  We found the 101 LINQ samples page on Microsoft, but most of them are not database related.

There is also a post on the ADO.Net Team Blog about 101 samples, but those are pretty old and still referencing Orcas as the target (Visual Studio 2008).  The most recent version of this we could find was on CodePlex under the ADO.NET Samples project.

Getting started

We first noticed that the Northwind they used in their tests is not the standard Northwind, or the NorthwindEF you see in most demos.  This Northwind has some specific changes to test some functionality.  So we migrated their database to VistaDB 4 and started using it.

You would think that with the promise of EF abstracting you from the database it would be pretty easy to use the same model across two providers.  Wrong, dead wrong.  It is a lot more complicated than it should be, and Matt did a great blog post about the steps he had to go through to make a single Entity Framework model work with two providers.

90 Tests?

Yea, we know.  It is called the 101 LINQ Samples, but there are actually only 90 tests in it, and some of them don’t work against the schema included.  The EF model is huge (see image zoomed WAY out).

The sample model worked fine in VistaDB 4 EF using Visual Studio 2010, but the tests that are in that sample project are not written as unit tests.

Building Unit Tests for the Samples

So we built unit tests using a Visual Studio 2010 test project.  Each sample was written to accept a NorthwindEntities data context object, and execute the query.

Then we build a test runner for each test that executes the VistaDB EF model, then the SQL Server model, and then compares the results to make sure we both returned the exact same data.  If a test blows up it is flagged as a failure for that provider.  If both providers succeed, but the results are different then it is still a failure.

Example Test Code

[TestMethod]
public void Test01()
{
    List<Customer> vdbResult = null;
    List<Customer> sqlResult = null;

    ExecutionResults vdb = ExecutionResults.Passed;
    ExecutionResults sql = ExecutionResults.Passed;
    CompareResults compare = CompareResults.Match;

    try
    {
        vdbResult = Samples.Sample01(EntitiesCache.VDBEntities);
    }
    catch (Exception e)
    {
        vdb = ExecutionResults.Failed;
    }
    try
    {
        sqlResult = Samples.Sample01(EntitiesCache.SQLEntities);
    }
    catch (Exception e)
    {
        sql = ExecutionResults.Failed;
    }

    if (!ResultCompare.Compare(vdbResult, sqlResult))
        compare = CompareResults.Inequal;

    TestResult(vdb, sql, compare);
}

Test Result Code

static void TestResult(ExecutionResults vdb, ExecutionResults sql, CompareResults compare)
{
    Assert.IsTrue(!(vdb == ExecutionResults.Failed && sql == ExecutionResults.Passed), 
            "VistaDB Failed to execute the query and SqlServer passed.");
    
    Assert.IsTrue(!(vdb == ExecutionResults.Passed && sql == ExecutionResults.Failed), 
            "SqlServer Failed to execute the query and VistaDB passed.");

    Assert.IsTrue(!(vdb == ExecutionResults.Failed && sql == ExecutionResults.Failed), 
        "Both SqlServer and VistaDB failed to execute the query");

    Assert.IsTrue(!(compare == CompareResults.Inequal), 
        "Both SqlServer and VistaDB executed the query both got different results");
}

Actual Sample Code

Each sample has a code block like this one to ensure the code is the exact same for each provider.

//Where - Simple 1
//This sample uses WHERE to find all customers in Seattle.
public static List<Customer> Sample01(NorthwindEntities Entities)
{
    var vdbQuery = from cust in Entities.Customers
                    where cust.City == "Seattle"
                    select cust;

    List<Customer> customers = new List<Customer>();
    foreach (Customer c in vdbQuery)
    {
        customers.Add(c);
    }
    return customers;
}

Tests run in Visual Studio 2010

Some tests were not implemented, we could not get them to compile against the provided EF model at all.

Test Results

Provider

Pass

Fail

Not Impl

VistaDB 4

77

4

9

SQL Server 2008 R2

71

10

9

The results surprised us quite a bit.  VistaDB had 6 fewer failures than SQL Server 2008 R2 using the same Entity Framework model.  And every test VistaDB failed, SQL Server also failed. 

There were no tests that VistaDB had incorrect or different results than SQL Server 2008 R2.

Next Steps

We will continue to implement more tests, and more importantly find out why they don’t work in SQL Server.  I took all of the samples that failed in SQL Server and ran them in LINQPad also.  They failed there as well, so it was not our model or harness.  I also generated a new EF model specifically for SQL Server and made sure it was not something in our changing of the model to work with both VistaDB and SQL Server.  In every case the tests that failed in our sample also failed in LINQPad and the other model.

I would love to get this same test to work with other providers, but because their types don’t match SQL Server the way VistaDB does it would be a lot of work.  Perhaps one of these days we will have the time to attempt it.

SQL CE couldn’t generate the model

I tried to then take all the same sample and run it against SQL CE.  The model would not even load against SQL CE.  The SQL CE provider couldn’t handle the complexity of the model as it appears it doesn’t handle dbo schema correctly and tries to put all the objects in the same collection.  This results in a duplicate column on things like ID that are used in multiple tables.  I tried this in Visual Studio 2010 with a new model generated just for SQL CE with a database migrated directly out of SQL Server 2008.