Pages

▼

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
at

5 comments:

  1. Raymund | angsarap.net2 March 2011 at 21:52

    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.

    ReplyDelete
  2. Paul Beck13 March 2011 at 19:07

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

    ReplyDelete
  3. Paul Beck13 March 2011 at 19:09

    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.

    ReplyDelete
  4. Kunaal15 June 2012 at 04:35

    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()

    ReplyDelete
  5. Paul Beck15 June 2012 at 10:00

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

    ReplyDelete