How can I run a SQL query in C# statement/program with Linqpad?

Question

How can I run a SQL query in C# statement or C# program with Linqpad?

Yes I have to mix SQL statements with Linq for compatibility reason. I use linqpad with postgres driver and these driver doesn't recognize the hstore of postgres. I already knows I can get these ignored column by using classic SQL.

Wrenn

Answer 1

You can use the ExecuteCommand on LinqPad to run SQL statements direct on C# Statement. You can execute SQL statements with parameters formatting command string with {0}, {1}, etc.

ExecuteCommand("DELETE FROM TableOne where PrimaryKey = {0}", primaryKey);

Siouxie

Note that you have to click the "Connection" dropdown above your LINQPad code and select a connection that you've previously set up in the Connections pane. Once you've done that, ExecuteCommand and ExecuteQuery are available as global static methods. – Ecosystem

It seems sometimes the autocomplete won't mention that ExecuteCommand() is available, but if you try to run the script, the autocomplete should be fixed after you try to run it and the other sql command methods should be visible – Oxtail

Answer 2

Have you tried setting up a connection in LINQPad? There is a driver available for download for PostgreSQL among others? Once you have set up the connection you can then run SQL or C#

Rectus

Yes I tried. The driver for PostgreSQL cannot return the hstore columns – Wrenn

Answer 3

I found part of the solution:

this.Command.CommandText = "select * from items LIMIT 50;";
var result = this.Command.ExecuteReader().Dump();

But the result variable is a IDataReader... not a collection. What is strange is the Dump() method properly display a grid.

Wrenn

Not sure whether it works with Postgres, but have you tried this.ExecuteQuery<Items>("select * from items LIMIT 50;").Dump() ? – Reduplicate

The problem with this solution is my item definition is not equal to my result. In linqpad my column with the hstore is ignored. My item entity does not contain the variable of the hstore column. – Wrenn

Answer 4

I have the same problem where I want to query an HStore field in a PostgreSQL table using only LINQ. Previously I switched to SQL in LINQPad and dealt with the problem that way. But LINQPad can cope quite well if you materialise the query using a .ToList() or something before doing the querying of the HStore field.

For example, I have a 'Survey' table where the AssetId field is a simple text field that 'describes' a relationship to another table, and then a Values HStore field with the results of the survey that could include several different fields. So if I want to look for all surveys for 'ROAD's with a 'number_lanes' value, then my LINQ query was:

AssetsSurveys.Where (a => a.AssetId.StartsWith("ROAD-")).ToList().Where(a => a.Values.ContainsKey("number_lanes"))

The .ToList() in the middle results in the HStore field being materialised as a Dictionary that can then be easily queried.

Rainstorm

Answer 5

You may use ExecuteQueryDynamic to run raw SQL, use {0}, {1} to pass parameters.

var SQL = @"Select * from Person where Id = {0} ";
ExecuteQueryDynamic(SQL, 1).Dump();

Supportable