I can't seem to find a guide which explains how to do an inner join on lookup fields using LINQ. Most of the guides require converting the EntityLists to custom objects so you can join on non lookup fields.

Here is the SQL I want to try and replicate in Sharepoint LINQ:

Select [fields] from table1 AA inner join table2 BB
on AA.lookupfield = AA.Title/ID 
where BB.field = $inputFromUser

As you can see hardly what I would call rocket science. But I'm having a miserable time locating code samples for LINQ and Sharepoint.

Can someone please post a working code sample.

Thank you!

5 Answers 5


There is no need to convert the EntityLists to custom objects to be able to do joins, as long as you have lookups to join on since this is the key for doing joins in LINQ. In my demo fragments below I have few lists (Country, Supplier, Product, ProductCategory) with lookups between these lists.

using (var context = new DemoTeamSiteDataContext(SPContext.Current.Web.Url))
    // get entity list
    EntityList<SuppliersItem> suppliers = context.GetList<SuppliersItem>("Suppliers");

    // retrieve list of distinct country titles over the supplier->country lookup
    var query = suppliers.Select(s => s.Country.Title).Distinct();

    // or in sql syntax
    query = (from s in suppliers
                select s.Country.Title).Distinct();

After you execute the actual query with a .ToList() or foreach iteration, you'll see following CAML generated if you output the context.Log property to a file. In this query you see that a join is created for you by LINQ without having to explicitly use the .Join() method.

        <FieldRef Name="ContentTypeId" />
        <Value Type="ContentTypeId">0x0100</Value>
    <FieldRef Name="CountryTitle" />
    <Field Name="CountryTitle" Type="Lookup" List="Country" ShowField="Title" />
    <Join Type="LEFT" ListAlias="Country">
      <!--List Name: Countries-->
        <FieldRef Name="Country" RefType="ID" />
        <FieldRef List="Country" Name="ID" />
  <RowLimit Paged="TRUE">2147483647</RowLimit>

Maybe another example with a bit more complexed join queries over multiple lookups. We start from the products list, query over 2 lookups to the country code of the supplier of the product and in the select we also retrieve the category title, which is yet another lookup to the Category list. You may notice I'm using the object oriented syntax over the sql syntax, but the same is possible in the sql syntax. Take the syntax you feel comfortable with.

var query = context.Products.Where(p => p.Supplier.Country.Code == "US")
    .Select(p => new { Title = p.Title, Supplier = p.Supplier.Title, 
            Category = p.Category.Title });

foreach (var product in query)
    Console.WriteLine(String.Format("Product: {0}\tSupplier:{1}\tCountry:{2}",
        product.Title, product.Supplier, product.Category));

Also note that I'm directly querying context.Products, which drops the extra step to create an EntityList object first and then query on that object like in the 1st example.

Your example would convert to something like:

// name parameter Table2Title to prevent name collision between both titles
var result = context.Table1
    .Where(a => a.LookupFieldToTable2.FieldInTable2 == inputFromUser)
    .Select(a => new { a.Title, a.SomeOtherField, Table2Title = a.LookupFieldToTable2.Title });

If you really have lookup field in your project, this is supported by Linq-to-SharePoint, but query logic is slightly different from SQL queries. But it is also not a rocket science, of course :) See the example below:

var result = from book in dataContext.Books
orderby book.Title
select new

, where Author is a lookup field in table Books.

So, there are two tables, Authors & Books, linked with lookup field, and this query will join them similar to SQL inner join.


Try somthing like this....

var result = tableA
.Join( tableB 
a => a.id,              //Join column for tableA
b => b.id,              //Join column for tableB
(a, b) => new {a.id, a.column1, b.column2})

If you want to use CAML: CAML query builder that supports inner joins?


I liked this rendition, because it is closely related to SQL:

 var queryUsers = from person in osobyItems.ToList()
 plannedPosition in pmItems
 person.PlánovanéMístoId equals plannedPosition.Id
 plannedPosition.Kategorie == "Z1"
 select new { login = person.Login };
  • 5
    DOesn't calling ToList destroy the idea of LINQ since it will download all items into list using effectively a select * and then filter in the business layer?
    – Wouter
    Apr 12, 2011 at 6:08
  • Absolutely correct, Wouter! Since ToList() has been called, Linq-to-objects is starting, not Linq-to-SharePoint... Apr 13, 2011 at 14:29
  • 1
    Oh lovely - thanks guys for the heads up, nice looking code - performance is going to suck!
    – JL01
    Apr 14, 2011 at 7:27

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.