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.

More...

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

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

More...

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.

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

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.

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

There are some new operations in .Net 4 Entity Framework, this a quick example of each working with VistaDB 4 and Visual Studio 2010.  These changes were mostly made to bring LINQ to Entities inline with the other LINQ providers in .Net.  Of the list below I think that Single() was the one that most people were confused about because if you used it you would get weird errors that didn’t make a lot of sense.

For a complete list of LINQ to Entities operators visit the Supported and Unsupported LINQ Methods on MSDN.

Entity Framework in .Net 4

The operations I want to demonstrate are the new Contains(), Single(), SingleOrDefault(), and DefaultIfEmpty().

These operations are all new in .Net 4, and yes they work with VistaDB 4.  I started with a simple one table database called Feedback.  The only table has 3 columns:

• int FeedbackID
• NText FeedbackText
• DateTime FeedbackDate

I added a few small text entries, including “I like grapes'” to search against with the Contains() operator.

More...

Yes, Visual Studio 2010 was released today to much fanfare in Las Vegas. 

We of course had to immediately download it (along with what must be some major percentage of MSDN subscribers) and give it a test. 

Use Build 14 or higher

VistaDB 4.0 Build 14 works great with the VS 2010 Release.  Versions prior to this should probably not be used as there were some changes we had to make in order to get everything to work with VS 2010 in the first place. 

I have run through all of our NUnit tests in both .Net 2 and .Net 4 and all of them work correctly.  The Visual Studio plugins work, and the Entity Framework provider seems to be working fine as well.  The screen shot above is me generating a .Net 4 EF model on our Tickets Database.  (Note you can use the Include Foreign Key Columns only if you are using .Net 4)

More...