How can I use the Distinct() method from LINQ to SQL on a List<T>?

Data and Databases
1

I am facing a frustrating issue with LINQ to SQL while developing an ASP.NET MVC3 application in C# with Razor using Visual Studio 2010.

I have two tables in my database: Product and Categories.

  • Product(Prod_Id [primary key], other attributes)
  • Categories(Dept_Id, Prod_Id [primary keys], other attributes)

Here, Prod_Id in Categories serves as a foreign key. I’ve successfully mapped both entities using the Entity Framework (EF) for the sake of simplicity.

The Categories table has multiple rows for the same Prod_Id, and I need a projection of all the distinct Prod_Id values in that table. I achieved this easily with the following SQL query:

SELECT DISTINCT Prod_Id
FROM Categories

This query returns the expected result. To replicate this in my application, I tried:

var distinctProdIds = _StoreDB.Categories.Select(item => item.Prod_Id).Distinct();

When I attempt to access the values using:

distinctProdIds.Select(item => item.Prod_Id);

Or via a foreach loop:

foreach (var value in distinctProdIds) {
  var prodId = value;
  // additional operations
}

It doesn’t function as intended. The Intellisense doesn’t suggest properties for distinctProdIds.Select() or value., only methods (like Equals, etc.). I initially suspected that Intellisense was mistaken, but during runtime, an error confirms there’s a problem.

Please note:

  1. I explored multiple forums and attempted standard LINQ to SQL queries (not using lambdas), but they failed. Since plain SQL executes correctly, I suspect an issue with my LINQ query (other queries in my app function flawlessly).
  2. For application-specific reasons, I utilized a List in place of _StoreDB.Categories. If possible, I would appreciate a solution that doesn’t involve List.
2

In your case, the key thing to remember is that the Distinct() method in LINQ works well on simple data types, but when dealing with custom objects, the behavior might differ. Since you are dealing with integers, your approach seems technically correct, but a subtle mistake could be the way you’re handling it afterwards. When you apply Distinct() to var distinctProdIds = _StoreDB.Categories.Select(item => item.Prod_Id).Distinct();, you get an IEnumerable<int>, which is a sequence of integers. Every element is just an int, so when you loop through distinctProdIds, you should directly work with int values, not expecting them to have properties like an object would. Also, ensure your data context _StoreDB isn’t disposed prematurely, as it might affect data fetching operations.

3

sometimes the issue can be a simple one, like not calling ToList() or similar methods after querying. Try changing var distinctProdIds = _StoreDB.Categories.Select(item => item.Prod_Id).Distinct(); to var distinctProdIds = _StoreDB.Categories.Select(item => item.Prod_Id).Distinct().ToList();. it should work since ToList performs execution of query and materializes your results.