How to run a LinqPad script in a C # console application

adte

I want to run some LinqPad scripts in a C # console application.

I downloaded the LinqPad package from nuget. And I wrote a piece of code like this:
LINQPad.ObjectModel.QueryCompilation compiledScript = LINQPad.Util.Compile("C:\\temp\\testSkryptu.linq"); LINQPad.ObjectModel.QueryExecuter exec = compiledScript.Run(LINQPad.QueryResultFormat.Text); await exec.WaitAsync(); var rr = exec.AsString();

Each of the scripts that are run returns a list of data to the console application. The above code, however, does not return this data to me as a collection, but I can extract it as a string.
Sample LinqPad script looks like this:
`List Main()
{
using (var connection = new SqlConnection("Data Source=.;Initial Catalog=sezam;Integrated Security=SSPI; Application Name=SezamServicehost_DEV;TImeout=9999;"))
{
connection.Open();

    var sql =
        $@"select m.ProjectNumber, m.VipCustomerNumber, m.Id as MessageId, m.OrderCount as MessageQuantity, ISNULL(p.ErpCode, '-') as ErpCode, ISNULL(s.Name, '-') as Name, 
            ISNULL(o.OrderNumber, '-') as OrderNumber, ISNULL(i.Quantity, 0) as Quantity, ISNULL(i.Price, 0) as Price, ISNULL(u.Shortcut, '-') as PM, ISNULL(o.ErpOrderStatus, '-') as ErpOrderStatus,
            u2.Shortcut as POS, m.OrderDate as Dedline, o.CreationDate, o.OrderDate,               
            case
              when m.OrderAnswerOrderIsRejected = 1 Then (case when o.OrderNumber is not null Then 'Błąd' else 'Odrzucone' end)
              when (m.AnswerDate is null and o.OrderNumber is null) or (o.ErpOrderStatus = 2 or ErpOrderStatus = 3) Then 'Nowe'
              when (m.AnswerDate is null and o.OrderNumber is not null) Then 'Błąd'                
              when (m.AnswerDate is not null and o.OrderNumber is not null) and (o.ErpOrderStatus = 0 or o.ErpOrderStatus = 1) Then  (case when i.Quantity < m.OrderCount Then 'Możliwy brak' else 'Zamówione' end)
              when (m.AnswerDate is not null and o.OrderNumber is null) Then 'Potwierdzone - nie zamówione'                  
              when (m.AnswerDate is not null and o.OrderNumber is not null and o.ErpOrderStatus is null) Then 'Potwierdzone - nie zamówione'
              when (m.AnswerDate is not null and o.OrderNumber is not null) and ErpOrderStatus = 4 and (w.Quantity - w.AllocatedQuantity) >= 0 Then 'Zamknięte'
              when ((m.AnswerDate is not null and o.OrderNumber is not null) and ErpOrderStatus = 4 and (w.Quantity - w.AllocatedQuantity) < 0) or (i.Quantity < m.OrderCount) Then 'Możliwy brak'
            end as Status
            from DbMessage m 
              left join DbSupplierOrderItem i on ISNULL(m.ProjectNumber, '') = ISNULL(i.ProjectNumber, '') and ISNULL(m.VipCustomerNumber, '') = ISNULL(i.VipCustomerNumber, '') and m.Product = i.Product
              left join DbProduct p on m.Product = p.Id
              left join DbSupplierOrder o on i.SupplierOrder = o.Id
              left join DbUser u on o.Owner = u.Id
              left join DbSupplier s on o.Supplier = s.Id
              left join DbWarehouseStock w on p.Id = w.Product and w.Warehouse = 8093696
              left join DbUser u2 on m.Sender = u2.Id
            where (m.ProjectNumber is not null and m.ProjectNumber != '') or (m.VipCustomerNumber is not null and m.VipCustomerNumber != '')
            order by m.ProjectNumber, m.VipCustomerNumber";

    var data = connection.Query(sql).ToList();
    return data;
}

}`

What the script and C # code should look like so that I can get the list of objects from the scripts and not the string to the application

nescafe

Why don't you run the code directly in your C# Console application? You would not be using any of the extension methods of LINQPad or the generated data context anyway.

adte

The application will be used to generate various reports. Each LinqPad script is a separate report. Dlateog I don't want to execute code directly in the application.
In the script itself, I don't need to use any LinqPad extension methods.

nescafe

var executor = LINQPad.Util.Run(@"C:\temp\testSkryptu.linq", QueryResultFormat.Text);
var result = await executor.ReturnValueAsync as List<string>;

I must add, this doesn't work for List<object> or List<dynamic>, it's stuck on await ReturnValueAsync (or ReturnValue).

nescafe

Perhaps you could take a look @JoeAlbahari ?

Problem: Executor does not return value from Main method with List<object> output type.

Script: http://share.linqpad.net/et728c.linq

adte

Thanks helped.

nescafe

@JoeAlbahari while you're at it, could you also take a look at this example of async wait and sync getresult?

Script: http://share.linqpad.net/4w6ecq.linq

JoeAlbahari

The example where you are returning List fails because the list contains anonymous types which are not serializable.

I've added an experimental feature to the latest LINQPad 6 beta build that works around this by serializing anonymous types to ExpandoObjects. This will allow you to Dump them and access their members via dynamic.

Note that this feature hasn't made it to LINQPad 5, although I've added a check such that it throws an exception rather than hanging.

@nescafe - I've fixed the issue you describe in the latest betas of both LINQPad 6 and LINQPad 5.