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.
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.
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();