Query Azure Table Storage with LINQPad6
Tags: azure, tablestorage, linqpad
Published: 2021-04-13 08:36:36

[LINQPad](https://www.linqpad.net/) is a world famous LINQ/C# playground which you can install locally (for Windows). There are bunch of out-of-box connectors for various services, including major cloud services, and 3rd party connectors which let you connect many data sources.

One of the connector I try this time is Azure Table storage and I show you how we can query table data.

# Prerequisites

- LINQPad (https://www.linqpad.net/)
- Azure subscription
- Storage account (Table)

# Azure Table Storage

[Azure Table Storage](https://docs.microsoft.com/en-us/azure/storage/tables/table-storage-overview) is a key/value storage which we can store data easily.

Let's create a table and add sample data.

1\. Go to [Azure Portal](https://portal.azure.com) and create a storage account, then go to storage explorer. 

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qff7pobfjhs0yuuxswkj.png)

2\. Right click **TABLES** and add new table.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mjn051s87yes7sgxwuef.png)

3\. Add some data. Or we can use [Azure Storage Explorer](https://azure.microsoft.com/en-us/features/storage-explorer/) to upload data from local computer.

4\. I added people data from [swapi](https://swapi.dev)

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g2kapaet18gaekjeqmb9.png)

# Connect to the table from LINQPad

As data is ready, let's connect and query data.

1\. Open LINQPad and click **Add connection**.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zj2ki7bfs31ryho1xmds.png)

2\. Click **View more drivers...**.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yuj8nxnly63wkg254e2a.png)

3\. Click **Install** to add the driver, then close. Click **Accept** if you are prompted to install modules.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wkm7gdr64su9kxit5qdf.png)

4\. Select installed driver and click **Next**.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uofzz2c5nwfyfmquai5w.png)
 
5\. Enter name and storage account key, then click **OK**.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m8elqrkqj5s6tn89yumb.png)

6\. Select connected data source from **Connection** dropdown.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4whmp5x71rgjqxd6yvdq.png)

That's all.

# Query data via LINQ

As name infers, LINQPad lets you query data by using LINQ. Let's try some.

### Query a couple of columns from the table.

```linq
from c in LINQPadDemoTable select new { c.name, c.birth_year}
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/iy1wxfvh56ljd934jan1.png)

### Filter data by a column

```linq
from c in LINQPadDemoTable 
where c.birth_year == "19BBY" 
select new { c.name, c.birth_year} 
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/blavu2udtbu8w66c7lo4.png)

# Query in C# way

We can also use C# expression.

### Query by using .WHERE .SELECT

```csharp
LINQPadDemoTable
	.Where(x => x.birth_year == "19BBY")
	.Select(x => new { x.name, x.birth_year})
```
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hu07bh362pkqj9qx5s9z.png)

### Parse Json string

We can also use power of NuGet for more complex scenario. The vechies column contains json array string like below.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/l7mcql36ybua5vacedqf.png)

We can use Json.NET C# library to parse it.

1\. Enter following expression.

```csharp
LINQPadDemoTable
	.Where(x => x.birth_year == "19BBY")
	.Select(x => new { x.name, x.birth_year, vehicles = JArray.Parse(x.vehicles) })
```

2\. As it cannot resolve JArray, it shows in red. Right click the pain and select **Nuget Package Manager**.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4p1dd0p1k06tduqtghnj.png)
 
3\. Click **Add to Query** button for Json.NET.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0nyogdw1arz5gnhi82k5.png)
 
4\. Once added, click **Add namespace** link.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g4te2urdhtkacn5k4xhb.png)
 
5\. Now we can execute the query.

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/i3j5blc2ieoicu5bstyo.png)
 
6\. Let's further digging into array object. Convert the results to List and query by SelectMany.

```csharp
LINQPadDemoTable
	.Where(x => x.birth_year == "19BBY").ToList()
	.Select(x => new { x.name, vehicles = JArray.Parse(x.vehicles) })
	.SelectMany(x => x.vehicles, (x, vehicle) => new { x.name, vehicle })
```

![image](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/q077vmfl6lo9v7t1xcda.png)
 
# C# statement/program

LINQPad let you write C# program as well, so if you are interested in this tool, please download and play with it.