locked
Need Help to Write Query RRS feed

  • Question

  • Hi Everybody

    I have an entity called Employee where I have created a self relation called Manager (Similar design like Beth's Contoso Construction). Also one more entity called Task where the relation between Employee and Task is One to Many (An Employee can have many task). In Employee entity I have a property called UserName. All Employee are unique based on application user name.

    Now I need to filter the Task table (based on User Name) in such a way that a Manager can see all its corresponding employee task including himself/herself. But an employee can able to see his/her own task.

    Any help will be highly appreciated to implement this query. (C# Recommended)

    Thanks


    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.


    • Edited by babloo1436 Tuesday, March 26, 2013 4:27 PM
    Tuesday, March 26, 2013 4:24 PM

Answers

  • Hey Babloo,

    It works better if you reverse your logic (from "get the tasks of my employees" to "get the tasks of employees that have me as a boss").

    Or in code:

                string userName = "MyUserName"; //Todo: get current user username?
                query = query.Where( task => task.Employee.Username == userName ||  //My tasks
                    (task.Employee.Boss != null && task.Employee.Boss.Username == userName)); //Tasks where I'm the boss

    Keep rocking LS!

    Jan


    It's your story - time to switch on the innovation. || About me || LightSwitch blog

    • Marked as answer by babloo1436 Thursday, March 28, 2013 1:22 PM
    Thursday, March 28, 2013 9:27 AM

All replies

  • Anybody??

    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.

    Wednesday, March 27, 2013 11:41 AM
  • Try splitting the process into 2 parts. What I do is usually run a query to assemble a list or array of the Employee Id's relevant and then in the query for the tasks use an IN clause to match to the Employee ID. For most scenarios that works well.

    Here is a simplified example:

     

     

    partial void StudentsBySection_PreprocessQuery(int? SectionID, ref IQueryable<Student> query)
            {
                var dw = this.Application.CreateDataWorkspace();
                var enrolments = dw.ApplicationData.EnrolmentsBySection(SectionID);
    
                List<int> studentIds = new List<int>();
                foreach (Enrolment e in enrolments)
                {
                    studentIds.Add(e.Student.Id);
                }
    
                query = from q in query where studentIds.Contains(q.Id) select q;
    }


    Dave Baker | Xpert360 blog | twitter : @xpert360 Opinions are my own. Please mark as answer if this helps solve your problem.



    • Edited by Xpert360 Wednesday, March 27, 2013 2:46 PM
    Wednesday, March 27, 2013 2:35 PM
  • Hi Dave

    Thank you for your reply.

    Let me implement the code, I will get back to you on this ASAP.

    Thanks


    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.

    Wednesday, March 27, 2013 4:19 PM
  • Hey Babloo,

    It works better if you reverse your logic (from "get the tasks of my employees" to "get the tasks of employees that have me as a boss").

    Or in code:

                string userName = "MyUserName"; //Todo: get current user username?
                query = query.Where( task => task.Employee.Username == userName ||  //My tasks
                    (task.Employee.Boss != null && task.Employee.Boss.Username == userName)); //Tasks where I'm the boss

    Keep rocking LS!

    Jan


    It's your story - time to switch on the innovation. || About me || LightSwitch blog

    • Marked as answer by babloo1436 Thursday, March 28, 2013 1:22 PM
    Thursday, March 28, 2013 9:27 AM
  • Hi Jan

    Thank you very much. Its working fine.

    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.

    Thursday, March 28, 2013 1:22 PM