Efficient Multi-List Queries in CSOM: Using CAML Joins with CAMLEX

The Problem: Multiple List Queries and Throttling

When working with related data across multiple SharePoint lists, developers often fall into the trap of making multiple individual queries:

// ❌ Bad approach - Multiple API calls
var customers = context.Web.Lists.GetByTitle("Customers");
var orders = context.Web.Lists.GetByTitle("Orders");
var orderItems = context.Web.Lists.GetByTitle("OrderItems");

// Each query consumes 2 resource units
var customerItems = customers.GetItems(camlQuery1);  // 2 units
var orderItems = orders.GetItems(camlQuery2);        // 2 units  
var itemDetails = orderItems.GetItems(camlQuery3);   // 2 units
// Total: 6 resource units + processing overhead

This approach has several issues:

Higher resource consumption: Each query consumes 2 resource units per multi-item request
Increased throttling risk: More API calls mean hitting limits faster
Network overhead: Multiple round trips to SharePoint
Complex data merging: Manual joining of results in C#

How to fix it?

I have worked alot with MSSQL where it is pretty easy to just join a table on a table on a table. But I did not know it was possible in CSOM. Eg. in the UI of SharePoint you can only expand one table – NOT multiple. But one day I deep dived into CAML and joins, and found out it was possible!

Resource Unit Comparison:

Let’s break down the actual cost difference. According to Microsoft’s throttling documentation, each multi-item query consumes 2 resource units.

Multiple separate queries (❌ Bad approach):

// Query 1: Get order items from main list
var orderItems = ordersList.GetItems(query1);        // 2 resource units

// Query 2: Get customer details  
var customers = customersList.GetItems(query2);      // 2 resource units

// Query 3: Get order details
var orderDetails = orderDetailsList.GetItems(query3); // 2 resource units

// Total: 6 resource units + network overhead + manual C# joining

Single join query (✅ Good approach):

// One query with joins gets ALL the data
var items = ordersList.GetItems(joinQuery);          // 2 resource units total

The math:

Multiple queries: 2 units × 3 lists = 6 units
Single join query: 2 units total
Savings: 66% fewer resource units!

This becomes even more significant when you consider that SharePoint throttling limits are measured in resource units per time window. With joins, you can query 3x more data within the same throttling limits.

The Solution: CAML Joins

CAML actually supports joining multiple lists in a single query! This means you can get data from related lists without multiple API calls.

First, install CAMLEX via NuGet:

Install-Package Camlex.Client.dll

Then build your join query:

var list = context.Web.Lists.GetByTitle("YourMainList");

CamlexNET.Interfaces.IQuery query = Camlex.Query();

query = query
.LeftJoin(x => x["OrderTaskLookUp"].ForeignList(ListGuidOrderTasks))
.LeftJoin(x => x["OrderDetailLookUp"].PrimaryList(ListGuidOrderTasks).ForeignList(ListGuidOrders))
.LeftJoin(x => x["CustomerLookUp"].PrimaryList(ListGuidOrders).ForeignList(ListGuidCustomers))
.ProjectedField(x => x["CustomerNo"].List(ListGuidCustomers).ShowField("CustomerNo"))
.ProjectedField(x => x["CustomerName"].List(ListGuidCustomers).ShowField("CustomerName"));

var camlQuery = new CamlQuery();
camlQuery.ViewXml = query.ToString();

var items = list.GetItems(camlQuery);
context.Load(items);
context.ExecuteQuery();

Important: Your lists need to be connected via lookup columns for joins to work!

Why CAMLEX Instead of Raw CAML?

I don’t write raw CAML myself – it’s verbose and error-prone. Instead, I use CAMLEX because:

Cleaner syntax: C# lambda expressions instead of XML
IntelliSense support: Catch errors at compile time
Easier for the next developer: Self-documenting code
Less mistakes: No more XML typos or missing tags

What else?

One thing is to join multiple lists, but to filter on a value 4 lists away – That is neat!

For example, filtering orders by the customer’s name, which is 2 lists away:

CamlexNET.Interfaces.IQuery query = Camlex.Query();

query = query
.Where(x => (string)x["CustomerName"] == "Contoso") // <---- Filtering on join field
.LeftJoin(x => x["OrderTaskLookUp"].ForeignList(ListGuidOrderTasks))
.LeftJoin(x => x["OrderDetailLookUp"].PrimaryList(ListGuidOrderTasks).ForeignList(ListGuidOrders))
.LeftJoin(x => x["CustomerLookUp"].PrimaryList(ListGuidOrders).ForeignList(ListGuidCustomers))
.ProjectedField(x => x["CustomerNo"].List(ListGuidCustomers).ShowField("CustomerNo"))
.ProjectedField(x => x["CustomerName"].List(ListGuidCustomers).ShowField("CustomerName"));

var camlQuery = new CamlQuery();
camlQuery.ViewXml = query.ToString();
var items = ordersList.GetItems(camlQuery);

This CAMLEX query automatically generates the following CAML XML – notice how complex the raw XML is compared to the clean C# syntax above:

<View>
  <Query>
    <Where>
      <Geq>
        <FieldRef Name="customerName" />
        <Value Type="Text">Contoso</Value>
      </Geq>
    </Where>
  </Query>
  <ViewFields>
    <FieldRef Name="Id" />
    <FieldRef Name="customerNo" />
    <FieldRef Name="customerName" />
  </ViewFields>
  <Joins>
    <Join Type="LEFT" ListAlias="0f0cfc71-1c6e-4fd4-b6f2-279d0e3862f4">
      <Eq>
        <FieldRef Name="OrderTaskLookUp" RefType="Id" />
        <FieldRef List="0f0cfc71-1c6e-4fd4-b6f2-279d0e3862f4" Name="Id" />
      </Eq>
    </Join>
    <Join Type="LEFT" ListAlias="ce57b9a2-5052-482c-a8c8-150c7d59ced3">
      <Eq>
        <FieldRef List="0f0cfc71-1c6e-4fd4-b6f2-279d0e3862f4" Name="OrderDetailLookUp" RefType="Id" />
        <FieldRef List="ce57b9a2-5052-482c-a8c8-150c7d59ced3" Name="Id" />
      </Eq>
    </Join>
    <Join Type="LEFT" ListAlias="e204ed0f-7c25-432f-9228-3eb438c527e2">
      <Eq>
        <FieldRef List="ce57b9a2-5052-482c-a8c8-150c7d59ced3" Name="CustomerLookUp" RefType="Id" />
        <FieldRef List="e204ed0f-7c25-432f-9228-3eb438c527e2" Name="Id" />
      </Eq>
    </Join>
  </Joins>
  <ProjectedFields>
    <Field 
      Name="customerNo" 
      Type="Lookup" 
      List="e204ed0f-7c25-432f-9228-3eb438c527e2" 
      ShowField="customerNo" 
    />
    <Field 
      Name="customerName" 
      Type="Lookup" 
      List="e204ed0f-7c25-432f-9228-3eb438c527e2" 
      ShowField="customerName"
    />
  </ProjectedFields>
</View>

Imagine having to write and maintain that XML manually! This is exactly why CAMLEX is so valuable – you get all the power of CAML joins with readable C# syntax.

Performance Benefits

Before (Multiple queries):

🔴 6+ resource units
🔴 Multiple network calls
🔴 Complex C# merging logic

After (Single join):

✅ 2 resource units only
✅ One network call
✅ Server-side joining

Key Takeaways

Use joins instead of multiple queries to reduce resource consumption
CAMLEX makes CAML readable for you and the next developer
You can filter on joined data even multiple lists away
SharePoint UI limitations ≠ API limitations – joins work even if the UI doesn’t show it

Common Issues & Solutions

❌ “List does not exist” error

// Use list GUIDs instead of names for reliability
.ForeignList(new Guid("12345678-1234-1234-1234-123456789012"))

❌ “Field not found” error

// Use internal field names, not display names
.ShowField("Title")        // ✅ Internal name
.ShowField("Customer Name") // ❌ Display name

❌ Join returns no data

Verify lookup columns exist and are properly configured
Check that you’re joining on the correct fields
Ensure the lookup field contains valid IDs

❌ “Cannot project this field type” error

Only specific field types can be included in ProjectedFields:

✅ Supported ProjectedFields types:

Calculated (treated as plain text)
ContentTypeId
Counter
Currency
DateTime
Guid
Integer
Note (one-line only)
Number
Text

❌ NOT supported in ProjectedFields:

Multi-line text fields
Rich text fields
Choice fields
Lookup fields (use joins instead)
User/Person fields
Managed metadata fields

💡 Pro tip: If you need data from unsupported field types, query them separately after getting your joined results.

TL;DR

Problem: Multiple SharePoint list queries consume 6+ resource units and risk throttling
Solution: Use CAML joins to get all data in one query (only 2 resource units)
Tool: CAMLEX makes CAML joins readable with C# lambda expressions
Benefit: 66% fewer resource units + no manual data merging
Surprise: You can filter on data 4 lists away in a single query!