Setting Up LINQPad Toolset To Assist In Visualization And Manipulation of SQL Queries And Backend Data

The purpose of this article is to describe the steps necessary to setup the LINQPad toolset to assist in visualization and manipulation of SQL queries and backend data.

In this Article we will be creating our own database to interact with LinqPad

The Following steps will be involved:

      • Installing LINQPad
      • Connecting LINQPad to our C#.NET database.
      • Implementing queries
      • Understanding the data visualization

To Begin: Installation

 

Before beginning this tutorial, be sure to download LINQPad to your computer.

  • Download LINQPad
  • LinqPad is a free tool with multiple priced tiers.

    Installation

    I have found this tool to be very helpful for learning about backend operations from simple queries that you could use for your API calls, to writing triggers, unit tests, and more difficult backend concepts.

    For this example I will be using the version 7 with support for .NET 6. You may be prompted to install .NET 6, which is necessary for the program to run if you do not have it already installed.

    Once you open LINQPad, you may choose to create a new database connection, or use the Demo database. This example will be using the Demo database but I will go over connecting to another database.

    Creating a connection to a database

    Begin by selecting to add a connection and choose a data context that is relevant to your project. You can choose to have the context built automatically, or a specific data context.

    I have chosen Entity Framework Core for my example.

    You must fill out the form with the essential data.

      • For specifying the Server IP, you can either use a period or localhost to specify localhost, but if your server is somewhere else, specify the IP.

      • You can use Windows Authentication or SQL Server authentication by toggling the Use Windows Authentication and using the User ID and Password fields below.

      • The entire connection string is visible below if needed.

      • Test the connection below and do not proceed until there is a success message.

    Select OK when you are ready to proceed

    Your new database connection will appear at the top of the connection stack. Clicking the name will open the connection so you can see the data within such as tables and their associated columns.

    The right window is where query creation and editing is done. The page can be saved to use later, and can be accessed in the window on the bottom left.

    Implementing Queries

    The main reason we use LINQPad is for the data visualization that is generated once we run our queries. Selecting a table with a right click brings up a selection of instant queries, and choosing the Albums.Take(100) option takes the latest 100 entries available. Once we choose to run the query with the green play button, the query language will auto populate within our Query1 window.

    We are not only limited to these quick queries, we are able to write our own in the top right window, as well as save them for later, change the language we wish to use, vary our .NET version, and change our database connection. Try making changes to the Albums.Take(100) query and you will see changes within the results!

    For use ideas:

      • Database testing can be done without having to go through lengthy procedures. The queries are much quicker this way, saving us time on long-winded trigger tests.

      • Searching the backend to check for specific validation cases or accounts and their linking data.

      • General query creation and learning.

    Understanding the Data Visualization

    A results table shows the results in a manner that is much easier to digest and quicker than using your test server.

    The text links within Artist and Tracks have become navigation properties to new tables. Opening one of them keeps the original table, but adds the new table. Navigating a new database can be difficult and visual interpretations can be very beneficial.
    The lower right window is not only for results. You can select any of the available options and each provides a different service. If we view the SQL table, we see that the C# expression we created above has been translated into sql.

    The SQL window can also be used as a translation tool if you are learning SQL with your initial language in the upper right window.

    The IL + Native window does a similar function and provides translation in the native language chosen, the result in IL, and in the native disassembly.
    The Tree tab shows the expression tree that was generated from our query. Hovering over the nodes will highlight their position within the query logic you wrote. Studying the structure of the tree can be very beneficial in understanding the structure on how a query is built. Here is the structure of the Albums.Take(100) that we ran earlier.
    Changing your query can drastically change the appearance of the tree. A select all statement with a filter attached can give a completely different appearance!

    Breaking down everything in the tree view would be a lengthy task, but there are many resources available to get the most out of this particular tool. 

    Conclusion

    While the tool is excellent for understanding the database, it is one of many. It is important to understand what we can do with the tools we have and what works best for our current situation. The principles remain the same and as you progress in learning about the database, you will be able to utilize and identify the tool that works best for you.

    About Intertech

    Intertech is a Software Development Consulting Firm that provides single and multiple turnkey software development teams, available on your schedule and configured to achieve success as defined by your requirements independently or in co-development with your team. Intertech teams combine proven full-stack, DevOps, Agile-experienced lead consultants with Delivery Management, User Experience, Software Development, and QA experts in Business Process Automation (BPA), Microservices, Client- and Server-Side Web Frameworks of multiple technologies, Custom Portal and Dashboard development, Cloud Integration and Migration (Azure and AWS), and so much more. Each Intertech employee leads with the soft skills necessary to explain complex concepts to stakeholders and team members alike and makes your business more efficient, your data more valuable, and your team better. In addition, Intertech is a trusted partner of more than 4000 satisfied customers and has a 99.70% “would recommend” rating.