LINQPad [extension] methods

LinqPad extension methods - Part 1 of 2

^{Go to part 2}

Besides the well-known myQuery.Dump("Query result:"), another feature to mention is the Util class: It contains many quite handy methods (some of them I've mentioned, but there are a lot more).

Also interesting is that you can modify the way Dump() works.

Finally I'll show you how you can make changes permanent (i.e. insert, update, delete LINQ queries) using SubmitChanges() or SaveChanges() as well as how you can access the internal connection object of LinqPad.

To round it up, I'll show you how you can create simple 2d graphic inside of LinqPad (drawing lines, bitmaps or functions).

So, here's a collection of built in LinqPad features (from my own experience with the tool):

.Dump()

(parameters available in LinqPad v5.03.08 and above)

The .Dump() extension method consumes and prints (almost) everything.

But did you know there are a couple of parameters available? Take a look at this code snippet:

var obj=new { a="Hello", b=5, c="World", d=new { y=5, z=10 } };
obj.Dump(description: "1st example", depth: 5, toDataGrid: false, exclude: "b,d");
obj.Dump("2nd example", exclude: "a,c");
obj.Dump("2nd example", exclude: "+b,d"); // new in V5.06.06 beta

The 1st example prints only variables a and c and hides b and d, the 2nd example does the opposite (note that it specifies only 2 of the available parameters). The variablesy and z cannot be hidden individually, because they are not at the top level.

The following parameters are available (all are optional):

description [string] - provides a description for the object to dump
depth [int?] - limits how deep the objects are recursively inspected
toDataGrid [bool] - if true, the output is formatted as a datagrid rather than as RichText
exclude [string] - if you provide a comma-separated list of variables, they will be excluded from the output (in the example "a,c": b and d are shown, a and c are hidden)
exclude [string] with "+" prefix - the prefix inverts the logic of the exclude parameter. This means, if you provide a comma-separated list of variables, all except the ones specified are hidden (in the example "+b,d": b and d are shown, all others hidden)
store included and excluded properties in a variable (new since LinqPad V5.09.04):
var x=Util.ToExpando(obj, "a, c", "b, d"); x.Dump();
The first string contains a list of properties to include, the second string a list to exclude
expand on click: If you use .OnDemand("click me").Dump(); instead of .Dump(), it will display a link you can click on to expand. Useful if you want to inspect values, e.g. Util.OnDemand("Customer-ID: " + customerObject.ID.ToString(), ()=>customerObject, false).Dump(); to always show the ID per default but reveal the details of customerObject only if you're interested in.

More advanced topics about Dump can be found
here (Customizing Dump) and there (Extensions Part 2).

Util.WriteCsv

(new: available since LinqPad version v4.45.05 (beta))

Util.WriteCsv (Customers, @"c:\temp\customers.csv");

This will write the content of the table Customers to the CSV file c:\temp\customers.csv. You can also find a nice example how to use Util.WriteCsv and then display the CSV data in Linqpad's result window here.

Hints:

To get/create a CSV file which is in the same directory as the query, you can use:
var csvFile=Util.CurrentQueryPath.Replace(".linq", ".csv");
If the table is large, use ObjectTrackingEnabled = false; before you write the CSV to avoid caching it in memory.

If you want to output a table in XML format rather than as comma-separated file, you can do it like:

  var xmlFile=Util.CurrentQueryPath.Replace(".linq", ".xml");
  var xml = XElement.Load(xmlFile);
  var query =
    from e in xml.Elements()
    where e.Attribute("attr1").Value == "a"
    select e;
  query.Dump();

This example returns all elements having the attribute attr1 which contains the value "a" from an XML file which has the same name as the query and is contained in the same path. Check out this link for more code samples.

Util.GetPassword

var pwd = Util.GetPassword("UserXY");

This will retrieve the password from LinqPad's built in password manager. To create & change the password, open the "Password manager" menu item in the "File" menu of LinqPad. If there is no password saved when you run the C# code, a password dialog will open up asking you for the password and you have the choice to create and save it on the fly by checking the save password checkbox (in the example, the password for "UserXY" would be saved, and later on you can find this entry in the Password manager).

Advantages are that you can store the password in the LinqScripts you create securely, separately and encrypted in Windows' user profile (it is stored in %localappdata%\LINQPad\Passwords as a file). LinqPad uses Windows DPAPI to protect the password.

Also, the password is stored centrally, so if you need to change it, you can do it in the menu and it immediately applies to all scripts you've created.

Notes:

If you don't want to save the password and just bring up a password dialog, you can use the 2nd parameter as follows:
var pwd = Util.GetPassword("UserXY", true);
This will uncheck the save password checkbox in the password dialog (however, the user is still able to check it and choose to save anyway).
If you require the password to be stored in a SecureString, you can use this helper function (n.b.: to get the extension method .ToSecureString() used, please follow this link at Stackoverflow - it also allows you to convert it back if needed):
System.Security.SecureString GetPasswordSecure(string Name, bool noDefaultSave=true)
{
return Util.GetPassword(Name, noDefaultSave).ToSecureString();
}

Util.Cmd

This method works like a command processor. You can invoke all commands you know from the Windows console.

Example 1 - dir:

Util.Cmd(@"dir C:\");

This will output the result of the directory without the need to .Dump it. Storing it in a variable has the advantage that you can use further Linq queries on it. For example:

var path=@"C:\windows\system32"; 
var dirSwitch="/s/b";
var x=Util.Cmd(String.Format(@"dir ""{0}"" {1}", path, dirSwitch), true);
var q=from d in x 
        where d.Contains(".exe") || d.Contains(".dll")              
        orderby d
    select d;
q.Dump();

This will dump all files with file extensions ".exe" or ".dll" contained in C:\windows\system32. The /s switch is used to recurse all subdirectories and /b is used for bare output format. Note that the second parameter of the Cmd method is specified to suppress the console output in order to show only the filtered result using the Dump method.

You can see that this is more flexible than the wildcards you have with dir since you can use the full flexibility of Linq's query engine.

Example 2 - text editor:

You can open a file in Notepad like this:

var filePath=@"C:\HelloWorld.txt";
Util.Cmd(@"%systemroot%\system32\notepad.exe", filePath);

Util.Image

Displays images from an URL. Example:

var url = "http://chart.apis.google.com/chart?cht=p3&chd=s:Uf9a&chs=350x140&chl=January|February|March|April";
Util.Image(url).Dump();

Util.ProgressBar, Util.Progress

Using Util.ProgressBar allows you to display a progress bar. You can use the following helper class:

public class ProgressBar
{
    Util.ProgressBar prog;
    
    public ProgressBar() 
    { 
        Init("Processing"); 
    }
    
    private void Init(string msg)
    {
        prog = new Util.ProgressBar (msg).Dump();
        prog.Percent=0;
    }

    public void Update(int percent)
    {
        Update(percent, null);
    }   
    
    public void Update(int percent, string msg)
    {
        prog.Percent=percent;
        if (String.IsNullOrEmpty(msg))
        {
            if (percent>99) prog.Caption="Done.";
        }
        else
        {
            prog.Caption=msg;
        }
    }
}

Simply use it as the following example shows:

void Main()
{
    var pb1= new ProgressBar();
    Thread.Sleep(50);
    pb1.Update(50, "Doing something"); Thread.Sleep(550);
    pb1.Update(100); Thread.Sleep(50);
}

You can alternatively use Util.Progress to update LinqPads integrated progress bar, for example:

Util.Progress = 25; // 25 percent complete

The difference is that it will not display in the results window, and you can't assign a message to it.

Util.RawHtml

Displays HTML in the output window. Example:

Util.RawHtml (new XElement ("h1", "This is a big heading")).Dump();

Hyperlinq, Util.HorizontalRun

You can use this example function

public void ShowUrl(string strURL, string Title)
{
    Action showURL = delegate() { Process.Start("iexplore.exe", strURL); };
    var url = new Hyperlinq(showURL, "this link", true);
    Util.HorizontalRun (true, "Click ", url, " for details.").Dump(Title);
}

to show hyperlinks in the result window - or any actions like opening your favourite editor. Usage:

ShowUrl("http://stackoverflow.com", "Check out StackOverflow");

Note that this function always works, while new Hyperlinq ("http://myURL", "Web site").Dump(); does not work for some kind of URLs (especially, if you have to pass port names like ":1234" as part of the URL).

Util.ReadLine

Reads input from the console. Example:

int age = Util.ReadLine<int> ("Enter your age");

As a synonym for Util.ReadLine<string>(), you can use Console.ReadLine() as well.

But there is more! You can create a simple JSON parser with the following snippet - quite useful, for example if you want to parse and test a JSON string on the fly. Save the following snippet as JSONAnalyzer.linq using a text editor and then open it in LinqPad (this is to add the references easily on the fly):

<Query Kind="Program">
    <Reference>&lt;RuntimeDirectory&gt;\System.Web.Extensions.dll</Reference>
    <Namespace>System.Web.Script.Serialization</Namespace>
</Query>

void Main()
{
    var jsonData=Util.ReadLine<string>("Enter JSON string:");
    var jsonAsObject = new JavaScriptSerializer().Deserialize<object>(jsonData);
    jsonAsObject.Dump("Deserialized JSON");
}

Now you can run it and simply paste a JSON string from the clipboard into the console - it will use the Dump function to display it as an object nicely - and you also get the error messages of the parser on the screen to fix issues. Very useful for debugging AJAX.

JSON

Util.ClearResults

If you need to clear the results window inside your script, use:

Util.ClearResults();

Either use it at the top of your script, or - if you're running multiple queries in a script - you should wait for user input before blanking the screen (e.g. by preceding it with Util.ReadLine).

Custom .Dump() - ICustomMemberProvider

Also interesting is, that you can change the output of the .Dump() method. Simply implement the interface ICustomMemberProvider, e.g.

public class test : ICustomMemberProvider 
{

      IEnumerable<string> ICustomMemberProvider.GetNames() {
        return new List<string>{"Hint", "constMember1", "constMember2", "myprop"};
      }
      
      IEnumerable<Type> ICustomMemberProvider.GetTypes() 
      {
        return new List<Type>{typeof(string), typeof(string[]), 
            typeof(string), typeof(string)};
      }
      
      IEnumerable<object> ICustomMemberProvider.GetValues() 
      {
        return new List<object>{
        "This class contains custom properties for .Dump()", 
        new string[]{"A", "B", "C"}, "blabla", abc};
      }

      public string abc = "Hello1"; // abc is shown as "myprop"
      public string xyz = "Hello2"; // xyz is entirely hidden
}

If you create an instance of this class, like

var obj1 = new test();
obj1.Dump("Test");

then it will output only Hint, constMember1, constMember2, and myprop, but not property xyz:

Linqpad dump

Displaying a MessageBox or InputBox in LinqPad

If you need to display a messagebox, look here how to do it.

For example, you can display an InputBox by using the following code

void Main()
{
    string inputValue="John Doe"; 
    inputValue=Interaction.InputBox("Enter user name", "Query", inputValue);
    if (!string.IsNullOrEmpty(inputValue)) // not cancelled and value entered
    {
        inputValue.Dump("You have entered;"); // either display it in results window
        Interaction.MsgBox(inputValue, MsgBoxStyle.OkOnly, "Result"); // or as MsgBox
    }
}

(don't forget to press F4 and add Microsoft.VisualBasic.dll and its namespaces to make this work)

Util.Run

(new: available since LinqPad version v4.52.1 (beta))

Allows you to run another LINQPad script from within your script or within your own .NET program or Windows service (by referencing the LINQPad4-AnyCPU version of LINQPad.exe). It executes the script just as the command line tool lprun.exe would do it.

Examples:

const string path=@"C:\myScripts\LinqPad\";
var dummy=new LINQPad.QueryResultFormat(); // needed to call Util.Run
Util.Run(path+"foo.linq", dummy);

This example runs the script foo.linq, which contains the following sample code:

void Main(string[] args)
{
    #if CMD
       "I'm been called from lprun! (command line)".Dump();
    #else
       "I'm running in the LINQPad GUI!".Dump();
       args = new[] { "testhost", "[email protected]", "[email protected]", "Test Subject" };
    #endif
    args.Dump("Args");
}

It allows you to check if the script was run from inside the LinqPad GUI or via lprun.exe or with Util.Run.

Note: The following variants of invocation might be helpful:

Util.Run(path+"foo.linq", dummy).Dump(); // obviously dumps the script output!
Util.Run(path+"foo.linq", dummy).Save(path+"foo.log"); // writes output into log
Util.Run(path+"foo.linq", dummy).SaveAsync(path+"foo1.log");     // async output log

SubmitChanges() - Linq To SQL

If you're using LinqToSQL, you might want to make changes permanent (for insert/update/delete operations). Since the database context is implicitly made by LinqPad, you need to call SubmitChanges() after each change as shown below.

Examples for (LinqPad-)Northwind database:

Insert

var newP = new Products() { ProductID=pID, CategoryID=cID, 
            ProductName="Salmon#"+pID.ToString() };
Products.InsertOnSubmit(newP);
SubmitChanges();

Update

var prod=(from p in Products
            where p.ProductName.Contains("Salmon")
            select p).FirstOrDefault();
prod.ProductName="Trout#"+prod.ProductID.ToString();
SubmitChanges();

Delete

var itemsToDelete=Products.Where(p=> p.ProductName.Contains("Salmon") ||
    p.ProductName.Contains("Trout"));
foreach(var item in itemsToDelete) { Products.DeleteOnSubmit(item); }
SubmitChanges();

Note: In order to get valid IDs for the previous examples, you can use:

var cID = (from c in Categories 
            where c.CategoryName.Contains("Seafood") 
            select c).FirstOrDefault().CategoryID;

var pID = Products.Count()+1;

before you invoke them.

SaveChanges() - Entity Framework

If you're using Entity Framework, you might want to make changes permanent as well (for insert/update/delete operations). Since the database context is implicitly made by LinqPad, you need to call SaveChanges() after each change as shown below.

The examples are basically the same as before for LinqToSQL, but you need to use SaveChanges() instead, and for inserting and deleting the methods have changed as well.

Insert

var newP = new Products() { ProductID=pID, CategoryID=cID, 
            ProductName="Salmon#"+pID.ToString() };
Products.Add(newP);
SaveChanges();

Update

var prod=(from p in Products
            where p.ProductName.Contains("Salmon")
            select p).FirstOrDefault();
prod.ProductName="Trout#"+prod.ProductID.ToString();
SaveChanges();

Delete

var itemsToDelete=Products.Where(p=> p.ProductName.Contains("Salmon") ||
    p.ProductName.Contains("Trout"));
foreach(var item in itemsToDelete) { Products.Remove(item); }
SaveChanges();

Note: In order to get valid IDs for the previous examples, you can use:

var cID = (from c in Categories 
            where c.CategoryName.Contains("Seafood") 
            select c).FirstOrDefault().CategoryID;

var pID = Products.Count()+1;

before you invoke them.

If you need to have transactions, take a look at this post: How to nest transactions.

this - database context

In LinqPad, the database context is applied automatically by using the combobox at the top and picking the right database for your query. But sometimes, it is useful to reference it explicitly, for example if you copy some code from your project out of Visual Studio, and paste it into LinqPad.

Your code snippet taken from the Visual Studio project very likely looks like this:

var prod=(from p in dc.Products
            where p.ProductName.Contains("Salmon")
            select p).FirstOrDefault();
prod.ProductName="Trout#"+prod.ProductID.ToString();
dc.SaveChanges();

Now what to do with dc? Of course, you could remove each occurrence of dc. in your query, but it is much easier. Just add it to the top of your snippet like so:

UserQuery dc { get => this; }
void Main()
{
    var prod=(from p in dc.Products
                where p.ProductName.Contains("Salmon")
                select p).FirstOrDefault();
    prod.ProductName="Trout#"+prod.ProductID.ToString();
    dc.SaveChanges(); 
}

and the code will work instantly!

this.Connection

Using LinqPad with OleDb, converting a datatable to Linq object, SQL queries in Linq

The following code snippet helps you to use LinqPad with OleDb. Add System.Data.OleDb from the System.Data assembly to the query properties, then paste the following code into Main():

var connStr="Provider=SQLOLEDB.1;"+this.Connection.ConnectionString; 

OleDbConnection conn = new OleDbConnection(connStr);
DataSet myDS = new DataSet();
conn.Open();

string sql = @"SELECT * from Customers";
OleDbDataAdapter adpt = new OleDbDataAdapter();
adpt.SelectCommand = new OleDbCommand(sql, conn); 
adpt.Fill(myDS);

myDS.Dump();

Now add a SqlServer connection to LinqPad and add the Northwind database in order to run this example.

N.B.: If you just want to get the database and server of the currently selected connection, you can use this code snippet:

void Main()
{
    var dc=this;
    var tgtSrv=dc.Connection.DataSource;
    var tgtDb=dc.Connection.ConnectionString.Split(';').Select(s=>s.Trim())
        .Where(x=>x.StartsWith("initial catalog", StringComparison.InvariantCultureIgnoreCase))
        .ToArray()[0].Split('=')[1];
    tgtSrv.Dump();
    tgtDb.Dump();
}

You can even convert myDS into Linq, the answers to the following question show how to do it: Nice examples of using .NET 4 dynamic keyword with Linq

One more example: Suppose your DBA gives you a SQL query and you want to analyze the results in LinqPad - of course in Linq, not in SQL. You can do the following:

void Main()
{
    var dc=this;
    
    // do the SQL query
    var cmd =
        "SELECT Orders.OrderID, Orders.CustomerID, Customers.CompanyName,"
        +"       Customers.Address, Customers.City"
        +" FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID";
    var results = dc.ExecuteQuery<OrderResult>(cmd);
    
    // just get the cities back, ordered ascending
    results.Select(x=>x.City).Distinct().OrderBy(x=>x).Dump();
}

class OrderResult
{   // put here all the fields you're returning from the SELECT
    public dynamic OrderID=null; 
    public dynamic CustomerID=null;
    public dynamic CompanyName=null;
    public dynamic Address=null;
    public dynamic City=null;
}

In this example the DBA's SELECT query is just "thrown into" the command text, and the results are filtered and ordered by City.
Of course, this is a simplified example, your DBA would probably give you a more complex script, but you're getting the idea: Add a supporting result class which contains all the fields from the SELECT clause, then you can directly use it.
You can even take the result from a stored procedure this way and use it in Linq. As you can see, in this example I don't care about the data type and use dynamic to express it.
So this is really about rapid programming to be able to analyze data quickly. You shouldn't do this in your real application for various reasons (SQL injection, because you can use EF from the beginning etc).

PanelManager

Draw graphic in LinqPad, part 1

To use the examples below, press F4 and add System.Windows.dll, System.Windows.Forms.dll, WindowsFormsIntegration.dll, PresentationCore.dll and PresentationFramework.dll to your LinqPad program and also add the namespace System.Windows.Shapes.

The 1st example simply draws a line:

var myLine = new Line();
myLine.Stroke = System.Windows.Media.Brushes.LightSteelBlue;
myLine.X1 = 1; myLine.X2 = 50;
myLine.Y1 = 1; myLine.Y2 = 50;
myLine.StrokeThickness = 2;
PanelManager.DisplayWpfElement(myLine, "Graphic");

The 2nd example shows how you can display graphic in LinqPad by using the PanelManager. Normally LinqPad only supports Wpf objects. This example uses System.Windows.Forms.Integration.WindowsFormsHost to make a PictureBox available (it was inspired by this):

// needs (F4): System.Windows.dll, System.Windows.Forms.dll, 
// WindowsFormsIntegration.dll, PresentationCore.dll, PresentationFramework.dll 
void Main()
{       
    var wfHost1 = new System.Windows.Forms.Integration.WindowsFormsHost();
    wfHost1.Height=175; wfHost1.Width=175; wfHost1.Name="Picturebox1";
    wfHost1.HorizontalAlignment=System.Windows.HorizontalAlignment.Left;
    wfHost1.VerticalAlignment=System.Windows.VerticalAlignment.Top;
    System.Windows.Forms.PictureBox pBox1 = new System.Windows.Forms.PictureBox();
    wfHost1.Child = pBox1;
    pBox1.Paint += new System.Windows.Forms.PaintEventHandler(picturebox1_Paint);
    PanelManager.StackWpfElement(wfHost1, "Picture");
} 

public string pathImg
{
    get { return System.IO.Path.Combine(@"C:\Users\Public\Pictures\Sample Pictures\", 
            "Tulips.jpg"); } 
}

// Define other methods and classes here
public void picturebox1_Paint(object sender, System.Windows.Forms.PaintEventArgs e)
{
    // https://stackoverflow.com/a/14143574/1016343
    System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(pathImg);
    System.Drawing.Point ulPoint = new System.Drawing.Point(0, 0);
    e.Graphics.DrawImage(bmp, ulPoint.X, ulPoint.Y, 175, 175);
}

This will create the following graphic (panel items "Graphic" and "Picture" are added by the examples above):

Showing_Graphic_in_LinqPad

If you want to display the images from the Northwind database, you can do the following:
Change the image file name to "NorthwindPics.jpg", then add the following code at the beginning of the 2nd example's Main() method:

var img = (from e in this.Employees select e).FirstOrDefault().Photo.ToArray();
using (FileStream fs1 = new FileStream(pathImg, FileMode.Create))
{
    const int offset=78;
    fs1.Write(img, offset, img.Length-offset);
    fs1.Close();
}

It will read the first record from the Employees table and display the picture.

Check out the following links to find out more:
Shapes and basic drawing in WPF
LinqPad custom visualizers

Note: You can achieve the same without the PanelManager as well, as the following example, which I saw here shows:

// using System.Drawing;
using (var image=new Bitmap(100, 100))
using (var gr = Graphics.FromImage(image))
{
    gr.FillRectangle(Brushes.Gold, 0, 0, 100, 100);
    gr.DrawEllipse(Pens.Blue, 5, 5, 90, 90);
    gr.Save();
    image.Dump();
}

It is using the .Dump() command to display it. You can invoke image.Dump() multiple times and it will append the image.

Windows Forms

Draw graphic in LinqPad, part 2

The following example, inspired by this post, is showing how to implement a function plotter in Linqpad using C#7:

void Main()
{
    fnPlotter(x1: -1, x2: 1, fn: (double x) => Math.Pow(x, 3)).Dump();
}

public static Bitmap fnPlotter(double x1=-3, double x2=3, double s=0.05, 
                                   double? ymin=null, double? ymax=null, 
                                   Func<double, double> fn = null, bool enable3D=true)
{
    ymin = ymin ?? x1; ymax = ymax ?? x2;
    
    dynamic fArrPair(double p_x1 = -3, double p_x2 = 3, double p_s = 0.01, 
                          Func<double, double> p_fn = null)
    {
        if (p_fn == null) p_fn = ((xf) => { return xf; }); // identity as default
        var xl = new List<double>(); var yl = new List<double>();
        for (var x = p_x1; x <= p_x2; x += p_s)
        {
            double? f = null;
            try { f = p_fn(x); }
            finally
            {
                if (f.HasValue) { xl.Add(x); yl.Add(f.Value); }
            }
        }
        return new { Xs = xl.ToArray(), Ys = yl.ToArray() };
    }

    var chrt = new Chart(); var ca = new ChartArea(); chrt.ChartAreas.Add(ca);
    ca.Area3DStyle.Enable3D = enable3D;
    ca.AxisX.Minimum = x1; ca.AxisX.Maximum = x2;   
    ca.AxisY.Minimum = ymin.Value; ca.AxisY.Maximum = ymax.Value;
        
    var sr = new Series(); chrt.Series.Add(sr);
    sr.ChartType = SeriesChartType.Spline; sr.Color = Color.Red;
    sr.MarkerColor = Color.Blue; sr.MarkerStyle = MarkerStyle.Circle;
    sr.MarkerSize = 2;
                
    var data = fArrPair(x1, x2, s, fn); sr.Points.DataBindXY(data.Xs, data.Ys); 
    var bm = new Bitmap(width: chrt.Width, height: chrt.Height);
    chrt.DrawToBitmap(bm, chrt.Bounds); return bm;
}

It is using the capability of LinqPad to display Windows forms in the results panel.
Example
Add references (press F4):
System.Drawing.dll, System.Windows.Forms.dll, System.Windows.Forms.DataVisualization.dll
and add all namespaces from these assemblies.

Additional hints / further reading:

Want to use LinqPad in Visual Studio? Here's how you can do that.
Need to have LinqPad as a "Portable app"? Read here how to do that.
Joe's website for LinqPad is an excellent source. Inside LinqPad, Help -> What's New gives you hints about new functions and methods. The LinqPad Forum also contains helpful hints.
Also helpful: This article about Linq(Pad) debugging.
Use lprun.exe for running LINQ queries in your batch scripts. Read this article for more details. For example:
echo Customers.Take(100) > script.txt
lprun -lang=e -cxname=CompanyServer.CustomerDb script.txt
In this example, the query is a simple LINQ expression. Of course, you can prepare complex queries as well using -lang=program to activate the program mode.
You can write and store extension methods in the My Queries tab on the left hand side of LinqPad: The last item of the tree is named My Extensions; double click on it to open a file where you can write extensions that are available to all your queries. Put them into the public static class MyExtensions, and use the Main() method to include tests for your extensions.

Continued here...

LinqPad extension methods - Part 2 of 2

^{Go to part 1}

Reached the StackOverflow text limit of 30000 characters in my previous answer, but there are still more cool extensions in LinqPad. Some of them I'd like to mention:

Auto-Scrolling and other shortcuts

Automatically scroll to the end of the Results Window while a query is running
(using .Dump() statements):
Press Shift+Control+E to toggle (turn on or off)
Multiple entry points:
Press Alt+Shift+1 to run Main1(), Alt+Shift+2 to run Main2(), and so on.
Note that you still need void Main() as main entry point, the methods above are additional (optional) entry points.
Run xUnit tests:
Press Alt+Shift+T to run all the xUnit tests decorated with [Fact] or [Theory]
(as preparation you need to add xUnit support via Query -> Add xUnit test support menu)

Environment

This is not a LinqPad extension, but rather a .NET class, but since it is useful, I'll mention it anyway. You can get a lot of useful information you can use in your scripts such as :

Environment.UserDomainName.Dump();
Environment.MachineName.Dump();
Environment.UserName.Dump();
Environment.CurrentDirectory.Dump();
Environment.SystemDirectory.Dump();

N.B. For obtaining Domain\UserName I would use System.Security.Principal.WindowsIdentity.GetCurrent().Name
rather than Environment.UserDomainName+@"\"+Environment.UserName.

Write your own extensions in LinqPad

ListTables

Did you know you can write your own extensions in LinqPad, available in all queries? Here's how you can do it: In LinqPad, go to the "My Queries" tab on the left side, scroll down to the end until you see "My Extensions". Double click on it and it will open a special query window named My Extensions. What you write there will become available in all queries.

Now paste the following code into it, then save it with Ctrl+S:

My Extensions

void Main()
{
    // no code here, but Main() must exist
}

public static class MyExtensions
{
    /// <summary>
    /// This will list the tables of the connected database
    /// </summary>
    public static IOrderedEnumerable<string> ListTables(
                        this System.Data.Linq.DataContext dc, bool dumpIt = true)
    {
       var query = dc.Mapping.GetTables();
       var result = query.Select(t => t.TableName).OrderBy(o => o);
       if (dumpIt) result.Dump();
       return result;
    }
}

Joe (the author of LinqPad) kindly provided me this snippet - it shows how you can pass the data context to My Extensions.
Note: For LinqPad 6 or greater, you need to press F4 for query properties and tick "Reference LINQ-to-SQL assemblies" to make it work.

Use this extension the following way: Open a new C# query window in LinqPad (with Ctrl+N), then connect to a database of your choice, and type:

New query

void Main()
{
    this.ListTables();
}

Important: If you're not connected to a database, then the extension is not available and LinqPad will show an error. So, connect to a database first, then type this.ListTables();.

Note that IntelliSense will show the summary of the XML comment we typed in My Extensions. Once you run it, you will get a list of the tables of the current database.

Configuration file (appsettings.json) in LinqPad

Previously I have shown how to use MyExtensions. Now if you want either a global appsettings.json file or one per script, you can use the following extension:

public static class MyExtensions
{
    // needs: Microsoft.Extensions.Configuration.json, press F4 and add it as NUGET package
    public static IConfiguration AppSettings(string path = null)
    {
            IConfiguration config = null;
            var configFile = (path != null) ? path : Util.CurrentQueryPath.Replace(".linq", ".appsettings.json");
            if (System.IO.File.Exists(configFile))
            {
                var builder = new ConfigurationBuilder().AddJsonFile(configFile);
                config = builder.Build();
            }
            else
            {
                configFile.Dump("Not found");
            }
            return config;
    }
}

You can also store it directly in your C# program, but this way it's available per default and you need to do the NUGET loading only once.

Say you have written a LinqPad program "YourCSharpProgram.linq". Now you can provide configuration like

var config1 = MyExtensions.AppSettings();

or like

var config2 = MyExtensions.AppSettings("C:\MyGlobalSettings\appsettings.json");

The first option, config1, will expect the the settings beneath the file "YourCSharpProgram.linq" and appends "appsettings.json" to it, meaning that your settings have to be in "YourCSharpProgram.linq.appsettings.json" in the same folder as the program. The second option just uses the absolute path as specified.

If your settings file contains

{
    "AzureStorage": {
        "StorageConnectionString": "some connection string"
    }
}

you can access it like

var config = MyExtensions.AppSettings();
string connectionString = config.GetSection("AzureStorage").GetSection("StorageConnectionString").Value.ToString();
connectionString.Dump();

NOTE: A second way to use configuration is to place the absolute path of your JSON file in LinqPads F4 dialog. In LinqPad 5, that was better because there was a separate tab for the settings file (there it was AppConfig, because version 5 is for .NET, not for .NET core). You have to reference it just as you would do it with an assembly, and it's not obvious. So I prefer it as described above.

.Dump() - revisited

Extension for click action

Sometimes it is useful if you can click a button to perform an action on a particular row. You can do this by writing an Extension (which you could store in "My Extensions" as described earlier).

Example for click column

All of the methods below need to be inside of a static not nested extension class (e.g. public static class Ext { ... }). First, we need to have an action button; to create one in a simple way we're using this helper method:

public static LINQPad.Controls.Button ActionButton<TSource>(this TSource arg, 
    string text, System.Action<TSource> selector)
    => new LINQPad.Controls.Button(text, 
       (LINQPad.Controls.Button b) => { selector(arg); });

The function takes a source type and allows to create a Button labled with a text and a selector (we'll see what that is in a minute).

With that, we can define a DumpAction method as follows:

public static void DumpAction<T>(this IEnumerable<T> lst, Action<T> selector,
                    string description = "", string clickText = "Click me")
{
    lst.Select(val => new
    {
        val, click = val.ActionButton<T>(clickText, selector)
    }).Dump(description);
}

This appends a column with buttons inside that you can click. You can use it as follows:

// list to display
var myList = new List<string>()
{
    "Apple", "Pie"
};

All you have to do is:

myList.DumpAction((x) => myAction(x), "Click Extension");

Which will do essentially the same as you know from .Dump() method. Now all it takes is to define a method myAction that is being called:

void myAction(string payload)
{
    payload.Dump("myAction");
}

This method will be called if you click the button passing the string value of the list item to it.

Note: You can do the same for Dictionaries, and the extension above already works for them, but it is taking a lot of space in the results window for each row - to solve that, you can define a second extension method:

public static void DumpActionDict<K, V>(this IEnumerable<KeyValuePair<K, V>> lst, 
                    Action<K> selector,
                    string description = "", string clickText = "Click me")
                    
{
    lst.Select(val => new
    {
        val.Key, val.Value, click = val.Key.ActionButton<K>(clickText, selector)
    }).Dump(description);
}

Use it like:

var myDict = new Dictionary<string, string>()
{
    ["a"] = "x",
    ["b"] = "y"
};

myDict.DumpActionDict((x) => myAction(x), "Extension for dict");

Note: It is also possible to format the text inside the button differently, take a look at this example:

public static LINQPad.Controls.Button ActionButton<TSource>(this TSource arg, string text,
    System.Action<TSource> selector)
{
    var btn = new LINQPad.Controls.Button(onClick: (LINQPad.Controls.Button b) => { selector(arg); });
    btn.HtmlElement.InnerHtml = $"<small>{text}</small>";
    return btn;
}

That will make the button text smaller. You can use any HTML element(s).

Coloring your dump with Util.HighlightIf

You can create colored dumps by using Util.HighlightIf(condition, object) or Util.HighlightIf(condition, htmlcolor, object).

The following example, taken from LinqPad's release notes and colored it a bit more shows how:

void Main()
{
    (from file in new DirectoryInfo(Util.LINQPadFolder).GetFiles()
    select 
        Util.HighlightIf(file.Extension == ".txt", "lightblue",
        Util.HighlightIf(file.Extension == ".json" || file.Extension == ".xml", "lightcyan",
        Util.HighlightIf(file.Extension == ".cmd" || file.Extension == ".bat", "lightyellow",
        Util.HighlightIf(file.Extension == ".dll", "lightgreen",  
        Util.HighlightIf(file.Extension == ".exe",    // Highlight the entire row if the file is an executable.
                         new {file.Name, 
                              Length=Util.HighlightIf(file.Length>999999,"orange",file.Length) ,
                              LastWriteDate=DateTime.Today.Date.ToString("yyyy-MM-dd")}
        )))))).Dump();
}

Now, what does it do? It colors the cells based on

file extension. The file extensions .bat,.txt, .json, .cmd, .dll, .xml and .exe have different colors for each of them (some share the same color).
file size. If the size exceeds 999999 bytes, its cell is colored in orange.

This will create a dump like:

colordump

Updating a message inline

Sometimes it is useful to overwrite the text you dumped rather than putting it into a new line, for example if you're performing a long-running query and want to show its progress etc (see also ProgressBar below). This can be done by using a DumpContainer, you can use it as shown in the

Example 1:

void Main()
{
   var dc = new DumpContainer("Doing something ... ").Dump("Some Action");
   System.Threading.Thread.Sleep(3000); // wait 3 seconds
   dc.Content += "Done.";
}

DumpContainerAnimation

Note that for some more complex objects, you might have to use dc.UpdateContent(obj); rather than dc.Content=....

Example 2:

void Main()
{
    var dc = new DumpContainer().Dump("Some Action");
    for (int i = 10; i >= 0; i--)
    {
        dc.UpdateContent($"Countdown: {i}");
        System.Threading.Thread.Sleep(250);
    };
    dc.UpdateContent("Ready for take off!");
}

Showing progress - Util.ProgressBar

Showing the progress can also be done by using a ProgressBar as follows:

Example:

void Main()
{
    var prog = new Util.ProgressBar("Processing").Dump();
    for (int i = 0; i < 101; i++)
    {
       Thread.Sleep(50); prog.Percent = i;
    }
    prog.Caption = "Done";
}

This is similar to the dump example before, but this time showing a nice progress bar animation.

enter image description here

JavaScript functions (using `.Dump()`)

Since version 5.42 beta of LinqPad you can embed JavaScript functions and call them directly from your C# code. Although this has some limitations (compared with JSFiddle), it is a nice way to quickly test some JavaScript code in LinqPad.

Example:

void Main()
{
    // JavaScript inside C#
    var literal = new LINQPad.Controls.Literal("script",
    @"function jsFoo(x) { 
        alert('jsFoo got parameter: ' + x); 
        var a = ['x', 'y', 'z']; external.log('Fetched \'' + a.pop() + '\' from Stack');
        external.log('message from C#: \'' + x + '\''); 
    }"); 
    // render & invoke
    literal.Dump().HtmlElement.InvokeScript(true, "jsFoo", "testparam");
}

In this example, a function jsFoo with one parameter is prepared and stored in the variable literal. Then, it is rendered and called via .Dump().HtmlElement.InvokeScript(...), passing the parameter testparam.

The JavaScript function uses external.Log(...) to output text in LinqPad's output windows, and alert(...) to display a popup message.

You can simplify this by adding the following extension class/methods:

public static class ScriptExtension
{
    public static object RunJavaScript(this LINQPad.Controls.Literal literal, 
                                       string jsFunction, params object[] p)
    {
        return literal.Dump().HtmlElement.InvokeScript(true, jsFunction, p);
    }
    
    public static LINQPad.Controls.Literal CreateJavaScript(string jsFunction)
    {
        return new LINQPad.Controls.Literal("script", jsFunction);
    }
}

Then you can call the previous example as follows:

    // JavaScript inside C#
    var literal = ScriptExtension.CreateJavaScript(
    @"function jsFoo(x) { 
        alert('jsFoo got parameter: ' + x); 
        var a = ['x', 'y', 'z']; external.log('Fetched \'' + a.pop() + '\' from Stack');
        external.log('message from C#: \'' + x + '\''); 
    }"); 

    // render & invoke
    literal.RunJavaScript("jsFoo", "testparam");

That has the same effect, but is easier to read (if you intend to do more JavaScript ;-) ).

Another option, if you like Lambda expressions and you don't like to specify the function name as string each time you're calling it, you can do:

var jsFoo = ScriptExtension.CreateJavaScript(
            @"function jsFoo(x) { ...  }"); 
ScriptExtension.RunJavaScript(() => jsFoo, "testparam");

provided you've added the helper function

public static object RunJavaScript(Expression<Func<LINQPad.Controls.Literal>> expr,  
                                   params object[] p)
{
    LINQPad.Controls.Literal exprValue = expr.Compile()();
    string jsFunction = ((MemberExpression)expr.Body).Member.Name;
    return exprValue.Dump().HtmlElement.InvokeScript(true, jsFunction, p);
}

to the class ScriptExtension. This will resolve the variable name you used (here jsFoo) which happens to be the same name as the JavaScript function itself (Note how the lambda expression is used to resolve the variable name, this cannot be done by using nameof(paramName) inside the function).

xUnit - Unit testing with LinqPad

Did you know you can write unit tests in LinqPad? For example, you can use the xUnit framework. For version 5 of LinqPad, it is available through LinqPad's NUGET support - via F4 - in the dialog click Add NUGET..... And since version 6 of LinqPad, it is built-in (Menu Query -> Add XUnit test support). Here's a step-by-step description how to use xUnit with LinqPad V5, V6 or V7.

Example 1:

[Fact] void TestDb1()
{
    var ctx = this; // "this" represents the database context
    Assert.True(ctx.Categories.Any(), "No categories");
    string.Join(", ", ctx.Categories.Select(s => s.CategoryName).ToList()).Dump("Categories");
    Assert.True(ctx.Products.Any(), "No Products");
    string.Join(", ", ctx.Products.Select(s => s.ProductName).ToList()).Dump("Products");   
}

This example needs a Northwind sample database (set up as Linq to SQL or as Entity Framework Core Connection), assigned to the query, and XUnit Test support added (select Query -> Add XUnit test support in LinqPad). It will print all categories and products to the Results window. The test fails, if there are no categories or products in the database.

Example 2:

// for MemberData
public static IEnumerable<object[]> GetNumbers()
{
    yield return new object[] { 5, 1, 3, 9 };  // 1st row: a, b, c, sum (succeeds)
    yield return new object[] { 7, 0, 5, 13 }; // 2nd row: a, b, c, sum (fails)
}

[Theory] 
[MemberData(nameof(GetNumbers))] // easier to handle than [ClassData(typeof(MyClass))]
void Test_Xunit(int a, int b, int c, int expectedSum) 
     => Assert.Equal (expectedSum, a + b + c);

This example uses a [Theory] with the member function GetNumbers() to fill in the parameters a, b, c and expectedSum and gives you more flexibility than a [Fact]. A [Fact] cannot have any parameters. The test fails if the expected sum is not equal to the sum of a+b+c. This is the case for the 2nd row: The assert will output: (a: 7, b: 0, c: 5, expectedSum: 13).

Note: You can also use InlineData (e.g. [InlineData(1,2,3,6)]) multiple times for a theory passing the values directly, and you can even combine it with the MemberData attribute. It will add all values to the test and execute it.

Multiple database support

The paid version of LinqPad (LinqPad 6 Premium) supports multiple databases in a query.

Below I am describing the steps for the database properies you get if you select "Linq to SQL (Optimized for SQL Server)".

Either create a new connection or open up an existing one. Open the Database Properties, select one database (don't use "Display all in a TreeView)

database properties

and then tick-mark "Include Additional Databases" - that will bring up another dialog where you can add multiple databases:

additional databases

Click Pick from List... and you can choose + select another database. When you are done, click Close to close the additional dialog and then Ok to close the database properties.

Databases selected there are "secondary" contexts (listed with the database name in "this" UserQuery), the first database (which you selected under "Specify new or existing database") is a "primary" context (which means, the tables appear directly in "this" UserQuery).

In the connections window, this will be shown as

".\MyInstance\AdventureWorks2017 + AdventureWorks2017 + Northwind"

In the code below I am using "AdventureWorks2017" as primary context and "AdventureWorks2017" and "Northwind" as secondary contexts.

Prepared like this, you can do:

public UserQuery ctx => this; // context
void Main()
{
 // "primary database"
 ctx.Products.Select(s => new {s.ProductID, Name=s.Name}).Take(3).Dump("AdventureWorks");

 // "secondary" databases
 var aw = ctx.AdventureWorks2017;
 var nw = ctx.Northwind;

 nw.Products.Select(s => new {s.ProductID, Name=s.ProductName}).Take(3).Dump("Northwind");
 aw.Products.Select(s => new {s.ProductID, Name=s.Name}).Take(3).Dump("AdventureWorks");
}

Both sample databases used in this example are from Microsoft, can be downloaded free, and they both have a Products table, but with different properties / fields: You can see that I've renamed the ProductName / Name so it appears in all queries as Name.

The program will give you the result:

result

Download links: AdventureWorks, Northwind, LinqPad

LINQPad defines two extension methods (in LINQPad.Extensions), namely Dump() and Disassemble(). Dump() writes to the output window using LINQPad's output formatter and is overloaded to let you specify a heading:

typeof (int).Assembly.Dump ();
typeof (int).Assembly.Dump ("mscorlib");

You can also specify a maximum recursion depth to override the default of 5 levels:

typeof (int).Assembly.Dump (1);              // Dump just one level deep
typeof (int).Assembly.Dump (7);              // Dump 7 levels deep
typeof (int).Assembly.Dump ("mscorlib", 7);  // Dump 7 levels deep with heading

Disassemble() disassembles any method to IL, returning the output in a string:

typeof (Uri).GetMethod ("GetHashCode").Disassemble().Dump();

In addition to those two extension methods, there are some useful static methods in LINQPad.Util. These are documented in autocompletion, and include:

Cmd - executes a shell command or external program
CreateXhtmlWriter - creates a text writer that uses LINQPad's Dump() formatter
SqlOutputWriter - returns the text writer that writes to the SQL output window
GetMyQueries, GetSamples - returns a collection of objects representing your saved queries / samples (for an example, execute a search using Edit | Search All)
Highlight - wraps an object so that it will highlight in yellow when Dumped
HorizontalRun - lets you Dump a series of objects on the same line

LINQPad also provides the HyperLinq class. This has two purposes: the first is to display ordinary hyperlinks:

new Hyperlinq ("www.linqpad.net").Dump();
new Hyperlinq ("www.linqpad.net", "Web site").Dump();
new Hyperlinq ("mailto:[email protected]", "Email").Dump();

You can combine this with Util.HorizontalRun:

Util.HorizontalRun (true,
  "Check out",
   new Hyperlinq ("http://stackoverflow.com", "this site"),
  "for answers to programming questions.").Dump();

Result:

Check out this site for answers to programming questions.

The second purpose of HyperLinq is to dynamically build queries:

// Dynamically build simple expression:
new Hyperlinq (QueryLanguage.Expression, "123 * 234").Dump();

// Dynamically build query:
new Hyperlinq (QueryLanguage.Expression, @"from c in Customers
where c.Name.Length > 3
select c.Name", "Click to run!").Dump();

You can also write your own extension methods in LINQPad. Go to 'My Queries' and click the query called 'My Extensions'. Any types/methods that define here are accessible to all queries:

void Main()
{
  "hello".Pascal().Dump();
}

public static class MyExtensions
{
  public static string Pascal (this string s)
  {
    return char.ToLower (s[0]) + s.Substring(1);
  }
}

In 4.46(.02) new classes and methods have been introduced:

DumpContainer (class)
OnDemand (extension method)
Util.ProgressBar (class)

Additionally, the Hyperlinq class now supports an Action delegate that will be called when you click the link, allowing you to react to it in code and not just link to external webpages.

DumpContainer is a class that adds a block into the output window that can have its contents replaced.

NOTE! Remember to .Dump() the DumpContainer itself in the appropriate spot.

To use:

var dc = new DumpContainer();
dc.Content = "Test";
// further down in the code
dc.Content = "Another test";

OnDemand is an extension method that will not output the contents of its parameter to the output window, but instead add a clickable link, that when clicked will replace the link with the .Dump()ed contents of the parameter. This is great for sometimes-needed data structures that is costly or takes up a lot of space.

NOTE! Remember to .Dump() the results of calling OnDemand in the appropriate spot.

To use it:

Customers.OnDemand("Customers").Dump(); // description is optional

Util.ProgressBar is a class that can show a graphical progressbar inside the output window, that can be changed as the code moves on.

NOTE! Remember to .Dump() the Util.ProgressBar object in the appropriate spot.