Hi,

I am trying to implement your predicate filtering for our WinForm application. We are using LLBLGEN 4.1. We have a form that has a datagridview to display a list of questions. A question has a DisplayAs text field for a short string to be displayed in the grid. A question has a body, which is the full question text. A question has a Context, which is foreign key related field. It describes a broad category to which the question applies (i.e. Legal, Technical). A question can have many keywords associated with it. Finally, a question can have many Responses.

I cannot figure out how to go from a PredicateExpression() to executing the query to get the results, so I am hoping that you can give me a nudge in the correct direction. Here is the relevant code... I've attached an image of the filtering panel in case it may be useful.

In my Filtering button click handler, I have the following code. First, I build the filtering query, and then I want to execute the query to get the collection of questions that meet the filtering criteria. Finally, I assign that collection to the dataviewgrid for display.

                // FILTERING BUTTON HANDLER...
                // Build the filtering query and do it!
                var filterQuery = BuildFilterQuery(
                    questionDisplayAs, andOr1,
                    questionBody, andOr2,
                    responseDisplayAs, andOr3,
                    responseBody, andOr4,
                    contexts, andOr5,
                    keywords
                    )
                    .WithPath(p=>p.Prefetch(c=>c.Context)
                    .Prefetch(qk=>qk.QuestionsKeywords)
                    .Prefetch(r=>r.Responses))
                    .OrderBy(q => q.DisplayAs)
                    .AsQueryable();

                var questions = filterQuery.Execute<EntityCollection<QuestionEntity>>();

                questionsDataGridView.DataSource = null;
                questionsDataGridView.DataSource = questions;

    // THE FOLLOWING METHOD CREATES THE QUERY FILTER TO BE USED IN THE ABOVE CODE...
    // Starting with simple predicate filtering and then I will implement the other filtering
    // criteria.
    IQueryable<QuestionEntity> BuildFilterQuery(
        string questionDisplayAs, string andOr1, 
        string questionBody, string andOr2,
        string responseDisplayAs, string andOr3,
        string responseBody, string andOr4,
        List<ContextDTO> contexts, string andOr5,
        List<KeywordDTO> keywords)
    {
        /*
                   IMPLEMENTING THIS QUERY, BUT WE WANT TO BE ABLE TO CHANGE THE AND/OR operators through code...
                    var filterQuery =
                    (
                        from question in metaData.Question

                        where
                            // Questions
                            question.DisplayAs.Contains(questionDisplayAs) ||
                            question.Body.Contains(questionBody) ||

                            // Responses
                            question.Responses.Any(r => r.DisplayAs.Contains(responseDisplayAs) ||
                                            r.Body.Contains(responseBody)) ||

                            // Contexts
                            //question.Context.Id == contextId ||

                            // Keywords
                            question.QuestionsKeywords.Any(qk => qk.Id == keywordId)
                        select new QuestionDTO
                        {
                            Id = question.Id,
                            DisplayAs = question.DisplayAs
                        }
                    )
                    .WithPath(p=>p.Prefetch(c=>c.Context)
                    .Prefetch(qk=>qk.QuestionsKeywords)
                    .Prefetch(r=>r.Responses))
                    .OrderBy(q => q.DisplayAs)
                    .AsQueryable();
         */

        IPredicateExpression filter = new PredicateExpression();

        // Set the initial search field, which may be empty: QuestionDisplayAs
        if (questionDisplayAs.Length > 0)
        {
            filter.Add(new FieldFullTextSearchPredicate(
                QuestionFields.DisplayAs, null,
                FullTextSearchOperator.Contains, questionDisplayAs));
        }       

        // Add the QuestionBody filter
        if (andOr1.Length == 0)
        {
            // Not set, so ignore the QuestionBody field.
        }
        else if (andOr1.Equals("or"))
        {
            // QuestionDisplayAs || QuestionBody
            filter.AddWithOr(new FieldFullTextSearchPredicate(
                QuestionFields.Body, null,
                FullTextSearchOperator.Contains, questionBody));
        }
        else
        {
            // QuestionDisplayAs && QuestionBody
            filter.AddWithAnd(new FieldFullTextSearchPredicate(
                QuestionFields.Body, null,
                FullTextSearchOperator.Contains, questionBody));
        }

        /*******************************************************
         * Okay, I hve my filter predicate expression, how do I return the IQueryable
         * to the filtering button click handler, so the query will be executed and
         * the list of questions can be displayed in the datagridview?
         *******************************************************/           

        //EntityCollection<QuestionEntity> questions = new EntityCollection<QuestionEntity>();
        //IEntityView2 filteredQuestions = new EntityView2<QuestionEntity>(questions, filter);

        IQueryable<QuestionEntity> query = null;

        //using (var adapter = new DataAccessAdapter())
        //{
        //  var metaData = new LinqMetaData(adapter);
        //  query = metaData.Question.Where(predicate);
       // }

        return query;
    }

Here is an image of the filtering panel.

Could you please explain what exactly is going wrong? What's the query you need to produce and what are you getting?

Hi Walaa,

I am new to your predicate filtering functionality, so I am trying to implement two simple filters: 1) To detect whether a user entered value is in any of the Questions' DisplayAs text field; 2) To detect whether a user entered value is in any of the Questions' Body text field; 3) Implementing and/or for these two filtering fields.

Right now, what I cannot figure out is how to use the PredicateExpression() that I build in BuildFilterQuery() so that method returns an IQueryable that I can use to get the filtered list of questions from the following code. I'd appreciate your help with this issue. Once I get it working, then I can expand my predicate filter to handle more complex cases.

                var filterQuery = BuildFilterQuery(
                    questionDisplayAs, andOr1,
                    questionBody, andOr2,
                    responseDisplayAs, andOr3,
                    responseBody, andOr4,
                    contexts, andOr5,
                    keywords
                    )
                    .WithPath(p=>p.Prefetch(c=>c.Context)
                    .Prefetch(qk=>qk.QuestionsKeywords)
                    .Prefetch(r=>r.Responses))
                    .OrderBy(q => q.DisplayAs)
                    .AsQueryable();

                var questions = filterQuery.Execute<EntityCollection<QuestionEntity>>();

                questionsDataGridView.DataSource = null;
                questionsDataGridView.DataSource = questions;

The initial message has the BuildFilterQuery() method, which is a simplified version that only does the filtering that I've described...

You are using LINQ2LLBL, but the filter is dynamic (you might or might not filter on some fields). So, to do that with Linq you should use PredicateBuilder, see http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=14224

You also could use the LLBLGen API, in this case, it's maybe a good choice. For instance:

public IRelationPredicateBucket BuildFilter(string questionDisplayAs, string andOr1,
    string questionBody, string andOr2,
    string responseDisplayAs, string andOr3,
    string responseBody, string andOr4,
    List<string> contexts, string andOr5,
    List<string> keywords)
{
    var filter = new RelationPredicateBucket();

    // DisplayAs
    if (!string.IsNullOrEmpty(questionDisplayAs))
    {
        var toAdd = new FieldFullTextSearchPredicate(
            QuestionFields.DisplayAs, null,
            FullTextSearchOperator.Contains, questionDisplayAs);
        filter.PredicateExpression.Add(toAdd);
    }

    // Body
    if (!string.IsNullOrEmpty(questionBody))
    {
        var toAdd = new FieldFullTextSearchPredicate(
                            QuestionFields.Body, null,
                            FullTextSearchOperator.Contains, questionBody);

        if (andOr1 == "or")
        {
            filter.PredicateExpression.AddWithOr(toAdd);
        }
        else
        {
            filter.PredicateExpression.AddWithAnd(toAdd);
        }
    }
            
    // do the same with other fields...
    // ...

    return filter;      
}

public void ClickHandler()
{
     var filter = BuildFilter(// params from GUI);
     var questions = new EntityCollection<QuestionEntity>();
     using (var adapter = new DataAccessAdapter())
    {
          adapter.FetchEntityCollection(questions, filter, ....);
    }

     questionsDataGridView.DataSource = questions;
}