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?

  • It is possible to build dynamic using CAML. never used LINQ. If want CAML, I can help – Gaurravs Nov 10 '15 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 Nov 10 '15 at 12:52
  • Use Camlex to build dynamic query: camlex.codeplex.com – Arsalan Adam Khatri Nov 10 '15 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 Nov 10 '15 at 17:12
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, privacy policy and cookie policy

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