Integrating LinqPad library from nuget into .net application

jamccleary

First of all, great product. I find myself using it more often the more comfortable I get with it Also great documentation on how to thread, I've learned a lot from that PDF.
Looking to use the library I added to my c# project to integrate LinqPad. I like the dynamic ability to execute text queries on the fly. I would like to use this to create LinqPad programs, store in DB as text and in workflow of processing data, execute against a data warehouse to generate a file (pipe delimited, wish LinqPad supported other delimiters other than comma )
I see that I can use the LPRun against a dos prompt, but I would then also need to store the queries (or generate) a query file for consumption, doable but not ideal.
I'm having trouble finding documentation on how to use the LinqPad NuGet library, looks like it is really geared towards a WinForms. The methods I look at are protected and prevent me from executing my generated query, confused how to add a connection string (the format to use), etc.
To recap, I want to use the LinqPad library, hosted from a c# application, passing parameters for what I wish to extract against a SQL DataWarehouse (preferably using Entity Framework).
Any direction anyone can give me?

Goyuix

You are corrent in exploring lprun.exe - that is a very good way to instrument headless operation in a data warehouse scenario like you described. You can certainly invoke it from your C# application by creating a new Process object as well. You should take a minute and familiarize yourself with the arguments you can pass to lprun.exe. It is pretty well documented with examples on the lprun.aspx page.

The queries you create in LINQPad are simple text files, complete with header information that contains the required settings for that query that includes the datasource, referenced assemblies and nuget packages that are referenced. You can quite easily import those into your database and then send the text to lprun.exe as part of your process. You can easily modify the text of the query prior to execution to do something like change the datasource details to connect to a different server.

Could you be more specific with the issues you are having with the NuGet integration? To take full advantage of it you will need an appropriate license otherwise the packages you can pull into your queries wil be fairly limited. The integration works similarly to NuGet in your Visual Studio - your are just loading and referencing assemblies so you can use their functionality in your queries.

jamccleary

I have a few issues....first one is the system I am sending to requires me to send a pipe (|) not CSV...I believe this would require me to create a csv file through LPRun and then open it up in code to convert it.
The second issue I have is to use LPRun, I have to take the text out of the database and write it to a file to allow LPRun to execute (and yes I am familiar with the arguments, I do have a working solution). The creation of the file in itself is not bad, but since my query is a parameterized LinqPad query, I have to write one per call and I will either be creating a lot of files to prevent this multithreaded application from a race condition on a single file, the file will be identical in all situations...the reason I am pulling it from the database is because I want to create extracts for date ranges which decouples releases of my software with theirs.
As far as why I cannot execute a query from the NuGet library I will have to create some example code....basically I believe I was able to create a query, but could not instantiate anything that would execute it as the access modifiers prevent me from doing it (maybe I have to go through the LinqPad.UI namespaces, but that seems like I am simulating a windforms app, just to execute code). Maybe I'm missing something.

jamccleary

BTW, I did use the LinqPadHost from NUGet and got something to work, a little hacky, and seems to be a little more tied to the database structure than I want, but I got it to work. There was a bug in his source that I worked through and had to create my on CSV formatter, yada, yada yada

jamccleary

Hopefully you will get my gist from this....
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace LinqPadIntegration
{
public class LinqPad_Library
{
private string _queryText;
private static LinqPad_Library _instance;
private static object _instanceLock = new object();
//Script to create database table entry for retrieval and seed it with some test data
/*
USE [poc]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[LinqPadExample](
[TransactionID] [int] IDENTITY(1,1) NOT NULL,
[CustomerID] [int] NOT NULL,
[SaleAmount] [money] NOT NULL,
[SaleDate] [date] NOT NULL,
[Status] [nvarchar](50) NULL,
CONSTRAINT [PK_LinqPadExample] PRIMARY KEY CLUSTERED
(
[TransactionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


USE [poc]
GO

INSERT INTO [dbo].[LinqPadExample]
([CustomerID]
,[SaleAmount]
,[SaleDate]
,[Status])
VALUES
(1
,200.55
,'2015-06-04'
,'Open')
GO
INSERT INTO [dbo].[LinqPadExample]
([CustomerID]
,[SaleAmount]
,[SaleDate]
,[Status])
VALUES
(1
,55.66
,'2015-06-06'
,'Closed')
GO
INSERT INTO [dbo].[LinqPadExample]
([CustomerID]
,[SaleAmount]
,[SaleDate]
,[Status])
VALUES
(2
,22.11
,'2015-06-06'
,'Closed')
GO
INSERT INTO [dbo].[LinqPadExample]
([CustomerID]
,[SaleAmount]
,[SaleDate]
,[Status])
VALUES
(2
,0.55
,'2015-06-08'
,'Void')
GO
INSERT INTO [dbo].[LinqPadExample]
([CustomerID]
,[SaleAmount]
,[SaleDate]
,[Status])
VALUES
(1
,99.99
,'2015-06-09'
,'Open')
GO */


public static LinqPad_Library Instance()
{
if (_instance == null)
{
lock (_instanceLock)
{
if (_instance == null)
{
_instance = new LinqPad_Library();
}
}
}
return _instance;
}

private LinqPad_Library()
{
StringBuilder sb = new StringBuilder()
.AppendLine("void Main(string[] args)")
.AppendLine("{")
.AppendLine("#if !CMD")
.AppendLine(" args = new[] { \"2\"};")
.AppendLine("#endif")
.AppendLine(" var header = LinqPadExamples.GroupBy(r=> r.CustomerID)")
.AppendLine(" .Select (grp => new ")
.AppendLine(" {")
.AppendLine(" ID = grp.Key, ")
.AppendLine(" HowMany = grp.Count()")
.AppendLine(" })")
.AppendLine(" .Where(r => r.ID == int.Parse(args[0]))")
.AppendLine(" ;")
.AppendLine(" header.Dump();")
.AppendLine("")
.AppendLine("var detail =")
.AppendLine("from myresult in LinqPadExamples where myresult.CustomerID == int.Parse(args[0])")
.AppendLine("select new ")
.AppendLine("{TransType = \"M\"")
.AppendLine(",TransactionID = myresult.TransactionID")
.AppendLine(",SaleAmount = string.Format(\"{0:N2}\", myresult.SaleAmount)")
.AppendLine(",EffectiveDate = myresult.SaleDate.ToString()")
.AppendLine(",SomevalueIndicator=\"MyInd\"")
.AppendLine(",TransactionType = myresult.Status")
.AppendLine("};")
.AppendLine("detail.Dump();")
.AppendLine("}");

_queryText = sb.ToString();

}
///

/// This code will be called on new Publish Event from an ESB, non-deterministic when runs and needs to be threadsafe.
/// Normally the _queryString text will be returned from a proc call to a database configuration for the file recipient
///

///
public void Run(int customerID)
{
//Not sure what the connectionstring format looks like, I've had to create one via UI and then use "..LinqPadExamples"
var connString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqPadConnectionString"].ConnectionString;
//MappingSource ms = MappingSource.//
LINQPad.DataContextBase b = new LINQPad.DataContextBase(connString);

//I want a delimited Format, just not a comma
var myFormat = LINQPad.QueryResultFormat.Csv;

//How do I create a QueryControl item?...is this even the right item..this is in the UI namespace which does not seem right
LINQPad.UI.QueryControl qc = new LINQPad.UI.QueryControl();
//How do I give it a connection string?
//How do I pass it a the argument?
bool success = qc.ExecuteQuery(_queryText, (int)LINQPad.QueryLanguage.Program, true);
//How do I get to the results?
}

}
}

JoeAlbahari

So you want to reference LINQPad from a Visual Studio project, and ask it to execute scripts?

If so, you can do this simply by referencing LINQPad.exe from your VS project and calling LINQPad.Util.Run or LINQPad.Util.Compile.

You don't need to download LINQPad from NuGet - just reference the LINQPad.exe that ships with LINQPad and it will work fine. You might find it best to download the Any-CPU build of LINQPad because you can reference it from both 32-bit and 64-bit applications.

If you plan to repeatedly execute a script, it's more efficient to first call Util.Compile, because you can then call Util.Run more than once without additional overhead.

You can pass in values to the script, and get return data out either via the result stream (CSV, HTML, Text/JSON) or as strongly typed data via the query's return value. If you used strongly typed data, the same types must be available to both the query being executed and your VS project, and must be serializable.

To give a complete example, suppose you want to execute the following LINQPad script which you've saved to c:\temp\query.linq:

string Main (string message)
{
   "testing".Dump();
   return message.ToUpper();
}

This is what your C# program in Visual Studio will look like:

using System;
using LINQPad;
    
namespace ConsoleApplication1
{
    class Program
    {
        static void Main (string [] args)
        {
            using (var query = Util.Compile (@"c:\temp\query.linq"))
            {
                var results = query.Run (QueryResultFormat.Text, "hello world");
                Console.WriteLine (results.AsString ());   // prints "testing"
                Console.WriteLine (results.ReturnValue);  // prints "HELLO WORLD"
            }
        }
    }
}

Notice that calling .AsString() on the results returns the results as a string, in whichever format you requested (CSV, HTML, Text/JSON, etc).

Calling .ReturnValue gives you an object that you can cast to a type such a string, DateTime, DataSet, or any custom serializable type in an assembly that's referenced both by your query and by your VS project.

jamccleary

Both signatures (LINQPad.Util.Run or LINQPad.Util.Compile) require a file. What I am getting at, is I have an active script/text in a database (start and end date driven), I pull that value and would prefer not to write it to a file to execute. I would like the signature to take a string or stream instead of having to create files on the fly and either risk a race condition or create almost 3 thousand files a night which are almost always 4 different queries.
How do I pass a string to something in LinqPad for execution? With arguments.

jamccleary

LinkPad.Util.Run("void main (string[] args){var header = LinqPadExamples.GroupBy....}", args[], connectionstring, [LinqPadProgram/Statement...]).
This is what I was hoping to see.

jamccleary

maybe output type, or some sort of configuration object to be passed with it, maybe listen to events of compilefailure, executionfailures.... stuff like that.

jamccleary

OK, I think I can work with using a filename rather than a string/stream/textreader as input.
Any thought about exposing the CSV separator character to something other than the hardcoded comma?