Saturday, 27 November 2010

Linq to SharePoint - CRUD operations

Problem: Code to perform CRUD operations against a SharePoint list using Linq to SharePoint.
Resolution:
1) Read:
DataContext dc = new DataContext();
EntityList Customer = dc.GetList("Customers");
var CustomerItems = from Customers in Customer
select Customers;

Alternative approach


2) Insert:
DataContext dc = new DataContext();
CustomersItem Customer = new CustomersItem();
Customer.Title = "Title";
dc.Customers.InsertOnSubmit(Customer);
dc.SubmitChanges();

3) Update:
DataContext dc = new DataContext();
EntityList Customer = dc.GetList("Customers");
var CustomerItems = (from Customers in Customer
where Customers.Id == Convert.ToInt32(txtEditCustomerId.Text)
select Customers).Single();
CustomerItems.Title = CustomerItems.Title + txtCustomerTitle.Text;
dc.SubmitChanges();

4) Delete:
DataContext dc = new DataContext();
EntityList Customer = dc.GetList("Customers");
var CustomerItems = (from Customers in Customer
where Customers.Id == Convert.ToInt32(txtEditCustomerId.Text)
select Customers).Single();
dc.Customers.DeleteOnSubmit(CustomerItems);
dc.SubmitChanges();

Alternative approach 


Tip: If you are using read-only operation in your LINQ to SharePoint queries set ObjectTrackingEnabled to false to improve performance.
DataContext dc = new DataContext();
dc.ObjectTrackingEnabled = false;

Tip: Use the context on the generated proxy for performing LINQ to SharePoint queries.
More Info:
LINQ to SharePoint: CRUD operation on SharePoint 2010 list using SPLinq
LinqPad
LinqPad for SharePoint 2010

5 comments:

Raymund said...

Great article but I have a question on InsertOnSubmit.

What if you have a Lookup field on Customer List which is for example CustomerOccupation that looks up on CustomerOccupation List how would I do this now?

DataContext dc = new DataContext();
CustomersItem Customer = new CustomersItem();
Customer.Title = "Title";
Customer.Occupation = ????????????
dc.Customers.InsertOnSubmit(Customer);
dc.SubmitChanges();


db.SubmitChanges();

as CustomerOccupation will be an CustomerOccupationItem type.

Paul Beck said...

Hi Raymund, applogies for the delayed reply I've been little busy but here is my answer:

DataContext dc = new DataContext();
EntityList CustomerOccupationList = dc.GetList("Customer Occupation");
var coQuery = (from b in CustomerOccupationList .ToList()
where b.Id == 5
select new
{
b
}).Single();


DataContext dc = new DataContext();
CustomersItem Customer = new CustomersItem();
Customer.Title = "Title";
Customer.Occupation = coQuery.b;
dc.Customers.InsertOnSubmit(Customer);
dc.SubmitChanges();

Paul Beck said...

In summary, find the entity that will be your foreign key using Linq to SharePoint and in your insert operation add the retrieved entity to your insert.

Kunaal said...

Hi Paul,

I am getting some issues with a similar functionality. And get the following error:

'All new entities within an object graph must be added/attached before changes are submitted'

This is after I'm setting a look up as a part of insertonsubmit, it gives me an error on submitchanges()

Paul Beck said...

Hi Kunaal, pls post the code and the error message stack.

Post a Comment