execute query from input .csv file, and append to it.

tanthiamhuat · June 2014

I can execute this query with 2 inputs (ITNO, MONO) as shown in below link.
http://postimg.org/image/44jjlp2zh/

However, I have more than 600 pairs of ITNO and MONO, and I do not want to execute the query more than 600 times.

I follow the instructions from
http://www.developertipoftheday.com/2012/10/read-csv-in-linqpad.html
to load in my pairs of ITNO and MONO inside LINQPAD.
as shown below:
http://postimg.org/image/k9lc4879v/
http://postimg.org/image/4c1w5cxe3/

How do I make use of this 642 loaded pairs of ITNO and MONO, and execute the above query to extract the FASTEST_TIME? I want the FASTEST_TIME to be on the third column, such that the first column is the ITNO and the second column is the MONO.

thank you.

Sorax · June 2014

I think PredicateBuilder would be most helpful. You can include it by pressing F4 and checking the "Include PredicateBuilder" box in the lower left corner.

This is a sketch of what I think would be most efficient and accomplish your goal. Of course it hasn't actually been run so please excuse any typos. Let me know any questions.

var csvData = 
MyExtensions.ReadFrom(@"C:\Sample.csv").Skip(1)
.Select(row => 
{
    var columns = row.Split(',');
    return new
    {
        ITNO = columns[0],
        MONO = columns[1]
}).ToList();

var predicate = PredicateBuilder.False<PPL_TESTSYSTEMS_UTILISATION>();
csvData
.ForEach(a => 
    predicate = predicate.Or(b => b.ITNO == a.ITNO && b.MONO == a.MONO)
);

PPL_TESTSYSTEMS_UTILISATION
.Where(predicate)
.Where(a => a.TESTINST != "DT_QC_SCAN" && a.TESTINST != "DT_TESTSTATUS" && a.TESTINST != "DT_EW")
.GroupBy(a => new { a.ITNO, a.MONO })
.Select(g => new
{
    g.Key.ITNO,
    g.Key.MONO,
    FASTEST_TIME = g.Min(a => a.TIME_FASTEST)
})
.Dump();

tanthiamhuat · June 2014

Hi Sorax,

thanks for your reply. However, I get the following error:
see http://postimg.org/image/fyujsrp67/

appreciate your help.

Sorax · June 2014

There's a difference in how csvData is initialized between your code and mine. ForEach is a method of class List<T>. In my code csvData is made a List by initializing it to the result of .ToList(). In your code the method is executed but the result is not captured.

This should point you in the right direction. Try resolving the error and let me know how it goes.

tanthiamhuat · June 2014

var csvData =
MyExtensions.ReadFrom(@C:\Sample.csv).Skip(1)
.Select(row =>
{
var columns = row.Split(',');
return new
{
ITNO = columns[0],
MONO = columns[1]
}).ToList();

But I could not execute your above code. What should I do so that I can execute all your code?

Sorax · June 2014

I'm not 100% sure what needs to be done on your machine to execute all the code. Like I said above, I'm sure they're some typos and I made some assumptions based on the available information.

I see in http://postimg.org/image/fyujsrp67/ that you fixed the type name of PplTestsystemsUtilisation. You'll probably have a couple more of those to do at least.

I do see a bug in my code you just quoted. Its anonymous type creation is missing its closing bracket. So it should be:

var csvData = 
MyExtensions.ReadFrom(@"C:\Sample.csv").Skip(1)
.Select(row => 
{
    var columns = row.Split(',');
    return new
    {
        ITNO = columns[0],
        MONO = columns[1]
    }
}).ToList();

Let me know if this now executes.

tanthiamhuat · July 2014

Hi Sorax,

your above code works, but the next part of the code does not, see error below:

http://postimg.org/image/j87jpneeh/

I really not sure what goes wrong. if you want to remote to my desktop, I will make it available.

thanks.

execute query from input .csv file, and append to it.

Comments