Quick and effective way to do an inner join using LINQ to Sharepoint on 2 tables

Question

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!

score 2 · Answer 1 · answered Apr 11 '11 at 16:28

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.

score 2 · Accepted Answer · answered Apr 12 '11 at 09:57

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 });

score 1 · Answer 3 · answered Apr 12 '11 at 08:12

1

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)

answered Apr 12 '11 at 08:12

Rikalous

111
2

score 0 · Answer 4 · edited Apr 13 '17 at 12:41

0

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

edited Apr 13 '17 at 12:41

Community

1

answered May 16 '11 at 12:50

Anthony Graglia

216
1
3
15

score -2 · Answer 5 · answered Apr 11 '11 at 18:10

-2

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 };

answered Apr 11 '11 at 18:10

JL01

1,219
5
22
36

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 '11 at 06: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 07:27

Quick and effective way to do an inner join using LINQ to Sharepoint on 2 tables

5 Answers5