none
Local collection and join? RRS feed

  • Question

  • I have two local collecitons. One is List<string> which I call a category list and the other is a List<CategoryDetail> where CategoryDetail simply contains two strings one for the category name and the other for the sub-category name. I want to use these collections which are what a user has selected but I am running into some difficulty that I was hoping to get help here resolving. My brute force approach looks like:

     

                        var selectForecastList = from c in orderContext.OrderForecasts
                                                 join sc in form.SelectedCategories on c.Category equals sc
                                                 where c.IdentifierType == 1 && form.SelectedCategories.Contains(c.Category)
                                                 select new { c.Category, c.Model, c.Created, c.ModelDetail };
    

    for the category and

                        var selectForecastList = from c in orderContext.OrderForecasts
                                                 join sc in form.SelectedSubCategories on c.Category equals sc.Category
                                                 join ssc in form.SelectedSubCategories on c.SubCategory equals ssc.SubCategory
                                                 where c.IdentifierType == 2
                                                 select new { c.Category, c.SubCategory, c.Model, c.Created, c.ModelDetail };
    

    for the sub category. The problem on the first query is I get:

    Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

    I can easily convert the first sqeunce to us 'Contains' but what about the second query? It is not a SubCategory unless *both* the category name and the sub-category name match.

    Suggestions?

    Thank you.

    Kevin
    Thursday, June 4, 2009 5:59 PM

Answers


  • I think you can do exactly the same, but instead of

    form.SelectedCategories.Contains(c.Category)

    use

    form.SelectedCategories.Contains(new CategoryDetail { Category = c.Category, SubCategory = c.SubCategory })
    • Marked as answer by KevinBurton Friday, June 5, 2009 3:53 AM
    Thursday, June 4, 2009 7:44 PM

All replies


  • It can't convert to a SQL statement.  You will want to use AsEnumerable() to allow processing to happen locally.


        var selectForcastList = orderContext.OrderForcasts
                                            .Where(c => c.IdentifierType == 1)
                                            .AsEnumerable()
                                            .Where(c => form.SelectedCategories.Contains(c.Category))
                                            .Select(c => new { c.Category, c.Model, c.Created, c.ModelData });
    


    I'm sorry, but I don't see why either of your statements needs a join.
    Thursday, June 4, 2009 6:37 PM
  • Thank you. This was just what I need.

    Now what about the SubCategory case where the List is a structure/class with a category and sub category?

    THanks again.

    Kevin
    Thursday, June 4, 2009 7:23 PM

  • I think you can do exactly the same, but instead of

    form.SelectedCategories.Contains(c.Category)

    use

    form.SelectedCategories.Contains(new CategoryDetail { Category = c.Category, SubCategory = c.SubCategory })
    • Marked as answer by KevinBurton Friday, June 5, 2009 3:53 AM
    Thursday, June 4, 2009 7:44 PM