none
Help with lambda expression to preprocess query in VS LightSwitch 2012 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)
    Tuesday, October 29, 2013 11:27 PM

Answers

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

    //First obtain all the Transitions that have the parameterized state as current
                var candidates = (from tr in StateTransitions where tr.CurrentState.Id == pCurrentStateId select tr).Execute();
    
                //Then create a list with the Ids from the candidates we are interested in
                List<int> allTr = new List<int>();
                foreach (StateTransitions st in candidates)
                {
                    allTr.Add(st.NextPossibleState.Id);
                }
    
                //Finally we extend the query 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:52 PM

All replies

  • Hello,

    If I understand correctly, the table structure should be like below:

    CREATE TABLE [dbo].[States]
    
    (
    
           [StatesId] INT NOT NULL PRIMARY KEY, 
    
        [StatesName ] NVARCHAR(50) NOT NULL
    
    )
    
    CREATE TABLE [dbo].[StateTransitions]
    
    (
    
           [StateTransitionsId] INT NOT NULL PRIMARY KEY, 
    
        [CurrentStateId] INT NOT NULL, 
    
        [CurrentStateName] NVARCHAR(50) NOT NULL, 
    
        [NextPossibleStateId] INT NOT NULL, 
    
        [NextPossibleStateName] NVARCHAR(50) NOT NULL
    
    )
    

    The List should be like:

    If it is, we can write codes as below using Entity Framework:

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    
                {
    
                    List<string> stateList = (from tr in db.StateTransitions
    
                                              where tr.CurrentStateId == 1
    
                                              select tr.NextPossibleStateName).ToList<string>();
    
                }
    

    If I have misunderstood, please let me know.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, October 31, 2013 4:19 AM
    Moderator
  • Thank you Fred for taking the time to reply. 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'.

    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, November 1, 2013 2:36 AM
  • Have a try change codes to be below:

    IDataServiceQueryable<State> candidates=transitions.Where((tr)  =>
    
    tr.CurrentState.Id==pCurrentStatelId).Select(s=>s.NextPossibleState);
    


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, November 1, 2013 6:56 AM
    Moderator
  • Sorry, I had to unmark as answered. I tried the above, and I'm still getting a cast error:

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

    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:14 PM
  • I found a way around it, here it is:

    //First obtain all the Transitions that have the parameterized state as current
                var candidates = (from tr in StateTransitions where tr.CurrentState.Id == pCurrentStateId select tr).Execute();
    
                //Then create a list with the Ids from the candidates we are interested in
                List<int> allTr = new List<int>();
                foreach (StateTransitions st in candidates)
                {
                    allTr.Add(st.NextPossibleState.Id);
                }
    
                //Finally we extend the query 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:52 PM