How to apply LINQ to SQL Distinct () statement to <T> list?

Question

I have a serious problem (with my crazy one) with LINQ to SQL . I am developing an ASP.NET MVC3 application using C # and Razor in Visual Studio 2010.

I have two database tables, Product and Categories :

Product (Prod_Id [primary key], other attributes)

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

Obviously, Prod_Id in categories is a foreign key. Both classes are mapped using the Entity Framework (EF). I do not mention the application context for simplicity.

There are several lines in the Category containing Prod_Id . I want to project all of the Distinct Prod_Id in a category. I did this using simple (T) SQL in MGMT Studio SQL Server according to this (really simple) query:

SELECT DISTINCT Prod_Id FROM Categories 

and the result is correct. Now I need to make this request in my application, so I used:

 var query = _StoreDB.Categories.Select(m => m.Prod_Id).Distinct(); 

I want to check the result of my query using:

 query.Select(m => m.Prod_Id); 

or

 foreach(var item in query) { item.Prod_Id; //other instructions } 

and it does not work. First of all, Intellisense, when I try to write query.Select(m => m. Or item. , Shows only suggestions about methods (such as Equals, etc.), and not properties. I thought it was possible that- this happened to Intellisense (I think many of you have hoped many times that Intellisense is wrong: -D), but when I launch the application, I get an error message at runtime.

Before giving your answer, keep in mind that:

1) I checked a lot of forums, I tried the usual LINQ to SQL (without using lambdas), but it does not work. The fact that it works in (T) SQL means that something is wrong with the LINQ to SQL statement (other queries in my application work fine).

2) For reasons related to the application, I used the variable List<T> instead of _StoreDB.Categories, and I thought it was a problem. If you can offer me a solution without using List<T> , then it will also be appreciated.

thank you for your responses

Francesco

Answer 1

This line:

 var query = _StoreDB.Categories.Select(m => m.Prod_Id).Distinct(); 

Your LINQ query most likely returns IEnumerable ... from ints (judging by Select(m => m.Prod_Id) ). You have a list of integers, not a list of entity objects. Try printing them and see what you have.

Answer 2

Calling _StoreDB.Categories.Select(m => m.Prod_Id) means that the query will contain Prod_Id values only , not the entire object. This will be roughly equivalent to this SQL, which selects only one column (and not the entire row):

 SELECT Prod_Id FROM Categories; 

Therefore, when you iterate through query using foreach (var item in query) , the type of item is probably int (or any other column of Prod_Id ), not your object. This is why Intellisense does not show the entity properties that you expect when you type " item. " ...

If you want all the columns in Categories be included in the query , you don't even need to use .Select(m => m) . You can simply do this:

 var query = _StoreDB.Categories.Distinct(); 

Note that unless you explicitly pass IEqualityComparer<T> to Distinct() , EqualityComparer<T>.Default (which may or may not behave the way you want, depending on type T , whether it implements System.IEquatable<T> etc.).

For more information on how Distinct works in such situations, see this question or this question and related discussions.

Answer 3

As explained by other answers, the error OP encountered was caused by the result of his code being a set of int, not a set of categories.

To which no answer was given, there was a question about how to use the int collection in a connection or something else to get some useful data. I will try to do it here.

Now I'm not quite sure why the OP wanted to get a separate list of Prod_Ids from categories, and not just get Prod_Ids from projects. Perhaps he wanted to know which products belong to one or more categories, so any unclassified products will be excluded from the results. I assume that this is so, and that the desired result is a set of individual products with related categories. First, I will answer the question of what to do with Prod_Ids, and then we will offer several alternatives.

We can take the Prod_Ids collection exactly as they were created in the question as a request:

 var query = _StoreDB.Categories.Select(m => m.Prod_Id).Distinct(); 

Then we will use join, for example:

 var products = query.Join(_StoreDB.Products, id => id, p => p.Prod_Id, (id,p) => p); 

This takes a query, attaches it to the Products table, indicates the keys to use, and finally says to return a Product object from each matching set. Since we know that Prod_Ids in a query are unique (due to Distinct ()), and Prod_Ids in products are unique (by definition, because this is the primary key), we know that the results will be unique without calling Distinct ().

Now the above will get the desired results, but it is definitely not the cleanest or easiest way to do this. If Category entities are defined with a relational property that returns a related record for Products (which will probably be called Product), the easiest way to do what we are trying to do is as follows:

 var products = _StoreDB.Categories.Select(c => c.Product).Distinct(); 

This gets the Product from each category and returns their separate collection. If the Category object does not have the property of relational properties of the product, we can return to using the Join function to get our products.

 var products = _StoreDB.Categories.Join(_StoreDB.Products, c => c.Prod_Id, p => p.Prod_Id, (c,p) => p).Distinct(); 

Finally, if we do not just want to get a simple set of products, then a little more, although we would have to do it, and perhaps the simplest would be to deal with this when repeating through Products. Another example is the calculation of the number of categories to which one Product belongs. If this happens, I would cancel the logic and start with Products, for example:

 var productsWithCount = _StoreDB.Products.Select(p => new { Product = p, NumberOfCategories = _StoreDB.Categories.Count(c => c.Prod_Id == p.Prod_Id)}); 

This will result in the collection of anonymous typed objects that reference the Product and NumberOfCategories associated with this product. If we still need to exclude any non-catalyzed products, we could add .Where(r => r.NumberOfCategories > 0) to the semicolon. Of course, if a Product object is defined with a relational property for related categories, you won’t need it, because you can just take any Product and do the following:

 int NumberOfCategories = product.Categories.Count(); 

In any case, sorry for the incoherent continuation. I hope this will be useful for everyone who is faced with a similar problem .;)