none
Comparing two Lists RRS feed

  • Question

  • Have two lists - one containing 'category ids' the other products. I would like to generate a list of products matching all category ids. Data access is using LINQ to entities.

    List<int> (category ids)    List<products>
    221                                 Prod 1, 221
    222                                 Prod 2, 221
                                          Prod 3, 221*
                                          Prod 3, 222*

    For the example data I would want Prod 3 only.

    Thank you,

    Steve

    • Moved by CoolDadTx Thursday, March 26, 2015 3:45 PM EF related
    Thursday, March 26, 2015 2:20 PM

Answers

  • You could group the products by the name and then use the Except method to compare the groups agains the categories list. Please refer to the following sample code:

     List<int> categories = new List<int>() { 221, 222 };
                
                List<products> products = new List<products>() { new products { Name = "p1", CategoryId = 221 }, 
                    new products { Name = "p2", CategoryId = 221 },
                    new products { Name = "p3", CategoryId = 221 }, 
                    new products { Name = "p3", CategoryId = 222 }};
    
               
                var groups = (from p in products
                           group p.CategoryId by p.Name into g
                           select new { ProductName = g.Key, CategoryIds = g.ToList() });
    
                //all product names:
                List<string> matchingproductNames = groups.Where(g => categories.Except(g.CategoryIds).Count().Equals(0)).Select(g => g.ProductName).ToList();
    
                //or all products:
                List<products> matchingProducts = products.Where(p => matchingproductNames.Contains(p.Name)).ToList();
    

    Hope that helps.

    Please remember to mark all helpful posts as answer to close your threads and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    • Proposed as answer by cedric pautet Thursday, March 26, 2015 3:43 PM
    • Marked as answer by Steven_Schultz Thursday, March 26, 2015 7:56 PM
    Thursday, March 26, 2015 3:39 PM
  • The all part makes this complicated because you are no longer simply looking at each row but the aggregated view of all the rows in order to eliminate products that don't have a matching # of entries.  However I believe you can accomplish this by combining a standard query for any product in any of the categories grouped by the product and having a count that matches the # of categories you are searching for.  Personally I think that T-SQL may be the better place to handle this type of query as it can be done using a GROUP BY with HAVING clause.  But I think the following may work for LINQ as well.

    var categories = new[] { 221, 222 };
    
    var products = new[] {
        new Product() { Name = "Prod 1", Category = 221 },
        new Product() { Name = "Prod 2", Category = 221 },
        new Product() { Name = "Prod 3", Category = 221 },
        new Product() { Name = "Prod 3", Category = 222 },
    };
    
    var productsInCategory = from p in products
                             where categories.Contains(p.Category)
                             group p by p.Name into g
                             where g.Count() == categories.Count()
                             select g;

    The biggest issue you may face is that EF might not like the query.  You might have to use some EF-specific SQL functions to get this to work if EF fails the query.

    Michael Taylor
    http://blogs.msmvps.com/p3net

    Thursday, March 26, 2015 3:44 PM

All replies

  • You could group the products by the name and then use the Except method to compare the groups agains the categories list. Please refer to the following sample code:

     List<int> categories = new List<int>() { 221, 222 };
                
                List<products> products = new List<products>() { new products { Name = "p1", CategoryId = 221 }, 
                    new products { Name = "p2", CategoryId = 221 },
                    new products { Name = "p3", CategoryId = 221 }, 
                    new products { Name = "p3", CategoryId = 222 }};
    
               
                var groups = (from p in products
                           group p.CategoryId by p.Name into g
                           select new { ProductName = g.Key, CategoryIds = g.ToList() });
    
                //all product names:
                List<string> matchingproductNames = groups.Where(g => categories.Except(g.CategoryIds).Count().Equals(0)).Select(g => g.ProductName).ToList();
    
                //or all products:
                List<products> matchingProducts = products.Where(p => matchingproductNames.Contains(p.Name)).ToList();
    

    Hope that helps.

    Please remember to mark all helpful posts as answer to close your threads and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    • Proposed as answer by cedric pautet Thursday, March 26, 2015 3:43 PM
    • Marked as answer by Steven_Schultz Thursday, March 26, 2015 7:56 PM
    Thursday, March 26, 2015 3:39 PM
  • The all part makes this complicated because you are no longer simply looking at each row but the aggregated view of all the rows in order to eliminate products that don't have a matching # of entries.  However I believe you can accomplish this by combining a standard query for any product in any of the categories grouped by the product and having a count that matches the # of categories you are searching for.  Personally I think that T-SQL may be the better place to handle this type of query as it can be done using a GROUP BY with HAVING clause.  But I think the following may work for LINQ as well.

    var categories = new[] { 221, 222 };
    
    var products = new[] {
        new Product() { Name = "Prod 1", Category = 221 },
        new Product() { Name = "Prod 2", Category = 221 },
        new Product() { Name = "Prod 3", Category = 221 },
        new Product() { Name = "Prod 3", Category = 222 },
    };
    
    var productsInCategory = from p in products
                             where categories.Contains(p.Category)
                             group p by p.Name into g
                             where g.Count() == categories.Count()
                             select g;

    The biggest issue you may face is that EF might not like the query.  You might have to use some EF-specific SQL functions to get this to work if EF fails the query.

    Michael Taylor
    http://blogs.msmvps.com/p3net

    Thursday, March 26, 2015 3:44 PM
  • Both options work, thank you  both very much!
    Thursday, March 26, 2015 7:57 PM