Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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!

share|improve this question

5 Answers 5

up vote 2 down vote accepted

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.

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

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 });
share|improve this answer

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
{
    book.Title,
    book.Author.FirstName,
    book.Author.SecondName
};

, 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.

share|improve this answer

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

 var queryUsers = from person in osobyItems.ToList()
 join
 plannedPosition in pmItems
 on
 person.PlánovanéMístoId equals plannedPosition.Id
 where
 plannedPosition.Kategorie == "Z1"
 select new { login = person.Login };
share|improve this answer
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 - SharePoint MVP Apr 12 '11 at 6:08
    
Absolutely correct, Wouter! Since ToList() has been called, Linq-to-objects is starting, not Linq-to-SharePoint... –  Andrey Markeev Apr 13 '11 at 14:29
1  
Oh lovely - thanks guys for the heads up, nice looking code - performance is going to suck! –  JL01 Apr 14 '11 at 7:27

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})
.Where(ab=>column2==7)
share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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