locked
"Group By" workaround in PreprocessQuery RRS feed

  • Question

  • Hello! My data structure looks as following, including the results I want my query to return.

    Key       Revision
    ==================
    abc       null
    def       null
    ghj       2
    ghj       null
    klm       null
    def       1
    abc       1
    abc       2
    
    Result if parameter is 1
    ========================
    abc       1
    def       1
    ghj       null
    klm       null
    
    Result if parameter is null
    ========================
    abc       null
    def       null
    ghj       null
    klm       null


    My LINQ query looks like this:

    partial void RuleEntriesByUserSaveId_PreprocessQuery(int? UserSaveId, ref IQueryable<RuleEntry> query)
    {
       query = query.Where(re => re.Revision == null || re.Revision == UserSaveId)
                    .GroupBy(re => re.Key)
                    .Select(g => g.FirstOrDefault(x => x.Revision != null) ?? g.First());
    }

    I obviously don't work since GroupBy isn't supported in LightSwitch PreprocessQuery, I know I can solve this by aggregating data with a WCF RIA Service but I rather not get into that hassle.

    My question is if I, in some way, could rewrite this query without using GroupBy?

    Thanks alot

    Wednesday, April 17, 2013 8:58 AM

All replies

  • Another alternative is a SQL Server view:

    1) Create a view in SQL Server to do the grouping.

    2) Add the view to a data source in LightSwitch.

    3) Optionally, add a query in LightSwitch to add more filtering or sorting.

    4) Add the query or table to your screen.

    This is very quick and takes no programming other than creating the view.

    Mark

    Thursday, April 18, 2013 3:46 AM
  • Hi

    By default LightSwitch will not support GroupBy. To achieve this create a WCF RIA Service. Please go through the below tutorial this will help you to solve your issue.

    How Do I: Display a chart built on aggregated data (Eric Erhardt)

    Regards


    Rashmi Ranjan Panigrahi
    www.lightswitchspecial.com

    If you found this post helpful, please “Vote as Helpful”. If it answered your question, please “Mark as Answer”.
    This will help other users to find their answer quickly.

    • Proposed as answer by Angie Xu Tuesday, May 7, 2013 6:09 AM
    • Marked as answer by Angie Xu Wednesday, May 8, 2013 12:52 AM
    • Unmarked as answer by Yann DuranModerator Friday, May 10, 2013 12:45 PM
    Thursday, April 18, 2013 4:06 AM
  • I am by no means a LINQ expert, but what about using OrderBy and ThenBy instead?
     
        query = query.Where(re => re.Revision == null || re.Revision == UserSaveId).OrderBy(re => re.Revision).ThenBy(re => re.Key);
     
    -- David
     

    Efficiently read and post to forums with newsreaders: http://communitybridge.codeplex.com
    Thursday, April 18, 2013 5:25 AM
  • Thanks for your answer, but I need to pass a parameter and Views doesn't support that as far as I know.
    Thursday, April 18, 2013 6:50 AM
  • You are correct; you can't pass a parameter to a view like you can to a stored procedure, but here is how I make this work.

    1) I add a view to my data source.

    2) I create a query (based on the view) in LightSwitch and add a parameter, for example "ClientId".

    3) I use the query in my screen.

    You can also do sorting in the query as views do not support "ORDER BY".

    I use this in several places and it works fine.

    Thursday, April 18, 2013 1:42 PM
  • Very simple.

    Just have an if statement on the Parameter with 2 options for the OrderyBy syntax.


    Garth Henderson - Vanguard Business Technology

    Friday, April 19, 2013 5:31 PM
  • @Angie

    The OP said "I know I can solve this by aggregating data with a WCF RIA Service but I rather not get into that hassle", so obviously this is not the answer to his question.

    And please stop proposing answers then marking as the answer them hours later.


    Yann Duran
         - Co-Author of Pro Visual Studio LightSwitch 2011
         - Author of the  LightSwitch Central Blog

    FREE Download: Luminous Tools for LightSwitch
    (a Visual Studio productivity extension for LightSwitch)
     
    Click Mark as Answer, if someone's reply answers your question
    Click  Vote as Helpful, if someone's reply is helpful
     
    By doing this you'll help everyone find answers faster.

    Friday, May 10, 2013 12:45 PM
    Moderator