LINQ to display count of records not in top x

Question

User-2012457684 posted

I am writing a logfile reader for our clients to use on their websites.   

To that end, I create a single list composed of the various files that they want to view. and then use LINQ to query the list and build the pages.   I will use LINQ to get the top x number of browsers  and then want to have all of the others included as others.   This is what I need help with.   I do not know how to get the count of the records that are not in the top x.    

 

Answer 1

User475983607 posted

It's very difficult to answer data questions without the actual data.   Basically the other records are those not in the TOP X.  Use a NOT IN or a JOIN to exclude the records NOT IN the TOP X.  See the standard TSQL docs.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15

Answer 2

User-2012457684 posted

This does not use a database.   If it did I would not need to use LINQ, and would know how to write in SQL.    In this case it is reading the log files using a streamreader and creating a list.  I then use LINQ to read the list.   here is my class

public class Logfile
{
    public string FileName { get; set; }
    public string date { get; set; }
    public string time { get; set; }
    public string ServerIP { get; set; }
    public string Method { get; set; }
    public string URI { get; set; }
    public string Query { get; set; }
    public string Port { get; set; }
    public string Username { get; set; }
    public string IP { get; set; }
    public string UserAgent { get; set; }
    public string Referrer { get; set; }
    public string Status { get; set; }
    public string SubStatus { get; set; }
    public string Win32Status { get; set; }
    public string TimeOnSite { get; set; }
    public string Browser { get; set; }



    public List<String> ListLogs(DateTime StartDate, DateTime EndDate)
    {
        List<String> logList = new List<String>();
        foreach (DateTime day in EachDay(StartDate, EndDate))
        {
            string d = day.Day.ToString("00");
            string m = day.Month.ToString("00");
            string yr = day.Year.ToString().Substring(2);
            string logfile = "u_ex" + yr + m + d + ".log";
            logList.Add(logfile);
        }
        return logList;
    }

    public List<Logfile> GetLogs(List<String> logList, string path)
    {
        List<Logfile> Files = null;
        var uaParser = Parser.GetDefault();
        Files = new List<Logfile>();
        foreach (string log in logList)
        {
            string newFile = path + @"\" + log;
            if (File.Exists(newFile))
            {
                //Logfile lf = new Logfile();
                using (StreamReader sr = new StreamReader(newFile))
                {
                    //the top 4 lines do not contain any log information so skip reading them
                    for (var i = 0; i < 4; i++)
                    {
                        sr.ReadLine();
                    }
                    while(sr.Peek() >= 0)
                    {
                        Logfile lf = new Logfile();
                        string line = sr.ReadLine();
                        var info = Regex.Split(line, @"\s+");
                        if (!info[0].StartsWith("#"))
                        {
                            lf.date = info[0];
                            lf.time = info[1];
                            lf.ServerIP = info[2];
                            lf.Method = info[3];
                            lf.URI = info[4];
                            lf.Query = info[5];
                            lf.Port = info[6];
                            lf.Username = info[7];
                            lf.IP = info[8];
                            lf.UserAgent = info[9];
                            lf.Referrer = info[10];
                            lf.Status = info[11];
                            lf.SubStatus = info[12];
                            lf.Win32Status = info[13];
                            lf.TimeOnSite = info[14];
                            ClientInfo c = uaParser.Parse(info[9]);
                            lf.Browser = c.UA.Family;
                            Files.Add(lf);
                        }
                    }
                }
            }
        }
        return Files;

    }

    private IEnumerable<DateTime> EachDay(DateTime from, DateTime thru)
    {
        for (var day = from.Date; day.Date <= thru.Date; day = day.AddDays(1))
        {
            yield return day;
        }
    }

}

public class Browsers
{
    public string Browser { get; set; }
    public int Count { get; set; }
}

then in my controller i simply do this where in this case x = 10 

string processID = Request.ServerVariables["INSTANCE_ID"].ToString();

public ActionResult Browsers(DateTime StartDate, DateTime EndDate)
{
    Logfile log = new Logfile();
    string path = @"c:\inetpub\logs\logfiles\W3SVC" + processID;
    if (!Directory.Exists(path))
    {
        return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
    }
    List<String> logs = log.ListLogs(StartDate, EndDate);
    List<Logfile> ListData = log.GetLogs(logs, path);

    var browsers = (from browser in ListData
                    group browser by browser.Browser into Agents
                    select new Browsers
                    {
                        Browser = Agents.Key,
                        Count = Agents.Count()
                    })
                    .OrderByDescending(x => x.Count)
                    .Take(10).ToList();
             
    return View(browsers);
}

What I am stuck on is getting the other browsers so when i build the report i can display all of the other browsers as a single nuumber.

Answer 3

User475983607 posted

What I am stuck on is getting the other browsers so when i build the report i can display all of the other browsers as a single nuumber.

Correct me if I'm wrong but the math is pretty simple.

Total - known browsers = Other browsers

As far as I can tell, you already have these values otherwise you could not get the count of known browsers.  

Answer 4

User-1330468790 posted

Hi mj1223,

 

You could select all records and count before take a certain number from the result.

It is a math problem as @mgebhard said. You could get count for all, "Total",  and you already know the number of records for displaying, "known browsers". The only thing you need to do is substraction.

 

More details, you could refer to below codes (Console App for .NET Core):

class Program
    {
        static void Main(string[] args)
        {
            // Simplified Example Data
            List<Logfile> ListData = new List<Logfile>
            {
                new Logfile { Browser= "Browser1",FileName= "FileName1", UserAgent= "UserAgent1"},
                new Logfile { Browser= "Browser1",FileName= "FileName2", UserAgent= "UserAgent2"},
                new Logfile { Browser= "Browser1",FileName= "FileName3", UserAgent= "UserAgent3"},
                new Logfile { Browser= "Browser1",FileName= "FileName4", UserAgent= "UserAgent4"},
                new Logfile { Browser= "Browser2",FileName= "FileName5", UserAgent= "UserAgent5"},
                new Logfile { Browser= "Browser3",FileName= "FileName6", UserAgent= "UserAgent6"},
                new Logfile { Browser= "Browser4",FileName= "FileName7", UserAgent= "UserAgent7"},
                new Logfile { Browser= "Browser5",FileName= "FileName8", UserAgent= "UserAgent8"},
                new Logfile { Browser= "Browser5",FileName= "FileName9", UserAgent= "UserAgent9"},
                new Logfile { Browser= "Browser5",FileName= "FileName10", UserAgent= "UserAgent10"},
                new Logfile { Browser= "Browser6",FileName= "FileName11", UserAgent= "UserAgent11"},
                new Logfile { Browser= "Browser7",FileName= "FileName12", UserAgent= "UserAgent12"},
                new Logfile { Browser= "Browser7",FileName= "FileName13", UserAgent= "UserAgent13"},
                new Logfile { Browser= "Browser8",FileName= "FileName14", UserAgent= "UserAgent14"},
                new Logfile { Browser= "Browser9",FileName= "FileName15", UserAgent= "UserAgent15"},
                new Logfile { Browser= "Browser10",FileName= "FileName16", UserAgent= "UserAgent16"},
                new Logfile { Browser= "Browser11",FileName= "FileName17", UserAgent= "UserAgent17"},
                new Logfile { Browser= "Browser12",FileName= "FileName18", UserAgent= "UserAgent18"},
                new Logfile { Browser= "Browser13",FileName= "FileName19", UserAgent= "UserAgent19"},
                new Logfile { Browser= "Browser13",FileName= "FileName20", UserAgent= "UserAgent20"}
            };

            // Take number
            int takeNum = 10;

            // Total records for browsers
            var TotalBrowsers = (from browser in ListData
                            group browser by browser.Browser into Agents
                            select new Browsers
                            {
                                Browser = Agents.Key,
                                Count = Agents.Count()
                            })
                   .OrderByDescending(x => x.Count).ToList();

            // Take a certain number of records
            var browsers = TotalBrowsers.Take(takeNum);

            // Others Count
            var othersCount = TotalBrowsers.Count() - takeNum;

           
            // Display known browsers
            foreach (var item in browsers)
            {
                Console.WriteLine(item.ToString());
            }

            // Display the count for others
            Console.WriteLine(String.Format("The count for others is: {0}", othersCount));

            Console.ReadLine();
        }
    }

    public class Browsers
    {
        public string Browser { get; set; }
        public int Count { get; set; }

        public override string ToString()
        {
            return String.Format("Browser: {0}, Count: {1}  \n",Browser, Count); 
        }
    }

    public class Logfile
    {
        public string FileName { get; set; }
        public string UserAgent { get; set; }
        public string Browser { get; set; }
    }

Result:

 

Hope this can help you.

Best regards,

Sean

Answer 5

User-2012457684 posted

Thank you for showing me that.