Linqpad

As an MVP, I sometimes receive licenses to software from the vendors for my usage. Some of them become indispensable for me and I feel in the obligation to write a review (yes, it's a biased review, as I really like the tool and use it on a daily basis ) as a way to say thank you!

One of these tools is Linqpad (https://www.linqpad.net/). It's a simple tool, with a small footprint, but I have used it in so many ways that I find it incredible. There is a free version that has a lot of features to start, but I really recommend the paid version (if you have the $125 to spend, the Premium edition has even a debugger to debug your snippets).

Introduction

Once you open Linqpad, you will see a simple desktop like this:

At first, the name of the tool may indicate that this is a notepad for linq queries, but it's much more than that! If you take a look at the Samples pane, you can see that there's even an Interactive Regex Evaluator.

A closer look at that pane shows that you are not tied to C#: you can also use F# there. In fact, there is a full F# tutorial there. If you open the Language combo, you can see that you can use also VB or SQL queries.

My first usages in Linqpad were to learn Linq (the name is Linqpad, no?). At the beginning, Linq seems a little bit daunting, with all those extension methods and lambdas. So, I started to try some Linq queries, making them more difficult as my knowledge was improving. In Linqpad, you have three flavors of code: Expressions, where you have a single expression evaluated; Statements, where you have some statements evaluated and Program, where you can have a full program run in Linqpad (I use this when I want to run a console program and don't want to open Visual Studio and create a new project).

In the Expression mode, you can enter a single expression, like this:

from i in Enumerable.Range(1,1000)
  where i % 2 == 0
  select i

If you run it, you will see the result in the Results pane:

As you can see, all the results are there, there is no need to open a console window or anything else. And, what's better, you can export the results to Excel, Word or HTML. You can also use the other Linq format, the functional one:

Enumerable.Range(1,1000).Where(i => i %2 == 0)

After that, you can start tweaking your code and clicking on the Run button and observing the results. If you have the paid version, you also have Intellisense in the code, so you can check the syntax.

For example, to get the sum of the squares of the even numbers, we can do something like this:

If we have something more complicated than a single expression, we can run it using the C# statements. For example, to get all methods and parameters of the methods in the Directory class, we can use these statements:

var methodInfos = typeof(Directory).GetMethods(BindingFlags.Public | 
  BindingFlags.Static);

methodInfos.Select(m => new 
{
  m.Name, 
  Parameters = m.GetParameters() 
}).Dump();

You may have noticed something different in the code above: the Dump method. Linqpad adds this method to dump the values to the results pane. It is very powerful, you don't need to know the type of the object, all the properties are shown there:

And you are not limited to old C#, you can also use C#7 features and even async programming. For example, this code (based on ) will download asynchronously some pages from the web and will display their sizes:

async Task Main()
{
    await SumPageSizesAsync().Dump();
}

private async Task<List<string>> SumPageSizesAsync()
{
    var results = new List<string>();
    // Declare an HttpClient object and increase the buffer size. The
    // default buffer size is 65,536.
    HttpClient client =
        new HttpClient() { MaxResponseContentBufferSize = 1000000 };

    // Make a list of web addresses.
    List<string> urlList = SetUpURLList();

    var total = 0;

    foreach (var url in urlList)
    {
        // GetByteArrayAsync returns a task. At completion, the task
        // produces a byte array.
        byte[] urlContents = await client.GetByteArrayAsync(url);

        // The following two lines can replace the previous assignment statement.
        //Task<byte[]> getContentsTask = client.GetByteArrayAsync(url);
        //byte[] urlContents = await getContentsTask;

        results.Add(DisplayResults(url, urlContents));

        // Update the total.
        total += urlContents.Length;
    }

    // Display the total count for all of the websites.
    results.Add(
        $"\r\n\r\nTotal bytes returned:  {total}\r\n");
    return results;
}

private List<string> SetUpURLList()
{
    List<string> urls = new List<string>
            {
                "https://msdn.microsoft.com/library/windows/apps/br211380.aspx",
                "https://msdn.microsoft.com",
                "https://msdn.microsoft.com/library/hh290136.aspx",
                "https://msdn.microsoft.com/library/ee256749.aspx",
                "https://msdn.microsoft.com/library/hh290138.aspx",
                "https://msdn.microsoft.com/library/hh290140.aspx",
                "https://msdn.microsoft.com/library/dd470362.aspx",
                "https://msdn.microsoft.com/library/aa578028.aspx",
                "https://msdn.microsoft.com/library/ms404677.aspx",
                "https://msdn.microsoft.com/library/ff730837.aspx"
            };
    return urls;
}

private string DisplayResults(string url, byte[] content)
{
    // Display the length of each website. The string format
    // is designed to be used with a monospaced font, such as
    // Lucida Console or Global Monospace.
    var bytes = content.Length;
    // Strip off the "https://".
    var displayURL = url.Replace("https://", "");
    return $"\n{displayURL,-58} {bytes,8}";
}

When you run it, you will see something like this:

And you are not tied to the default C# libraries. If you have the Developer or Premium versions, you can download and use NuGet packages in your queries. For example in this previous article, I've shown how to use the Microsoft.SqlServer.TransactSql.ScriptDom package to parse your Sql Server code. You don't even need to open Visual Studio for that. Just put this code in the Linqpad window:

static void Main()
{
    using (var con = new SqlConnection("Server=.;Database=WideWorldImporters;Trusted_Connection=True;"))
    {
        con.Open();
        var procTexts = GetStoredProcedures(con)
          .Select(n => new { ProcName = n, Tree = ParseSql(GetProcText(con, n)) })
          .Dump();
    }
}

private static List<string> GetStoredProcedures(SqlConnection con)
{
    using (SqlCommand sqlCommand = new SqlCommand("select s.name+'.'+p.name as name from sys.procedures p " +
      "inner join sys.schemas s on p.schema_id = s.schema_id order by name", con))
    {
        using (DataTable procs = new DataTable())
        {
            procs.Load(sqlCommand.ExecuteReader());
            return procs.Rows.OfType<DataRow>().Select(r => r.Field<String>("name")).ToList();
        }
    }
}

private static string GetProcText(SqlConnection con, string procName)
{
    using (SqlCommand sqlCommand = new SqlCommand("sys.sp_helpText", con)
    {
        CommandType = CommandType.StoredProcedure
    })
    {
        sqlCommand.Parameters.AddWithValue("@objname", procName);
        using (var proc = new DataTable())
        {
            try
            {
                proc.Load(sqlCommand.ExecuteReader());
                return string.Join("", proc.Rows.OfType<DataRow>().Select(r => r.Field<string>("Text")));
            }
            catch (SqlException)
            {
                return null;
            }
        }
    }
}

private static (TSqlFragment sqlTree, IList<ParseError> errors) ParseSql(string procText)
{
    var parser = new TSql150Parser(true);
    using (var textReader = new StringReader(procText))
    {
        var sqlTree = parser.Parse(textReader, out var errors);

        return (sqlTree, errors);
    }
}

You will see some missing references. Just press F4 and it will open the following screen:

Click the Add NuGet button and add the Microsoft.SqlServer.TransactSql.ScriptDom package, then run the program. You will see something like this:

You can even click on the ScriptTokenStream result, to see the list of tokens in the procedure:

You can also simplify the query by using the connections available in Linqpad. Just go to the connections pane, add a new connection and point it to the WorldWideImporters database. Then select the connection in the connections combo and use this code:

void Macsharpin()
{
    ExecuteQuery<string>("select s.name+'.'+p.name as name from sys.procedures p " +
      "inner join sys.schemas s on p.schema_id = s.schema_id order by name")
          .Select(n => new 
            { 
              ProcName = n, 
              Tree = ParseSql(ExecuteQuery<string>("exec sys.sp_helpText @objname={0}",n).FirstOrDefault()) 
            })
          .Dump();
}

private static (TSqlFragment sqlTree, IList<ParseError> errors) ParseSql(string procText)
{
    var parser = new TSql150Parser(true);
    using (var textReader = new StringReader(procText))
    {
        var sqlTree = parser.Parse(textReader, out var errors);

        return (sqlTree, errors);
    }
}

You will see the same results. As you can see, you don't even need to open the connection and create the command to run it. You can run your queries against your databases the same way you would do with any data. And if you are a SQL guy, you can run your queries directly using the SQL language. And, if you are brave and want to learn F#, you have here a really nice tool to learn.

Conclusions

At first, the size and appearance of Linqpad may fool you, but it's a very nice tool to work, saving you a lot of time to try and debug your code. If you have some code snipped that you want to test and improve, this is the tool to use. And, one feature that I didn't mention that's invaluable when you are optimizing your code is the timing feature. After the execution of each query, Linqpad shows the execution time, so you can know how long did it take to execute it.