none
Tying tow tables together with a dictionary. RRS feed

  • Question

  • I have the following queries

                var categoryDefinitions = from cx in catalogContext.CatIDXREFs
                                          group cx by cx.TopLevelCat into categoryList
                                          select categoryList;
                var catalogDefinitions = from c in catalogContext.Buyseasons_CatalogProducts
                                         select new { Category = c.CatID, Sku = c.VariantID };
    

    There are two tables involved. One is where the category (and sub category) is defined basically it has three columns long, string, string where the first column (long) is the CatId, the second column is a string description of the category, and the third is a string description of the sub-category. So the first query groups the data by category. The second query looks into the catalog and simply returns the SKU and the cat id for each SKU. The result I want is all of the SKUs that are associated with a category. For example the database may have a category definition like

    1,BOYS,Young
    2,BOYS,Adult
    3,BOYS,Senior
    4,GIRLS,Young

    The catalog may have

    1,10
    2,20
    3,30
    4,40

    Where the first number is the CatId and the second is the SKU for the first BOYS I would want a list of 10,20,30 and for GIRLS I would want 40.

    Can someone help me form a LINQ query that would link these two tables?

    Thank you.

    Kevin
    Friday, January 15, 2010 6:33 PM

Answers

  • Hi Kevin,

    Something like this might work for you:

    var result =
    from category in context.CatIDXREFs
    join catalog in context.BuySeasons_CatalogProducts on category.CatID equals catalog.CatID
    group catalog by category.TopLevelCat into grp
    select new 
    {
      TopLevelCategory = grp.Key,
      ListOfCatalogsInThatCategory = grp.Select( cat=>cat.VariantID )
    }


    Note that I am first joining the two tables and then grouping it by the TopLevelCategory field.

    Hope that helps.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by KevinBurton Wednesday, January 20, 2010 11:47 PM
    Friday, January 15, 2010 7:30 PM

All replies

  • Hi Kevin,

    Something like this might work for you:

    var result =
    from category in context.CatIDXREFs
    join catalog in context.BuySeasons_CatalogProducts on category.CatID equals catalog.CatID
    group catalog by category.TopLevelCat into grp
    select new 
    {
      TopLevelCategory = grp.Key,
      ListOfCatalogsInThatCategory = grp.Select( cat=>cat.VariantID )
    }


    Note that I am first joining the two tables and then grouping it by the TopLevelCategory field.

    Hope that helps.

    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    • Marked as answer by KevinBurton Wednesday, January 20, 2010 11:47 PM
    Friday, January 15, 2010 7:30 PM
  • Thank you very much. When I try your query I get an error pointing to the join part of the query:

    Error 1 The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'Join'. . . .\Program.cs 758 17 BuildOrderHistory

    This undoubtedly has to do with the fact that CatID in the catalog is a string and CatID in the CatIDXREF is a long.

    How do you suggest doing the cast?

    Kevin

    Friday, January 15, 2010 7:43 PM
  • I am glad type conversions are easy in LINQ To SQL. You either need to convert both fields to strings or long(Int64). Try replacing your join condition with either of these:

     on category.CatID.ToString() equals catalog.CatID
    
    //or
    
     on category.CatID equals Convert.ToInt64(catalog.CatID)


    Regards,
    Syed Mehroz Alam
    My Blog | My Articles
    Saturday, January 16, 2010 8:06 AM