locked
Need help conceptualizing query logic RRS feed

  • Question

  • I've gotten some great answers from this forum so here goes!

    I have a table "Employees" with a  0:1-N relationship to table "Certifications". We'd like to able to filter a list of employees based on the HIGHEST certification in a query. For example, considering the following rows in the "Certifications" table (expressed here as a CSV):

    [Certification], [Rank]

    EMT Medic, 4

    First Responder, 3

    Litter Carrier, 2

    Rescue Vehicle Operator , 0

    What I'd like to do is, say for example, select in a picklist/dropdown the value "First Responder" which has a rank of "3" and the query would return as a list, every employee that is a "First Responder" AND every employee that is also a litter carrier or has no certification. In other words, if an Incident Commander decides he needs x number of people that AT LEAST includes a first responder, the "Rank" value of 3 will supply a query with equal to or less than "3". The 0:1 part of the relationship drops any employees that have no certification by default from any query. 

    I hope this makes sense. 

    Visually, I can picture this perfectly if I'm using SQL syntax, and I am very easily able to fire a query in the very-beginner-friendly VS LS environment that give me "Medics" or "First Responders", but I'm having a hard time wrapping my head around how to bind the query to the "Rank" column, fire the equal to or less then criteria, yet still present users with a pick-list of the "Certification Column". Haven't coded anything in attempt to do this, still at the searching-MSDN-forum for an example stage. 


    Friday, March 7, 2014 11:50 PM

Answers

  • If it is really 0:1-to-1 you may not need the grouping/distinct part :) but it is a useful sample for many other occasions.

    Which is this:

    #1 Employee 0:1-to-N Certifications ?

    #2 Certification 0:1-to-N Employees ?

    The code was for #1 but your later description sounds like #2.


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.


    • Edited by Xpert360 Saturday, March 8, 2014 6:48 PM
    • Marked as answer by tpcolson Sunday, March 9, 2014 5:38 PM
    Saturday, March 8, 2014 6:44 PM

All replies

  • There may be ways to do this without using a WCF RIA Service, but I wanted to offer it as an option because it is what I would use. For example see: LightSwitch Survey: Handling Complex Business Logic Using WCF RIA Services. In that example I had to return a list of Surveys but, for each Survey, look in an associated table to see if the current user has completed all the questions.

    Using a WCF RIA Service allows me to do procedure code looping to create the results I need yet it still works great in LightSwitch:

           [Query(IsDefault = true)]
            public IQueryable<QuestionsForUser> GetAllQuestionsForUser()
            {
                // Get the current user
                string strCurrentUserName = System.Web.HttpContext.Current.User.Identity.Name;
                // We are under Forms Authentication so if user is blank then we
                // are debugging and we are TestUser
                if (strCurrentUserName == "")
                {
                    strCurrentUserName = "TestUser";
                }
    
                // Get all the Questions For User
                var colQuestionsForUser = from Survey_Question in this.Context.SurveyQuestions
                                          orderby Survey_Question.Survey.Id
                                          orderby Survey_Question.Id
                                          // Shape the results into the 
                                          // QuestionsForUser class
                                          select new QuestionsForUser
                                          {
                                              QuestionId = Survey_Question.Id,
                                              UserName = strCurrentUserName,
                                              SurveyId = Survey_Question.SurveyQuestion_Survey,
                                              Question = Survey_Question.Question,
                                              isAnswered = ((Survey_Question.SurveyAnswers
                                              .Where(x => x.UserName == strCurrentUserName
                                                  && x.SurveyQuestion.Id == Survey_Question.Id).Count()) > 0),
                                              isActive = false
                                          };
    
                // final collection
                List<QuestionsForUser> GetAllQuestionsForUserFinal = new List<QuestionsForUser>();
    
                // Flag for the current SurveyID
                int intCurrentSurveyID = -1;
    
                // Flag to indicate when at least one question is marked active
                bool boolNextQuestionLocated = false;
    
                // Loop through all questions
                foreach (var item in colQuestionsForUser)
                {
                    // Possibly reset intCurrentSurveyID
                    if (intCurrentSurveyID != item.SurveyId)
                    {
                        // Reset intCurrentSurveyID
                        intCurrentSurveyID = item.SurveyId;
    
                       // Set boolNextQuestionLocated
                        boolNextQuestionLocated = false;
                   }
    
                    // If isAnswered set isActive to true
                    item.isActive = (item.isAnswered);
    
                    // Find the first non active question 
                    if (!item.isActive && (boolNextQuestionLocated == false))
                    {
                        item.isActive = true;
                        boolNextQuestionLocated = true;
                    }
    
                   GetAllQuestionsForUserFinal.Add(item);
               }
    
               return GetAllQuestionsForUserFinal.AsQueryable<QuestionsForUser>();
            }


    Unleash the Power - Get the LightSwitch HTML Client book

    http://LightSwitchHelpWebsite.com


    Saturday, March 8, 2014 12:19 AM
  • I've been looking at WCF RIA through some of your blog posts for a number of opportunistic scenarios, namely performance, but it remains on my list of things to learn how to implement on a "Sandbox" project.

    Unfortunately, I'm trying to keep coding and complexity to a minimum. I'm one of they guys with one of the certifications, we don't really have access (or budget) for computer programmer folks that would be able to wrap their heads around RIA services, setting them up, and maintaining them. That, and with the summer "Rescue" season soon approaching, I gotta keep this simple. 

    Thanks for your reply though. Every (other) thing I've figured out how to do in VS LS has come from great forum replies!

    Saturday, March 8, 2014 12:32 AM
  • You may want to try this tutorial:

    Creating a WCF RIA Service for Visual Studio LightSwitch 2013

    and see that they are not as hard as they seem. Over the years I have created over 1000 LightSwitch applications and when faced with complex business logic, WCF RIA Services are the only thing I didn't regret when I had to return to the code (usually the next day) to maintain it.

    For example, you can solve the challenge using JavaScript or custom controls (if you are using the desktop client), but when you have to go back to make changes to the code, or try to consume the results elsewhere in the application you may have a pile of hard to maintain code.


    Unleash the Power - Get the LightSwitch HTML Client book

    http://LightSwitchHelpWebsite.com

    Saturday, March 8, 2014 12:40 AM
  • A few questions about RIA in general:

    Overall, this application, while simple, has a few queries, a few related table, one many to many relationship, and a few data entry and one search screen: This will illustrate my ignorance of WCF RIA in general, but, in translating the tutorial on RIA you posted, do I have to create a separate RIA service for each query/table/screen?  What would be the simplest approach to move as much of an EXISTING LS application, with all of its objects, into RIA? 

    While I'm potentially a big fan of WCF RIA, in the context of simplifying future maintenance and enhancing performance, I'm really under a tight deadline to knock this project out with as few mouse clicks as possible, and regarding the original topic, is WCF RIA the only way to accomplish the result? If so, we might have to revisit the desired business rules and drop the "Search by Less than or equal to Rank" requirement due to constraints our capability to use some of the more advanced features of LS. 

    Thanks again!

    Saturday, March 8, 2014 5:19 PM
  • Here is a non-RIA service way:

    Create a custom query on Employee and add an integer parameter 'MaxRank'. Add code to the PreProcessQuery event to use the parameter to do your filtering.

            partial void EmployeeWithRank_PreprocessQuery(int? MaxRank, ref IQueryable<Employee> query)
            {
                query = from q in query
                        from c in q.Certifications
                        where c.Rank <= MaxRank
                        group q by new { q.Id } into g
                        select g.FirstOrDefault();
            }
    

    Gets distinct employee from query where employee has a certification <= MaxRank. Adjust as you need it for similar filtering. Bind any selected item's integer (Rank) to the MaxRank query parm.

    Looking at your scenario, I would question whether this should be a Many-to-Many with a link table. If that is the case that is not too hard to do either.


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Saturday, March 8, 2014 6:02 PM
  • We initially had employee -> certifications as a many to many with a link table, but circled back, as an employee can only have one certification, as, one certification "outranks" another, and the "customer" (my boss) doesn't want to manage a many to many certifications relationship.

    I'm going to give your answer a try, thanks for replying!

    Saturday, March 8, 2014 6:33 PM
  • If it is really 0:1-to-1 you may not need the grouping/distinct part :) but it is a useful sample for many other occasions.

    Which is this:

    #1 Employee 0:1-to-N Certifications ?

    #2 Certification 0:1-to-N Employees ?

    The code was for #1 but your later description sounds like #2.


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.


    • Edited by Xpert360 Saturday, March 8, 2014 6:48 PM
    • Marked as answer by tpcolson Sunday, March 9, 2014 5:38 PM
    Saturday, March 8, 2014 6:44 PM
  • I'm having a hard time converting C to VB (should have stated that was what I using in the first place!).

    in the code below, q.SARCertificationsSetItem flags as unrecognized. 

            Private Sub QRYSarCert_PreprocessQuery(MaxRank As System.Nullable(Of Integer), ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Employee))
                query = From q In query
                        From c In q.SARCertificationsSetItem
                        Where c.Rank <= MaxRank
                        Group q By New q.Id Into g()
                        Select g.FirstOrDefault()
            End Sub

    A few more questions, when you say Bind Rank to the query param, do you mean the query param from the Search Screen, or from the custom query on the table. Thanks!

    Saturday, March 8, 2014 6:53 PM
  • Saturday, March 8, 2014 8:33 PM
  •  do I have to create a separate RIA service for each query/table/screen?  What would be the simplest approach to move as much of an EXISTING LS application, with all of its objects, into RIA? 

    No you only create one WCF RI Service. You can put as many entities in it as you need. You only need to create the WCF RIA methods as you need them, otherwise you use the normal LightSwitch entities and collections.

    Unleash the Power - Get the LightSwitch HTML Client book

    http://LightSwitchHelpWebsite.com

    Sunday, March 9, 2014 4:39 AM
  • Based on Xpert360's comments about the direction of the relationship (and cardinality), here's what worked for me:

            Private Sub QRYMaxRank_PreprocessQuery(MaxRank As System.Nullable(Of Integer), ByRef query As System.Linq.IQueryable(Of LightSwitchApplication.Employee))
                'if a user supplied certification is selected from the list of 
                'certification in the certification table
                'then select ALL employees that have that certification
                'AND ALL employees that have a lesser certification
                'allows IC to generate a list of responders that 
                'contains anyone that is certified at level [q]
                'or below
                If MaxRank.HasValue Then
                    query = From q In query
                            Where q.SARCertifications.Rank <= MaxRank
                                                Select q
                    'at runtime the search results include all responders
                    'regardless of their certification, which an IC may want
                    'this is the default, if IC wants filter, then use picklist
                Else
                    query = From q In query
                            Select q
                End If
            End Sub

    "MaxRank" is an optional integer parameter that is added to "QRYMaxRank" on the employee table. 

    A search screen was created using "QRYMaxRank.

    A Local Property "SARCertifications(Entity) was added to the screen and renamed "AllCerts".

    The query parameter "MaxRank" was bound to AllCerts.Rank

    All Certs was added above the employees rows layout.

    At run time, the screen defaults to returning all employees regardless of their certification level. If a user selects from the picklist at the top of the screen, a list of employees is returned that either posses the certification selected in the pick list, or any other certification that is of a lesser rank than the selected item. 

    To clarify for others trying to follow this example, the user requirement here is that an Incident Commander needs to be able to generate a list of First Responders that posses a minimum level of training based on the type of incident. The training is hierarchical, so a responder that has achieved level "5" (e.g. EMT) is considered to meet the requirements of levels 1-4. Hope this all makes sense. 

    Not elegant at all, but gets the job done. Thanks for your answer!


    Sunday, March 9, 2014 5:38 PM