I'm working on an assignment that requires me to search through a database to return the company names that are in a city the user enters.

The method I've created works and does what it's supposed to, however, there are two issues:

1. It's printing a line 6 times instead of just once in the first foreach statement.
2. It's not returning the right amount of customers in the first foreach statement.

Here's the full method:

static void ReturnCities()
{
    WriteLine("Enter the name of a city: ");
    string? city = ReadLine();

    using (var db = new Northwind())
    {
        // query to find all customers in the
        // city that was entered
        var query = db.Customers
                      .Where(customer => customer.City == city)
                      .Select(customer => new
                              {
                                  customer.City
                              });

        // prints a statement with how many customers
        // are in the city entered
        foreach (var item in query)
        {
            WriteLine("There are {0} customers in {1}: ",
                      arg0: item.City.Count(),
                      arg1: item.City);
        }

        // query to return a list of all customers 
        // in the city entered       
        var query2 = db.Customers
                       .Where(customer => customer.City == city)
                       .Select(customer => new
                               {
                                   customer.CompanyName
                               });

        foreach (var item2 in query2)
        {
            WriteLine($"    {item2.CompanyName} ");
        }
    }
}

The output for this currently is:

Enter the name of a city: 
London
There are 6 customers in London: 
There are 6 customers in London: 
There are 6 customers in London:
There are 6 customers in London:
There are 6 customers in London:
There are 6 customers in London:
    Around the Horn
    B's Beverages
    Consolidated Holdings
    Eastern Connection
    North/South
    Seven Seas Imports

I'm not sure what's causing the first issue other than the foreach statement possibly because it's returning a print statement for each item in the query, but I haven't been able to fix it.

The second issue I'm currently still trying to fix but any and all tips for both issues are appreciated!

CodePudding user response：

Your query will just return Your query will just return an IQueryable<string> { "london", "london", "london" } because of the Select().

You could simply do 1 query for your code ...

var query = db.Customers
    .Where(customer => new() {
        customer.City,
        customer.CompanyName
    });

var count = query.Count();
// output: 6

foreach(var customer in query) 
{
    Console.Writeline(customer.CompanyName);
}

// output:
//    Around the Horn
//    B's Beverages
//    Consolidated Holdings
//    Eastern Connection
//    North/South
//    Seven Seas Imports