Better way to get a SPListItem by Field

Question

I want to get a SPListItem from a SPList where a field match a value. I have to ways to do it, CAML and LINQ.

My code is:

LINQ

...
SPList list = Web.Lists["MyList"];
SPListItem item = (from SPListItem it in list.Items 
                    where it["A_Field"] == "A_Value" 
                    select it).First();
....

CAML

...
SPList list = Web.Lists["MyList"];
SPQuery query = new SPQuery();

query.Query = @"
    <Where><Eq>
        <FieldRef Name=""A_Field""/><Value Type=""Text"">A_Value</Value>
    </Eq></Where>";

SPListItem item = list.GetItems(query).Cast<SPListItem>().First();
....

I have read in some blogs (like this) that there is a performance issue with LINQ.

What is the better way to do it from scope of performance?

Answer 1

If you use LINQ, the code retrieves all records for a list from the content data base, in the case of CAML, CAML is performed to SQL query and retrieve data that match the criteria. For big data the CAML is the best choice.

if you do not familiar with CAML you can use Camlex.NET. It helps to build queries by using expression trees.

Answer 2

Definitely CAML is the fastest. If you use LINQ using your code above it will fetch all list items into memory first and then apply the linq filtering. If you have thousands of items then thats bad news...

Also, LINQ-to-Sharepoint is NOT normal linq like the code you've posted above. Linq-to-sharepoint maps content types to strongly-typed objects which you generate and then use a data context. Its similar to Entity framework except mapping to lists rather than sql tables but with many limitations. Sharepoint uses a linq provider to transform your linq-to-sharepoint queries to CAML queries.

If you hate CAML, use CAMLeX.net. Its the business.

Answer 3

LINQ-to-SharePoint simply turns your LINQ statement into CAML. (How to: View CAML Generated by LINQ to SharePoint) If you don't want the extra step of the conversion then use straight CAML. I think it's negligible.

The only exception is if you are doing large batch jobs...then I would stick to CAML. It's really powerful.