How to Select Multiple Fields from DataGridView using LINQ in C# WinForms-CodePudding

Technical Details:

Visual Studio 2017 (Community Edition)
LINQ
C# (WinForms)

I'm trying to use LINQ to query the data from a datagridview and display it in another datagridview (the original data source is a text file that is read by the datagridview at runtime). So far, I'm able to do that when selecting one field only.

For example:

Field 2 = HTTP_CODE
Field 5 = IP_ADDRESS
Field 9 = PAGE

Field 11 = USERNAME

         //all pages hits report
         var pageCountQuery = (dataGridViewIISDateTime.Rows.Cast<DataGridViewRow>()
             .Where(r => r.Cells[9].Value != null)
             .Select(r => r.Cells[9].Value)
             .GroupBy(pg => pg)
                 .OrderByDescending(pg => pg.Count())
                 .Select(g => new { PAGE = g.Key, HITS = g.Count() })).ToList();

         dataGridView1.DataSource = pageCountQuery;

and:

                //IPs generating traffic report
                var ipCountQuery = (dataGridViewIISDateTime.Rows.Cast<DataGridViewRow>()
                    .Where(r => r.Cells[5].Value != null)
                    .Select(r => r.Cells[5].Value)
                    .GroupBy(ip => ip)
                        .OrderByDescending(ip => ip.Count())
                        .Select(g => new { IP_ADDRESS = g.Key, VISITS = g.Count()})).ToList();

                dataGridView1.DataSource = ipCountQuery;

But when, I'm trying to select two or three fields using the same code as above, I start getting several warnings about syntax errors, variables being out of scope, etc.

The following is what I'm trying to do (SQL):

An example of selecting two fields:

//all pages hits and the IPs hitting them report
select page, ip, count(page)
from [LogFileName]
group by page, ip
order by count(page) desc

I tried the following (wrong approach):

//var pagesIPCountQuery = (dataGridViewIISDateTime.Rows.Cast<DataGridViewRow>()
                    //.Where(r => r.Cells[5].Value != null && r.Cells[9].Value != null)
                    //.Select(r => r.Cells[5].Value, r.Cells[9].Value)
                    //.GroupBy(ip => ip, page => page)
                    //.OrderByDescending(ip => ip.Count(), page => page.Count())
                    //.Select(g => new { IP = g.Key, Count = g.Count() }, { Page = })).ToList();

                    //dataGridView1.DataSource = pagesIPCountQuery;

An example of selecting three fields:

//500 errors per page and user report
SELECT username, page, http
FROM [LogFileName]
WHERE http = 500
GROUP BY username, page, http
ORDER BY count(http) DESC

I tried the following but I get a checkbox on the HTTP_CODE field and it doesn't filter the results by the error 500:

var error500Query = (dataGridViewIISDateTime.Rows.Cast<DataGridViewRow>()
                        .Where(r => r.Cells[9].Value != null && r.Cells[2].Value != null && r.Cells[11].Value != null)
                        .Select(r => new { Page = r.Cells[9].Value, HTTP = r.Cells[2].Value.Equals("500"), Username = r.Cells[11].Value })
                        .GroupBy(usernamepagehttp => usernamepagehttp)
                        .OrderByDescending(g => g.Count())
                        .Select(g => new { USERNAME = g.Key.Username, PAGE = g.Key.Page, HTTP_CODE = g.Key.HTTP, HITS = g.Count() })).ToList();

                    dataGridView1.DataSource = error500Query;

CodePudding user response：

Think I can translate this one:

//all pages hits and the IPs hitting them report
select page, ip, count(page)
from [LogFileName]
group by page, ip
order by count(page) desc

var pageCountQuery = (dataGridViewIISDateTime.Rows.Cast<DataGridViewRow>()
    .Where(r => r.Cells[9].Value != null && r.Cells[5].Value != null)
    .Select(r => new { Page = r.Cells[9].Value, IP = r.Cells[5].Value })
    .GroupBy(pageip => pageip)
    .OrderByDescending(g => g.Count())
    .Select(g => new { PAGE = g.Key.Page, IP = g.Key.IP, HITS = g.Count() })).ToList();

You haven't said what column HTTP code is in.. But the second SQL you've posted has syntax errors and would only really work in MySQL, and even then only if ONLY_FULL_GROUP_BY is deactivated