1

I have a custom application where the user can select multiple (13) parameters to select data from a SharePoint list. To achieve this, I want to use LINQ to query this data from SharePoint.

The enduser can select from different dropdowns to filter. I need to build a dynamic query for these "filters".

I know how to achieve this by CAML but due to the large amount of filters, its highly complicated to build a CAML with possible 13 AND's.

I want to use LINQ to SharePoint but you are not able to create dynamic WHERE clauses.

Is it possible to create dynamic WHERE clauses and if yes how?

4
  • It is possible to build dynamic using CAML. never used LINQ. If want CAML, I can help
    – Gaurravs
    Commented Nov 10, 2015 at 12:14
  • @Gauras. Ok, thanks a lot! You have 13 possible <eq>'s due to selected filters. But when the users selects only 2 filters, then you will have 2 EQs. If the users selects all 13 filters, then you will have 13 EQs. My problem is to create out of these n to 13 filter a dynamic CAML all linked with ANDs. Could you show me a way to build such a dynamic CAML query with n EQs?
    – BOG
    Commented Nov 10, 2015 at 12:52
  • Use Camlex to build dynamic query: camlex.codeplex.com Commented Nov 10, 2015 at 13:26
  • ok, can you tell me how you storing the user selected data? So I can create a sample code and share with you. Also Which type of columns are all those? All Choice column or anything else
    – Gaurravs
    Commented Nov 10, 2015 at 17:12

2 Answers 2

0

Considering that field1Value is the 1st filter control's value which is available on your form as so on. Use the second if block n number of times as you want. (12 in your case)

bool indicator = false;
string query = String.Empty;

if(!String.IsNullOrEmpty(field1Value))
query +="<Eq><FieldRef Name='columnName1'/><Value Type='columnType'>field1Value</Value></Eq>"

if(!String.IsNullOrEmpty(field2Value)){
  if(!String.IsNullOrEmpty(query)){    
     indicator = true;
   }
query +="<Eq><FieldRef Name='columnName2'/><Value Type='columnType'>field2Value</Value></Eq>"
   if(indicator){
     query = "<And>" + query + "</And>";
     indicator = false;
   }  
}


if(!String.IsNullOrEmpty(field3Value)){
  if(!String.IsNullOrEmpty(query)){    
     indicator = true;
   }
query +="<Eq><FieldRef Name='columnName3'/><Value Type='columnType'>field3Value</Value></Eq>"
   if(indicator){
     query = "<And>" + query + "</And>";
     indicator = false;
   }  
}

if(!String.IsNullOrEmpty(field4Value)){
  if(!String.IsNullOrEmpty(query)){    
     indicator = true;
   }
query +="<Eq><FieldRef Name='columnName4'/><Value Type='columnType'>field4Value</Value></Eq>"
   if(indicator){
     query = "<And>" + query + "</And>";
     indicator = false;
   }  
}

if(!String.IsNullOrEmpty(field5Value)){
  if(!String.IsNullOrEmpty(query)){    
     indicator = true;
   }
query +="<Eq><FieldRef Name='columnName5'/><Value Type='columnType'>field5Value</Value></Eq>"
   if(indicator){
     query = "<And>" + query + "</And>";
     indicator = false;
   }  
}

As final step you can do the following:

query = "<Where>"+query + "</Where>";

Mark it as answer if works for you. Someone might be looking for similar post, it would be beneficial for them too

0

I found the solution. Thanks to Pawel: https://buli.waw.pl/caml-query-multiple-conditions-in-and-or-clause/

He has provided a small codesnippet for dynamic CAML building which works fine.

enum MergeType { Or, And };

/// <summary>
/// Merge CAML conditions in code
/// </summary>
/// <param name="conditions">A list of contitions to merge into alternative or conjunction</param>
/// <param name="type"><value>MergeType.Or</value> for alternative, MergeType.And for conjunction</param>
/// <returns></returns>
private static string MergeCAMLConditions(List<string> conditions, MergeType type)
{
    // No conditions => empty response
    if (conditions.Count == 0) return "";

    // Initialize temporary variables
    string typeStart = (type == MergeType.And ? "<And>" : "<Or>");
    string typeEnd = (type == MergeType.And ? "</And>" : "</Or>");

    // Build hierarchical structure
    while (conditions.Count >= 2)
    {
        List<string> complexConditions = new List<string>();

        for (int i = 0; i < conditions.Count; i+=2)
        {
            if (conditions.Count == i + 1) // Only one condition left
                complexConditions.Add(conditions[i]);
            else // Two condotions - merge
                complexConditions.Add(typeStart + conditions[i] + conditions[i + 1] + typeEnd);
        }

        conditions = complexConditions;
    }

    return conditions[0];
} 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.