More Questions Linq And CAML Query

How to Query List using Linq?


// Get DataContext from page context

DataContext data = new DataContext(SPContext.Current.Web.Url);
// Get the SharePoint list

EntityList Customers = data.GetList("Customers");

// Query for customers from London

var londonCustomers = from customer in Customers

where customer.City == "London"

select customer;
foreach (var londonCust in londonCustomers)


Console.Writeline("id = {0}, City = {1}",




What are the list views ?

How to create the lookup columns

There are requirements to keep in mind when creating list joins.

You cannot join just any two lists, regardless of type.

And if two lists can be joined, not just any primary and foreign field can be used as the “join on” pair of fields.

The field in the primary list must be a Lookup type field and it must lookup to the field in the foreign list.

For this reason, all joins mirror existing lookup relations between lists.

How to define Join with the CAML queries?

The “join on” fields are identified by a pair of FieldRef elements.

The first represents the Lookup field in the primary list and identifies it by internal name.

It must have a RefType attribute set to “Id”. If the primary list of the join is not the parent list of the view, then it, too, is identified with a List attribute set to its alias.

The second FieldRef element of each pair identifies the foreign list, by alias again, and the foreign key field, which must always be the Id field.

What are the Projected Fields?

Fields from the foreign List

Joins and projected fields can also be used in CAML queries. In this use, also, the joins and projected fields are defined with Joins and ProjectedFields elements. However, the elements are not children of the Query element. They are independent XML markups that form the value of the SPQuery.Joins and SPQuery.ProjectedFields properties of the SPQuery object that represents the query.

It is usually best to use the LINQ to SharePoint Provider to query SharePoint Foundation lists with server code. Because CAML has the Joins and ProjectedFields elements, the LINQ to SharePoint Provider, which translates LINQ queries into CAML queries, can fully support the LINQ join (Join in Visual Basic) and select (Select in Visual Basic) operators. If your code is intended to run on clients, we recommend that you query by using SharePoint to ADO.NET Data Service.

If you want to create CAML queries directly and set the relevant properties of an SPQuery object explicitly, you should consider using a tool to generate the queries. To locate such tools, navigate to www.bing.com and search for “CAML query tool” without the quotation marks. It may be some time after the release of Microsoft SharePoint Foundation 2010 before there are any tools available that support the generation of Joins and ProjectedFields elements.

The following example shows a query that returns all orders from an Orders list where the customer’s city is London. The example assumes that the Orders list has a CustomerName field that looks up to a Customers list and that the latter list has a CityName field that looks up to a Cities list.
The query requires a join of Orders to Customers and from Customers to Cities, so the value of the Joins property would be the following.

Because the Where section of our query is going to test the customer’s city, we need to create a CustomerCity field. Hence, the value of the ProjectedFields is the following.




ShowField=’Title’ />

Next, we need to make the field available by adding a reference to it to the ViewFields element. Therefore, the value of the ViewFields property is the following.

Finally, the Query property is set to the following.



The following is an example of two Join elements; both are left outer joins. CustomerName is a lookup field on an Orders list. It looks up to the ID field of a Customers list. The Customer list, in turn, has a CityName field that is a lookup field to a Cities list. The first Join element assigns ‘customers’ as an alias for the Customers list. The Eq element child defines the join using the same source and target fields that constitute the lookup relation. The second Join element assigns ‘customerCities’ as an alias to the Cities list. It defines the join in parallel to the existing lookup relation between the Customer and Cities lists.

Aliases are valuable for the lists because there can be more than one join to the same list and different aliases are needed to differentiate the joins. For example, in addition to the joins from Orders to Customer and from Customer to Cities, there could also be joins from Orders to Suppliers and from Suppliers to Cities. In the case of the last join, a different alias, say ‘supplierCities’, would be assigned to the Cities list from the one that is used for the Customer to Cities join.


What is the Datacontext?


How to automate the creation of SPMetal.exe in prebuild command?


SPMetal /web:http://MyServer/Contoso /code:Contoso.%SPLANGEXT%

spaces in project path

cd $(ProjectDir)should becd "$(ProjectDir)"in case the project directory has spaces