Northwind Database Linq Query

Question

I'm new to Linq and I'm trying to write a query using the northwind database which should return all suppliers who have two or more products in the same category.

var test1 =
(from p in Products
join pi in Products on p.CategoryID equals pi.CategoryID
join pf in Products on p.SupplierID equals pf.SupplierID
where p.ProductID != pi.ProductID
select new 
{p.ProductName, p.CategoryID, p.SupplierID}).ToList().Distinct();


test1.Dump();

This was my last try that didn't work. I'm a bit resigned because I've been trying to figure this out for hours and it still won't do what it's supposed to. Maybe I'm just getting it completely wrong?

My approach was that there has to be two or more listings with the same SupplierID and CategoryID but different ProductID, but yet I haven't found a solution.

Answer 1

This is better done using a GroupBy() :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            List<Product> Products = new List<Product>() {
                new Product() { ProductName = "ABC", CategoryID = 1, SupplierID = 1},
                new Product() { ProductName = "DEF", CategoryID = 1, SupplierID = 1},
                new Product() { ProductName = "GHI", CategoryID = 1, SupplierID = 3},
                new Product() { ProductName = "JKL", CategoryID = 1, SupplierID = 3},
                new Product() { ProductName = "MNO", CategoryID = 2, SupplierID = 1},
                new Product() { ProductName = "PQR", CategoryID = 3, SupplierID = 1},
                new Product() { ProductName = "STU", CategoryID = 4, SupplierID = 1},
                new Product() { ProductName = "VWX", CategoryID = 4, SupplierID = 1},
                new Product() { ProductName = "YZ1", CategoryID = 4, SupplierID = 1},
                new Product() { ProductName = "234", CategoryID = 5, SupplierID = 1}
            };



            var test1 = Products.GroupBy(x => new { supplier = x.SupplierID, category = x.CategoryID })
                .Where(x => x.Count() >= 2).Select(y => y.Select(z => new { name = z.ProductName, supplier = y.Key.supplier, category = y.Key.category })).SelectMany(x => x).ToList();

            foreach (var item in test1)
            {
                Console.WriteLine("Name = '{0}', Supplier = '{1}', Category = '{2}'", item.name, item.supplier, item.category);
            }
            Console.ReadLine();
        }

    }
    public class Product
    {
        public string ProductName { get; set; }
        public int CategoryID { get; set; }
        public int SupplierID { get; set; }
    }
}

Answer 2

Your desired outcome is missing, but I can tell you that what you're doing right now is not going to do much good.

Simply put, you're currently asking the database to return all products that match all products that match all products, which basically results in you getting all products, if the database doesn't time out. So, we can simplify your query to this:

var test1 =
(from p in Products
select new 
{p.ProductName, p.CategoryID, p.SupplierID}).ToList().Distinct();

From this selection, you then want to select an unique lists of product names, category and supplier. The main question here is: do you want a unique list of combinations, or does one of the three properties need to be unique? Assuming the first, the easiest way to get your result is as such:

public class ProductResult  : IEquatable<ProductResult> // we have to tell C# compiler how to check if the objects are different from one another
{
   public string Name { get; set; }
   public string Category { get; set; }
   public string Supplier { get; set; }

   public bool Equals(ProductResultother)
            {
                if (other == null)
                    return false;

                return (Category == other.Category) 
                       && Supplier  == other.Supplier)
                       && Name  == other.Name ); // optionally do an .Equals() to make this case-insensitive (check for nulls first if you do this!)
            }
}

Then you can do this:

var test1 = (from p in Products
select new ProductResult()
{
   Name = p.ProductName,
   Category = p.CategoryId,
   Supplier = p.SupplierID,
}).Distinct();

You now have a list of all unique product name / category / supplier combinations.