JOIN/SELECT for LinqDataSource RRS feed

  • Question

  • This SELECT works great with Sql Data Source.  This SELECT joins  table1 with table2, table2 being the result of aggregates made on table3, i.e., the AVG and COUNT.  

        SelectCommand="SELECT * FROM (SELECT RecipeID, COUNT(*) AS Count, AVG(Rating) AS RatingAVG FROM AfricanRatings GROUP BY RecipeID) AS AfricanRatingsAVG
                           JOIN (SELECT AfricanRecipes.RecipeID, AfricanRecipes.Category, AfricanRecipes.Name, AfricanRecipes.Description
                           FROM AfricanRecipes) AS AfricanRecipes ON (AfricanRatingsAVG.RecipeID = AfricanRecipes.RecipeID)">

    I have researched and tried hundreds of variations but cannot get it right for Linq DataSource, which is very important because of the Search/Filter aspects of Linq.  Is there a way to make this SELECT/JOIN work with LinqDataSource?

    Any help would be greatly appreciated.

    Earl Davis

    Wednesday, April 3, 2013 10:38 PM


  • I am uncertain if AfricanRatingsAVG have a Primary key, else you should start by defining this,
    and then the RecipeId should be defined as a foreign key to the ReciepeId in AfricanRecipes.

    Then you can just do:

    from row in AfricanRecipes
    select row;

    Now if you want the count you simply write


    Using the normal dot syntax you should be able to achive what you want.

    Thursday, April 4, 2013 11:30 AM