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

More...

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.

More...

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.

More...

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.

More...

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\).

More...

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.

More...

The new database tools from CornerstoneDB are getting closer to beta release and  the development team would like to give a few sneak peeks to some of the CornerstoneDB features and advantages over the next few weeks.

Visual Studio 2010 project templates

All of the CornerstoneDB Tools will include project templates built into Visual Studio 2010. CornerstoneDB users will have the advantage of getting up and running fast using all of the powerful API’s. All project templates can be found under File > New Project > CornerstoneDB within Microsoft’s Visual Studio 2010.

Each of the templates will include all the references to the components required, and a quick start file in either C# or VB.Net that has some basic operations to get your started.  These are not meant to be fully working samples, just the snippets you will need more often for the type of project you are building.

More...

I was recently tasked with creating a number of samples testing the compliance of both VistaDB and Microsoft SQL Server with Linq to Entity queries. Each sample tested if the provider was able to execute the query without error and then compare both queries to ensure that both providers returned the same results. To ensure valid test results I needed all queries to be executed against a single entity model for both database providers. Entity Framework was designed in a generic manner for this very purpose so my task should be trivial correct? In this article I will explain the complications I ran into during my task.

Generating the original model

My original Entity Data Model was generated from a VistaDB Northwind example which I had an identical copy of in my local SQL Express server. The process of generating a new data model will add a new appconfig file to your project if there is not already one present. If there is already an appconfig file present the data model will simply add a new connection string to it. Entity Framework connection strings always includes paths to the three files that make up the data model, the database connection string and provider information. The EF connection string that was auto generated for Northwind.vdb4 looks like this.

<add name="NorthwindEntities" connectionString="metadata=res://*/NorthwindModel.csdl|
res://*/NorthwindModel.ssdl 
|res://*/NorthwindModel.msl;
provider=System.Data.VistaDB;
provider connection string='Data Source=&quot;C:\Northwind.vdb4&quot;'" 
providerName="System.Data.EntityClient" />

Things to note about the connection string:

· The path of the CSDL file */NorthwindModel.csdl

· The path of the SSDL file */NorthwindModel.ssdl

· The path of the MSL file */NorthwindModel.msl

· The database connection string Data Source=C:\Northwind.vdb4

· Provider Name System.Data.VistaDB

More...