locked
Help extending query to populate lookup list RRS feed

  • Question

  • Hi,

    I have this scenario (simplified): Issues, with possible States, and Tasks related to them. Everytime a new Task is created, the lookup list for State should show only the possible reachable States, according to a StateTransitions table, based on the current State.

    I'm trying to create a query that serves as Choice for the mentioned New State lookup list.

    States contains Id (int), StateName (string).

    The StateTransitions contains CurrentState (State) and NextPossibleState (State).

    The query contains a parameter for the current state: pCurrentStateId.

    How can I do something like this SQL statement using a Lambda expression, to populate the lookup list?

    SELECT NextPossibleState
    FROM StateTransitions
    WHERE CurrentState.Id = pCurrentStateId

    There might be more than one possible state for the current state.

    Preprocess Query arguments are:

    StateLookupQuery_PreprocessQuery(int? pCurrentStateId, ref IQueriable<States> query)


    Monday, October 7, 2013 4:43 PM

Answers

  • I found a way around it, here it is:

    //First obtain all the Transitions that have the parameterized state as current

    and create a List List<int> allTr = (from tr in StateTransitions

    where tr.CurrentState.Id == pCurrentStateId

    select tr.NextPossibleState.Id)

    .Execute()

    .ToList(); //Then extend the query by selecting the ones we put on the list query = from nps in query where allTr.Contains(nps.Id) select nps;


    It was inspired in this post.


    Tuesday, November 12, 2013 1:51 PM

All replies

  • Hi,

    You can take a look at this post, I believe it will provide you with the guidelines on how to do that:

    http://blogs.msdn.com/b/lightswitch/archive/2010/11/15/how-to-use-lookup-tables-with-parameterized-queries-karol-zadora-przylecki.aspx

    If that does not work for you, Please send me a screen shot of your schema and your screens, and I'll try to provide you with detailed steps on how to do it.

    Monday, October 7, 2013 5:15 PM
  • Thank you Amr, unfortunately mine is a more complex scanario. I cannot post screenshots due to privacy restrictions.

    Let's say the values for States are: opened, assigned, waitingforapproval, and closed.

    The StateTransitions are:

    CurrentState         NextPossibleState

    open                     assigned

    assigned                waitingforapproval

    assigned                closed

    waitingforapproval  closed

    And the pCurrentState is 'assigned'.

    The lookup list (with the query as Choice) should contain two possible States: 'waitingforapproval' and 'closed'. Instead, it has 'assigned' and 'closed'.

    The expression I use is:

    var candidates = from States state in query
                     where state.StateTransitions.All(s => s.CurrentState.Id == pCurrentStateId)
                     select state;
                 
    query = candidates;

    Also I tried:

    query = query.Where(st => st.StateTransitions.Where((tr) => tr.CurrentState.Id == pCurrentStateId).Any());

    But didn't work either.


    Monday, October 7, 2013 6:53 PM
  • I'm getting closer getting a cast error. I tried:

    var transitions = DataWorkspace.ApplicationData.StateTransitions;
    
    IQueryable<State> candidates=transitions.Where((tr)  =>
    
    tr.CurrentState.Id==pCurrentStatelId).Select(s=>s.NextPossibleState);
    
    query=candidates;

     
     
    Throws an error on the  .Select(s =>....):
    Cannot implicitly convert type 'Microsoft.LightSwitch.IDataServiceQueryable<LightSwitchApplication.State>' to 'System.Linq.IQueryable<LightSwitchApplication.State>'. An explicit conversion exists (are you missing a cast?) 
     
    Then, if I cast to IDataServiceQueryable<State> I get the same error in query = candidates;

    Friday, October 11, 2013 10:32 PM
  • I kept trying. Keep in mind the expected object type is IQueryable.

    So instead I declared 'candidates' as var, then tried: query = candidates.AsQueryable() but again:

    Error 1: Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Linq.IQueryable<LightSwitchApplication.State>'. An explicit conversion exists (are you missing a cast?)

    After that I tried:

    query = candidates.AsQueryable().Cast<State>();

    there are no errors at design time, but when I run the code I get:

    Selecting the source element is the only supported selector.

    UPDATE:

    I found out that selecting a few fields from an entity is not supported in LightSwitch. It always returns the whole entity. So I'm stuck here.

    Monday, November 11, 2013 7:37 PM
  • I found a way around it, here it is:

    //First obtain all the Transitions that have the parameterized state as current

    and create a List List<int> allTr = (from tr in StateTransitions

    where tr.CurrentState.Id == pCurrentStateId

    select tr.NextPossibleState.Id)

    .Execute()

    .ToList(); //Then extend the query by selecting the ones we put on the list query = from nps in query where allTr.Contains(nps.Id) select nps;


    It was inspired in this post.


    Tuesday, November 12, 2013 1:51 PM