Have you tried to use LINQ to query a database using Visual Studio? It can be a frustrating experience of things that compile fail at runtime, and that edit / compile / test cycle can quickly lead to hours of lost time trying to get a single complex query to work correctly.
LINQPad is an Editor for LINQ?
LINQPad is sort of like a Notepad, you can write and edit .linq files using it. But that is where the Notepad similarity stops. You can execute your LINQ queries and see the results without having to run your application. It is truly something you have to watch in order to believe how much more productive it can make your writing of LINQ.
How I Found LINQPad
When I was first working on the VistaDB product store and account manager writing LINQ queries against my Entity Framework objects was incredibly frustrating. Most of the documentation and samples I found were for Linq2Sql, which is similar syntax… But not the same. And worse, most of the syntax compiles fine, but blows up at runtime with cryptic error messages.
My typical dev and test cycle was around 5 minutes to compile the DAL / Site, login to the account, navigate to the correct page, and visit the yellow screen of death from ASP.NET. It was not fun, so I started working in a stand alone tester app I built just for this purpose. Write the query, compile, debug, step, step, read exception and try to decipher it.
I was reading a post on Stack Overflow about one of those cryptic errors when someone suggested to the poster they use LINQPad to test their queries first before putting them into their apps. Wow, what a great idea! Where was this tool? (You can download it for free from Linqpad.net.)
LINQPad to the Rescue
LINQPad allows you to execute single LINQ commands against an existing EF model, or even to write .NET code in the editor and execute it like a little dynamic .NET environment. The main application is free, but there is an auto-complete feature to the editor that you must pay in order to activate. Believe me, it is worth it to pay for the license, you also support the author and show him the application is worth money. The license is very inexpensive and well worth the price in order to get intellisense like behavior on your LINQ queries.
The first couple of versions I played with worked great with SQL Server, but could not load the VistaDB EF provider. I was bummed, but at least I could debug my queries against SQL Server, and then run against VistaDB to make sure it worked the same way. But I recently got an email from the author of LINQPad that it could load VistaDB, in version 2.
Now with VistaDB Goodness
So now I can use it with VistaDB 4 and test my LINQ queries the same way I do the SQL Server queries. The steps are a little difficult the first time you do them, but nothing that should scare off something taking up LINQ (it is pretty scary all by itself).
Get LINQPad to Use your Developer License
Ok, first things first. You have to get LINQPad to be able to load your VistaDB developer license. Since it obviously wasn’t compiled with VistaDB in mind, you need to use the app.config approach. In this case, you create a LINQPad.config file in the same directory as the executable.
="1.0"="utf-8"
<configuration>
<appSettings>
<add key="VistaDBUseDesignTimeLicense" value="true"/>
</appSettings>
</configuration>
This will tell the engine to allow LINQPad to use your design time license. All that means is you must have a valid developer license on the machine where you are running LINQPad.
Build an EF Model
You can build your EF model in an EXE or DLL assembly, but the model namespace must be public. It is going to be reflection loaded by LINQPad, so it has to be able to reach it.
I built the project as a .NET 3.5 Console Application and then added an Entity Framework model to our VistaDB Site database. I don’t normally recommend a complete database as a single Model, but this was just for a demonstration.
Now build the application so you have an output of the DLL or EXE. You will need this to point LINQPad to in order to load your model.
LINQPad Add Connection
Within LINQPad, there is an area on the left that represents all your current connections to databases. This is sort of like the Server Explorer in Visual Studio. The top entry is an Add Connection link, click that to see the Choose Data Context dialog.
Choose the Entity Framework from a typed data context in your own assembly. This will be the application we just built above.
Select Next and a LINQPad connection dialog appears. Choose the BROWSE link on the Path to Custom Assembly textbox.
Browse to your assembly and choose the DLL or EXE, a Choose Custom Type dialog will appear asking you to identify which models you want to use from that assembly.
After choosing the assembly, the connection dialog will look something like this image below. It is not correct yet, but if you take a moment and copy the Server field, it includes the actual filename you need in the next step.
Under the PROVIDER group box, choose the OTHER checkbox and the dialog will change.
Choose the System.Data.VistaDB provider name from the dropdown, and then enter a valid connection string. I recommend you just use the Data Source= and the name of the file from the previous step. You can’t use macros like DataDirectory because the working path of LINQPad is not your application. You need to provide an absolute path. If you don’t remember the VistaDB connectionstring options, visit the website.
The Remember this connection means that LINQPad will reload that connection at next startup. The TEST button should load and return a success to your database. If it fails with file not found, then you probably have the wrong path to the database, or an error in your connection string.
Success!
Now the complete model is visible in the connection area of LINQPad, and you are ready to start writing LINQ queries against VistaDB.
The complete model is shown, and you can navigate down through the entities to look at their properties.
Query Results!
Now you can write a query against the EF model and get answers by running them directly within LINQPad.
In this case, I am selecting all the shipping products and ordering them by the productid. See the RESULTS selected under that query? You can select the Lambda symbol and see what the query looks like as a Lambda.
Notice the Lambda syntax looks quite a bit different. I personally really like this ability to have LINQPad show me another way of doing the same query. Sometimes one syntax or another is much clearer to me on what is happening.
I took the Lambda version and executed it to get the same results. The SQL option doesn’t work for anything other than Linq2Sql entities, because Entity Framework doesn’t give you a nice way to get the SQL out of an execution.
Off to Learn More LINQ?
Hopefully, this post will get you off to learn more LINQ using VistaDB (or any other third party EF provider actually). LINQ is one of those technologies that has a huge learning curve to it, but the rewards are pretty spectacular once you have a basic grasp of the syntax. You can do things much more expressively in LINQ than you can using SQL. The ability to query your objects within your native programming language is a big change in how programmers access and manipulate data.
I find that I enjoy writing LINQ queries in LINQPad due to the instant feedback of running the queries. There are a LOT of powerful options in LINQPad that I have not even touched on here. Maybe another post with more details about the power of LINQPad would be in order. What do you think?
