Filtering an Iqueryable object by a group of rules

Question

I've been filtetring an Iqueryable object by some rules, using successive Where clauses to do && filtering, how can i do the same for || filtering? Anyone has a suggestion?

Using another words, i want to apply for all rules || filtering.

I'm going to show a portion of the code to exemplify what im asking.

Where i get the pages object:

    public IQueryable<gbd_Pages> getAllPagesByOrderAndDir(int template_id, string name = "", int sortOrder = 3, string sortDirection = "asc")
    {
        IQueryable<gbd_Pages> Listpages;
        if (sortDirection == "asc")
        {
            Listpages = from p in _db.gbd_Pages 
                        from pc in (from c in p.gbd_Content
                                    where c.IsActive == true && c.IsDeleted == false && c.Template_Id == template_id
                                    && !string.IsNullOrEmpty(name) ? c.Content.ToLower().Contains(name.ToLower()) : true
                                    && c.gbd_Template_Fields.SortOrder == sortOrder
                                    select c).Take(1)
                        orderby pc.Content ascending
                        where p.IsActive == true && p.IsDeleted == false
                        select p;
        }
        else
        {
            Listpages = from p in _db.gbd_Pages 
                        from pc in (from c in p.gbd_Content
                                    where c.IsActive == true && c.IsDeleted == false && c.Template_Id == template_id
                                    && !string.IsNullOrEmpty(name) ? c.Content.ToLower().Contains(name.ToLower()) : true
                                    && c.gbd_Template_Fields.SortOrder == sortOrder
                                    select c).Take(1)
                        orderby pc.Content descending
                        where p.IsActive == true && p.IsDeleted == false
                        select p;
        }
        return Listpages;
    }

The function where i do the filtering. Actually it's only doing && filtering for all rules:

    public IEnumerable<gbd_Pages> PagesFiltered(List<Rule> newRules, string search, int sortColumnId, string sortColumnDir, out int count)
    {
        GBD.FrontOffice.Controllers.SegmentsController segmentCtrl = new GBD.FrontOffice.Controllers.SegmentsController();
        var Pages = controller.getAllPagesByOrderAndDir(1, search, sortColumnId, sortColumnDir);

        if (newRules.Count > 0)//FILTRA A LISTAGEM DE ACORDO COM AS REGRAS
        {
            foreach (var rule in newRules)
            {
                if (rule.Value!=null)
                {
                    gbd_Template_Fields templateField = controller.getTemplateFieldById(1, rule.Template_Field_Id);
                    gbd_Segment_Operators segOperator = segmentCtrl.getOperatorById(rule.Operator_Id);
                    if (segOperator.Value == "==")//IGUALDADE
                        if (rule.Value.Trim() != "")
                        {
                            if (templateField != null && templateField.Field_Id == 3) //tipo data
                            {
                                try
                                {
                                    DateTime test_datetime = DateTime.Parse(rule.Value); // testa se a data introduzida é valida
                                    Pages = Pages.Where(p => p.gbd_Content.Any(c => c.Templates_Fields_Id == rule.Template_Field_Id && c.IsActive == true && c.IsDeleted == false && (!string.IsNullOrEmpty(c.Content) && c.Content.Trim() == rule.Value.Trim())));
                                }
                                catch (Exception ex)
                                {
                                }
                            }
                            else if (templateField != null && templateField.Field_Id == 12 && !string.IsNullOrEmpty(rule.Value))//Lista múltipla de valores
                            {
                                rule.Value = rule.Value.Replace(',', '|');
                                Pages = Pages.Where(p => p.gbd_Content.Any(c => c.Templates_Fields_Id == rule.Template_Field_Id && c.IsActive == true && c.IsDeleted == false && (!string.IsNullOrEmpty(c.Content) && c.Content == rule.Value)));
                            }
                            else
                                Pages = Pages.Where(p => p.gbd_Content.Any(c => c.Templates_Fields_Id == rule.Template_Field_Id && c.IsActive == true && c.IsDeleted == false && (c.Content != null && c.Content.ToLower().Trim() == rule.Value.ToLower().Trim())));
                        }
                        else
                        {
                            Pages = Pages.Where(p => !p.gbd_Content.Any(c => c.Templates_Fields_Id == rule.Template_Field_Id) || p.gbd_Content.Any(c => c.Templates_Fields_Id == rule.Template_Field_Id && c.IsActive == true && c.IsDeleted == false && (c.Content == null || c.Content.ToLower().Trim() == rule.Value.ToLower().Trim())));
                        }
                    else if (segOperator.Value == ">")//MAIOR QUE
                    {
                        if (templateField != null && templateField.Field_Id == 3) //tipo data
                        {
                            try
                            {
                                DateTime test_datetime = DateTime.Parse(rule.Value); // testa se a data introduzida é valida
                                Pages = Pages.Where(p => p.gbd_Content.Any(c => c.Templates_Fields_Id == rule.Template_Field_Id && c.IsActive == true && c.IsDeleted == false && (!string.IsNullOrEmpty(c.Content) && (c.Content.Substring(6, 4).CompareTo(rule.Value.Trim().Substring(6, 4)) > 0 || c.Content.Substring(6, 4).CompareTo(rule.Value.Trim().Substring(6, 4)) == 0 && c.Content.Substring(3, 2).CompareTo(rule.Value.Trim().Substring(3, 2)) > 0 || c.Content.Substring(6, 4).CompareTo(rule.Value.Trim().Substring(6, 4)) == 0 && c.Content.Substring(3, 2).CompareTo(rule.Value.Trim().Substring(3, 2)) == 0 && c.Content.Substring(0, 2).CompareTo(rule.Value.Trim().Substring(0, 2)) > 0))));
                            }
                            catch (Exception ex)
                            {
                            }
                        }
                        else if (templateField != null && templateField.Field_Id == 5)//tipo número
                            Pages = Pages.Where(p => p.gbd_Content.Any(c => c.Templates_Fields_Id == rule.Template_Field_Id && c.IsActive == true && c.IsDeleted == false && (c.Content != null && c.Content != "" && (c.Content.Trim().CompareTo(rule.Value.Trim()) > 0 || (rule.Value.Trim().StartsWith("-") && c.Content.Trim().CompareTo(rule.Value.Trim()) > 0 || (c.Content.Trim().CompareTo("0") > 0))))));//procurar outra solução https://stackoverflow.com/questions/7740693/big-issue-in-converting-string-to-datetime-using-linq-to-entities
                        else if (templateField != null && templateField.Field_Id == 6) //tipo telefone  //ESTA CONDIÇÃO NÃO FOI ADICIONADA AINDA A BD
                        {
                            try
                            {
                                Pages = Pages.AsEnumerable().Where(p => p.gbd_Content.Any(c => c.Templates_Fields_Id == rule.Template_Field_Id && c.IsActive == true && c.IsDeleted == false && (c.Content != null && c.Content != "" && (Convert.ToInt32(c.Content.Split(' ')[1]) > Convert.ToInt32(rule.Value))))).AsQueryable();//TODO: procurar outra solução
                            }
                            catch (Exception ex) { }
                        }
                    }

many more rules...

                    else if (segOperator.Value == "StartsWith")//COMEÇA COM
                    {
                        if (rule.Value.Trim() != "")
                            Pages = Pages.Where(p => p.gbd_Content.Any(c => c.Templates_Fields_Id == rule.Template_Field_Id && c.IsActive == true && c.IsDeleted == false && (c.Content != null && c.Content.ToLower().Trim().StartsWith(rule.Value.ToLower().Trim()))));
                    }
                    else if (segOperator.Value == "EndsWith")//ACABA COM
                    {
                        if (rule.Value.Trim() != "")
                            Pages = Pages.Where(p => p.gbd_Content.Any(c => c.Templates_Fields_Id == rule.Template_Field_Id && c.IsActive == true && c.IsDeleted == false && (c.Content != null && c.Content.ToLower().Trim().EndsWith(rule.Value.ToLower().Trim()))));
                    }
                }
            }
        }
        count = Pages.Select(d => d.Id).Count();
        return Pages;
    }

Consider using LINQKit? It has tools to combine expressions dynamically then apply them in the `Where`. — NetMage, Nov 28 '17 at 21:50
This looks like a good candidate for [Dynamic LINQ](https://www.nuget.org/packages/System.Linq.Dynamic/). But another approach is to gradually build the predicate expression first and when it's finished use it to filter `Pages`. — Gert Arnold, Nov 28 '17 at 21:53

sfuqua · Answer 1 · 2017-11-28T22:07:34.407

0

The Where statement is implicitly using AND in all cases. To get an OR statement equivalent, you'll need to use the double pipe operator (||) inside of a Where statement with something like:

.Where(p => p.A == 'b' || p.C == 'd')

Unfortunately there is no simple way to add successive optional statements the .NET framework. It would be great to have something like .Where(p => p.A == 'b').Or(p => p.C == 'd') but generating a query from this seems impractical - if there was an another Where statement, then how would Linq know how to group it relative to the other two? (A || B && C) versus ((A || B) && C).

That said, there is an external library (LINQKit) that can help fill the gap. See Dynamic where clause (OR) in Linq to Entities for reference.

edited Nov 28 '17 at 22:07

answered Nov 28 '17 at 21:51

sfuqua

5,797
1
32
33

*there is no way to add successive optional statements* There is when using a predicate builder. – Gert Arnold Nov 28 '17 at 21:57
Predicate builder is still inserted into a Where clause though. But I'm rethinking this. – sfuqua Nov 28 '17 at 22:02
1

Yes, but the predicate can be a chain of `or` conditions: https://stackoverflow.com/a/14622200/861716. – Gert Arnold Nov 28 '17 at 22:04
Right, it is an external library. I was thinking about this from the native .NET standpoint. Still, I'll update my answer. Thank you for the contribution @GertArnold. – sfuqua Nov 28 '17 at 22:05

score 0 · Answer 2 · answered Nov 28 '17 at 22:11

You can work around the limitations of linq by just adding all the results into a List<>

List<gbd_Pages> allPages = new List<gdb_Pages>();
foreach (var rule in newRules)
{
    IEnumerable<gbd_Pages> rulePages;

    // current logic (except you need to not overwrite the Pages variable)
    // instead of the pattern "Pages = Pages.Where( ..." use "rulePages = Pages.Where( ..."

    allPages.AddRange(rulePages);
}

return allPages;

Filtering an Iqueryable object by a group of rules

2 Answers2