Querying with LINQ and EF Core

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!

>Solution :

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