none
Build Lambda Expression Tree over many tables at runtime for Linq RRS feed

  • Question

  • Hello there,

    in the following case I would like to build an linq expression tree covering 3 tables for filtering data

    based on the content of an DatagridViewRowsCollection ( having attribute to filter and searchValue )

    Description case :

    Tables:

    I do have 3 identity tables in linq to sql (.dbml); named:  Companies  / Notes / Categories with each one to many relationship.

    ex; 1 company has many Notes and ech note could have many categories

    Using the predicateBuilder class:

    public static class ExpressionTreeBuilder
     {
    
    
     public static Expression<Func<T, bool>> True<T> () { return f => true; }
     public static Expression<Func<T, bool>> False<T> () { return f => false; }
     
     public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr1 ,Expression<Func<T, bool>> expr2)
     {
     var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
     return Expression.Lambda<Func<T, bool>>(Expression.OrElse (expr1.Body, invokedExpr), expr1.Parameters);
     }
     
     public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2)
     {
     var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
     return Expression.Lambda<Func<T, bool>>(Expression.AndAlso (expr1.Body, invokedExpr), expr1.Parameters);
     }
    }

    The code i made so far :

     

     

     

     

    For instance :

    If i build a testquery 

    Companies.Company = bert  or Notes.Memo.contains(searchValue)

    the I get only the single companies.

    My goal is : give me all notes having a text like " meeting minutes" and return all fields / attributes from company, Note and Categorie  

    Problems I discovered :

    Because I build the expressionn tree  as follows ;

    Expression<Func<Company, bool>> criteria1;

    I could not use the combined data, like :

    var notes = from c in dc.Companies
            join n in dc.Notes on c.IdCompany equals n.Company        join na in dc.NoteActions on n.IdNote equals na.IdNote
            join ncat in dc.Categories on n.IdNote equals ncat.IdNote
             select new { c.IdCompan,y, c.Company, n.IdNote, n.Memo, n.Potential, na.Action, na.ActionTo, na.Email, Categorie = ncat.Category };
    

     because the type of notes is anymious or not known .

    Note ; i even tried the create a class , containing a List of objects with all needed attributes , but then I discovered problemd with comparing identities  c =c.Company.contains(SearchValue);

    maybe somebody knows an other approache ?

     

    Thanks for your help in advance

    Bye Bertoo

    public Object GetFilteredData(DataGridView dgSearch,string frmName)
      {
       Object obj= null;
       
    
         var tree = ExpressionTreeBuilder.False<Company>();
         int i = 0;
    
         // combining 3 tables Companies, Notes & Categories 
    
         var notes = from c in dc.Companies
            join n in dc.Notes on c.IdCompany equals n.Company        join na in dc.NoteActions on n.IdNote equals na.IdNote
            join ncat in dc.Categories on n.IdNote equals ncat.IdNote
             select new { c.IdCompan,y, c.Company, n.IdNote, n.Memo, n.Potential, na.Action, na.ActionTo, na.Email, Categorie = ncat.Category };
    
         DataGridViewRowCollection rwcol = dgSearch.Rows ;
    
         foreach (DataGridViewRow rw in rwcol ) // this datagridView contains the fieldName and related SearchValue for that field
         {
          Expression<Func<Company, bool>> criteria1;
    
          string searchValue = (string)rw.Cells[1].Value; // searchValue comming from datagridView
    
          switch ((string)rw.Cells[0].Value) // the field where we have to look foor de searchValue      {      
           case "Company": // from first table
    
            criteria1 = c => c.Company.Contains(searchValue);
             
            if (i == 0) // test -> if a expression tree can not start with ||
            {
             tree = criteria1;
            }
            else 
            {
             tree = tree.Or(criteria1);
            }
    
            break;
    
           case "Memo": // From second table Notes
            
           criteria1 = c => c.Notes.Any(n => n.Memo.Contains(searchValue));
           
           if (i == 0) // eerste assignment
            {
             tree = criteria1;
            }
            else 
            {
             tree = tree.Or(criteria1);
            }
            break; 
     
           case "Category": //from third table Categories
    
            criteria1 = c => c.Notes.AsQueryable<Note>().Any(n => n.NoteTypes.Any(nt => nt.NoteType1.Contains(searchValue)));
    
            
            if (i == 0) // eerste assignment
            {
             tree = criteria1;
            }
            else 
            {
             tree = tree.Or(criteria1);
            }
            break;
          }
          i+=1;
         }
         var result= dc.Companies.Where(tree);
    //because the expression tree was build on the master table companies in the datacontext dc.companies
    I do not know how I could get the other fields/attributes from form second en third tables (Notes & categories)
    Monday, September 6, 2010 8:07 PM

Answers

All replies

  • Hi,

     

    Based on my understanding, are you trying to dynamically load the corresponding Companies, Notes and Categories?   From your query, I think it will load all the qualified Company entities.   How do you want to load the attributes from the second and the third tables?  

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, September 7, 2010 6:25 AM
    Moderator
  • Hi Lingzhi !

    Thanks again for your reply.

    First question -> For construction of the expression tree -> no that's not necessary I beleive . In the above sample I was trying to apply an expression tree filter on the Inumerable "var notes" ( combination of 3 tables)  , but the object 'notes' is an anomynious type and not corresponding the identity  from the expression tree ...

    Second Question -> yes , for all the resulting records of my query , I would like to obtain all the attributes of the 3 tables together .

    Exactly as you are saying , after  querying I would only have Compnay identities and don't see a way to combine the related notes ( not all notes but the notes corresponding my expression tree query )

    By the way ..

    , in case I would have an question on a treated question in the forum ( having status "answered") in the past from you or somebody , what is the best way to have a reaction ? ( by posting new question or adding new question to the subject of the past ? )

    Nice evening

    Bertoo

    Tuesday, September 7, 2010 7:29 PM
  • Hi Bertoo,

     

    For the anonymous type, I think we can access the related entities via navigation properties instead of through a LINQ JOIN query.   Also, you can consider creating a view to represent the joined data from three tables.  

     

    BTW, if the thread is answered, you can open a new thread to discuss the new question and I recommend you refer the original thread in the new post.   Or if the new question is a follow-up question which is very related to the original one, you can just follow up the original thread.   Just my cents.  J

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, September 9, 2010 7:18 AM
    Moderator
  • Hi

    Ok , yes  I could navigate the related entities .

    Nevertheless I would like to come back to the initial goal:

    Considering the 3 tables ( master-detail ) Company  |1-M |Notes |1-M |Categories

    Having a closer look at the filter criteria at runtime :

    Based on the user input , I need to evaluate the target attribute to filter

    Case 1 ; if the attribute is an attribute coming from entity Notes , then I need to build the expression based on entity Notes

     Case 2 ;  if the attribute is an attribute coming from entity Category, then I need to build expression based on entity Category

    Then finally I should combine both expression together with the predicatebuilder class … but the final expression is containing  an expression based on Notes en one based on Categories, which I believe won’t work .

    Therefore I was thinking to apply recursive filtering. ( based on the results of first filter , I should used the filter result again for the next filtering  and  etc ..)( like you suggest , the company entity is available through navigation the objects.)

    Because I believe that filtering based on an expression tree , having expression based on different entities  won’t work , I am still wondering is there is not a solution on a recursive way (knowing filtering attribute is an variable which should be tested at runtime for applying to table A or B or C )

    Thanks for taking your time again

    Nice weekend !

    bertoo

     

    Friday, September 10, 2010 7:34 AM
  • Hi bertoo,

     

    I agree that recursive building the expression is a good idea.  Also, besides the PredicateBuilder, we recommend you another dynamic LINQ library for your references like:

    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

     

    Hope it is helpful when you dynamically build the lambda expressions.

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by bertoo Wednesday, September 15, 2010 7:09 PM
    Monday, September 13, 2010 9:34 AM
    Moderator
  • Thank you Lingzhi Sun !

    yes, This could help me find out a solution.

    Best regards Bertoo .  

     

    Wednesday, September 15, 2010 7:11 PM
  • Glad to hear that!  :-)

     

    Good day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, September 17, 2010 1:24 AM
    Moderator