C# Reading and Summarizing Text File with LINQ

Question

I've read MANY different solutions for the separate functions of LINQ that, when put together would solve my issue. My problem is that I'm still trying to wrap my head about how to put LINQ statements together correctly. I can't seem to get the syntax right, or it comes up mish-mash of info and not quite what I want.

I apologize ahead of time if half of this seems like a duplicate. My question is more specific than just reading the file. I'd like it all to be in the same query.

To the point though..

I am reading in a text file with semi-colon separated columns of data.

An example would be:

US;Fort Worth;TX;Tarrant;76101
US;Fort Worth;TX;Tarrant;76103
US;Fort Worth;TX;Tarrant;76105
US;Burleson;TX;Tarrant;76097
US;Newark;TX;Tarrant;76071
US;Fort Worth;TX;Tarrant;76103
US;Fort Worth;TX;Tarrant;76105

Here is what I have so far:

var items = (from c in (from line in File.ReadAllLines(myFile)
    let columns = line.Split(';')
    where columns[0] == "US"
    select new
    {
        City = columns[1].Trim(),
        State = columns[2].Trim(),
        County = columns[3].Trim(),
        ZipCode = columns[4].Trim()
    })
    select c);

That works fine for reading the file. But my issue after that is I don't want the raw data. I want a summary.

Specifically I need the count of the number of occurrences of the City,State combination, and the count of how many times the ZIP code appears.

I'm eventually going to make a tree view out of it. My goal is to have it laid out somewhat like this:

- Fort Worth,TX (5)
       -  76101 (1)
       -  76103 (2)
       -  76105 (2)  

- Burleson,TX (1)
       - 76097 (1)

- Newark,TX (1)
     - 76071 (1)

I can do the tree thing late because there is other processing to do.

So my question is: How do I combine the counting of the specific values in the query itself? I know of the GroupBy functions and I've seen Aggregates, but I can't get them to work correctly. How do I go about wrapping all of these functions into one query?

EDIT: I think I asked my question the wrong way. I don't mean that I HAVE to do it all in one query... I'm asking IS THERE a clear, concise, and efficient way to do this with LINQ in one query? If not I'll just go back to looping through.

If I can be pointed in the right direction it would be a huge help. If someone has an easier idea in mind to do all this, please let me know.

I just wanted to avoid iterating through a huge array of values and using Regex.Split on every line.

Let me know if I need to clarify.

Thanks!

*EDIT 6/15***

I figured it out. Thanks to those who answered it helped out, but was not quite what I needed. As a side note I ended up changing it all up anyways. LINQ was actually slower than doing it other ways that I won't go into as it's not relevent. As to those who made multiple comments on "It's silly to have it in one query", that's the decision of the designer. All "Best Practices" don't work in all places. They are guidelines. Believe me, I do want to keep my code clear and understandable but I also had a very specific reasoning for doing it the way I did.

I do appreciate the help and direction.

Below is the prototype that I used but later abandoned.

    /* Inner LINQ query Reads the Text File and gets all the Locations.
     * The outer query summarizes this by getting the sum of the Zips 
     * and orders by City/State then ZIP */


var items = from Location in(
    //Inner Query Start
    (from line in File.ReadAllLines(FilePath)
    let columns = line.Split(';')
    where columns[0] == "US" & !string.IsNullOrEmpty(columns[4])
    select new
    {
    City = (FM.DecodeSLIC(columns[1].Trim()) + " " + columns[2].Trim()),
    County = columns[3].Trim(),
                   ZipCode = columns[4].Trim()
    }
    ))
    //Inner Query End
    orderby Location.City, Location.ZipCode  
                   group Location by new { Location.City, Location.ZipCode , Location.County} into grp
            select new
            {
            City = grp.Key.City,
            County = grp.Key.County,
            ZipCode = grp.Key.ZipCode,
            ZipCount = grp.Count()      
            };

Answer 1

The downside of using File.ReadAllLines is that you have to pull the entire file into memory before operating over it. Also, using Columns[] is a bit clunky. You might want to consider my article describing using DynamicObject and streaming the file as an alternative implemetnation. The grouping/counting operation is secondary to that discussion.

Answer 2

There is no point getting everything into one query. It's better to split the queries so that it would be meaningful. Try this to your results

var grouped = items.GroupBy(a => new { a.City, a.State, a.ZipCode }).Select(a => new { City = a.Key.City, State = a.Key.State, ZipCode = a.Key.ZipCode, ZipCount = a.Count()}).ToList();

Result screen shot

EDIT

Here is the one big long query which gives the same output

var itemsGrouped = File.ReadAllLines(myFile).Select(a => a.Split(';')).Where(a => a[0] == "US").Select(a => new { City = a[1].Trim(), State = a[2].Trim(), County = a[3].Trim(), ZipCode = a[4].Trim() }).GroupBy(a => new { a.City, a.State, a.ZipCode }).Select(a => new { City = a.Key.City, State = a.Key.State, ZipCode = a.Key.ZipCode, ZipCount = a.Count() }).ToList();

Answer 3

        var items = (from c in
                         (from line in File.ReadAllLines(myFile)
                          let columns = line.Split(';')
                          where columns[0] == "US"
                          select new
                                     {
                                         City = columns[1].Trim(),
                                         State = columns[2].Trim(),
                                         County = columns[3].Trim(),
                                         ZipCode = columns[4].Trim()
                                     })
                     select c);
        foreach (var i in items.GroupBy(an => an.City + "," + an.State))
        {
            Console.WriteLine("{0} ({1})",i.Key, i.Count());
            foreach (var j in i.GroupBy(an => an.ZipCode))
            {
                Console.WriteLine(" - {0} ({1})", j.Key, j.Count());

            }

        }