locked
Linq query join RRS feed

  • Question

  • Hi all

    My problem:

    I have two entities

    Departement:ID; Name

    Orders:ID; Name; Order_Departement

    An order has one department.

    I want to implement a filter on the list of orders, which can filter by various departments.

    For this I have a string with the name of the various departments to filter, with which I build a list of strings with the departments (lstDepart) to process in the PreprocessQuery.

    how can I build the query?

    I tried unsuccessfully

    query = (from q in query
                                 where lstDepart.Contains(q.Department.Name)
                                 select q);

    (I think being in the opposite direction).

    Thanks


    Thursday, June 6, 2013 9:01 PM

Answers

All replies

  • Hello

    I am slightly confused by this but fancy a challenge :)

    Are these tables related already in SQL? If they are not and you have control over that data then that is where I would start.

    If you do not and you have constructed the string list correctly for your lstDepart comparison the syntax *should* be the following:-

    query = from q in qurey where lstDepart.Contains(q.Order_Department) select q;

    The confusing part is if your example is correct I think the tables are linked, hence your q.Department.Name reference.

    Come back to us with that info and we will see what we can do from there


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Friday, June 7, 2013 9:41 AM
  • Hi Otis,

    Thanks for the reply

    Yes, the tables are related. My first attempt was as you say 

    query= (from q in query where lstDepart.Contains(q.Order_Department.Name)                              select q);

                                       

    But in this case, returns only the departments like the first list entry.

    Friday, June 7, 2013 1:38 PM
  • If the tables are already linked why are you trying to do the filtering within the PreProcessQuery?

    I personally would create a new query based on the Orders table and add a filter to there. As the tables are linked you will be able to do the query easily.

    If you need a walkthrough on that let me know


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Friday, June 7, 2013 2:04 PM
  • Sorry, I don't see how.

    I want to get all the Orders of several departments.
    The relationship is:

    an Order has one Department and one Department is in Several orders.

    My input string to the filter is one list (example [Depart01, Depart02])

    And I intend to get the list of orders from departments  [Depart01, Depart02].

    How would you do?

    Thanks

    Friday, June 7, 2013 2:18 PM
  • How are you going to be selecting which departments to bring back?

    If you have just one list how are you selecting more than one item in order to do the multi select?

    Within the query itself you can do OR conditions and have more than one selection item. i.e.

                     order department= field1
                             OR
                     order department= field2
                             OR
                     order department= field3

    but without knowing how you are selecting the department or order or what sort of volume it's hard to suggest


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Friday, June 7, 2013 2:31 PM
  • Here is another way to do what you want.  This is an example of a preprocess query that builds a list based on another table and then uses Contains to filter the query. 

    In this example, I have a Transactions table that has an TranType field.  I want to filter the Transactions table so that only certain TranType values show.  There is a separate TransactionTypes table that I query first to build a list.  The main query then is filtered to show only Transactions that contain TranTypes in the list.

     partial void queryTransactionData_PreprocessQuery(int? ClientId, DateTime? TransactionStartDate, DateTime? TransactionEndDate, ref IQueryable<TransactionData> query)
            {
                Microsoft.LightSwitch.IDataServiceQueryable<LightSwitchApplication.TransactionType>
                 types = from t in DataWorkspace.ApplicationData.TransactionTypes
                         where t.TranCategory == 1        // Receipts
                         select t;
                List<string> tranTypes = new List<string>();
                foreach (TransactionType t in types)
                {
                    tranTypes.Add(t.TranType);
                }
                query = from q in query
                        where tranTypes.Contains(q.TranType)
                        orderby q.TranDate
                        select q;
            }
    Mark
    Friday, June 7, 2013 2:57 PM
  • Hummm

    My Department list is dynamic;

    I have one grid with multi-selection....

    Friday, June 7, 2013 2:58 PM
  • I have one grid with multi-selection....


    Is this a custom control by any chance? I know that LS master Yann had done a multiselect grid but i dont know how you would get the results back from that.

    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Friday, June 7, 2013 3:29 PM
  • Hi mwamoreira,

    Take a look in this post:

    Search query for multiple string values within the same property


    Norman

    • Proposed as answer by Otis Ranger Monday, June 10, 2013 8:37 AM
    • Marked as answer by Angie Xu Wednesday, June 26, 2013 8:36 AM
    Friday, June 7, 2013 4:16 PM
  • Great post from Norman, looks to do exactly what you need. +1 from me.


    If you found this post helpful, please mark it as helpful. If by some chance I answered the question, please mark the question as answered. That way you will help more people like me :)

    Monday, June 10, 2013 8:38 AM