Showing posts with label LINQ to SP. Show all posts
Showing posts with label LINQ to SP. Show all posts

Friday, February 20, 2015

LINQ to SP and LookUp operation

Below code will get the actual look up value from look up list. There is no way out to get look up value from the list which has look up column. We should get this value with look up list reference avail. in Entity Class.


We have Emp (Child list) and Dept (lookup list). Emp list has Dept lookup column pointing to Dept list's Title column.

EmpDataContext dc = new EmpDataContext("http://ramsqlbi:47407/sites/test/");

                EntityList<EmpEntity> empList = dc.GetList<EmpEntity>("Emp");

                var getName = (from emp in empList
                                         where emp.Dept.Title == "IT"
                                         select emp).First();

                string lstrFirstName = getName.Title;

Above code will seek Dept "IT" in Dept list and display First Name from Emp list. Take a look at code "emp.Dept.Title". Emp object has Dept reference which points to Dept list and match Title column in Dept. list. This is the way to query lookup value.

Thursday, February 19, 2015

SPMetal command to generate Entity Class for specific list

SPMetal tool is use to generate Entity Classes and reduce developer work drastically. SPMetail resides at location -

C:\program files\common files\microsoft shared\Web Server Extensions\15\BIN

 Hence, below command will do the job and generate Entity class for us.


C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\BIN>spmetal.exe /web:http://ram:40705/sites/site1 /namespace:LINQToSP /code:EmployeeEntities.cs

However, above command will generate Entity classes for all the list available in given website (/web). Generating N nos. of entity classes is of no use till we need it. However, we can too generate entity class for specific list.

Get all the details over here.


Handling Conflicts changes in Linq To SharePoint

EmployeeEntitiesDataContext dc =null;
            try
            {
                EmployeeEntitiesDataContext dc = new EmployeeEntitiesDataContext("http://ramsqlbi:40705/sites/gqs/");
             
                    EntityList<EmployeeItem> emp = dc.GetList<EmployeeItem>("Employee");
                    var updatedResult = (from empToUpd in emp
                                         where empToUpd.FirstName == "Yagnesh"
                                         select empToUpd).First();

                    updatedResult.FirstName = "Yagnesh2";
                    updatedResult.Title = "Kotecha";
                    dc.SubmitChanges();
            }

            catch (Microsoft.SharePoint.Linq.ChangeConflictException exc)
            {
                foreach (ObjectChangeConflict conflicts in dc.ChangeConflicts)
                {
                    //conflicts.Resolve(RefreshMode.KeepCurrentValues);//Keep current value and override anything in DB with this value
                    //conflicts.Resolve(RefreshMode.OverwriteCurrentValues);//Keep Database version
                    conflicts.Resolve(RefreshMode.KeepChanges);
                }

                dc.SubmitChanges();
            }

LINQ to SharePoint to work with CRUD operation

LINQ is the best to work on list and its operations and avoiding SPSite, SPList and SPListItem stuffs.

Add item in list with LINQ to SharePoint

using (EmployeeEntitiesDataContext dc = new EmployeeEntitiesDataContext("http://ram:40705/sites/site1/"))
            {
                EntityList<EmployeeItem> emp = dc.GetList<EmployeeItem>("Employee");
                EmployeeItem newItem = new EmployeeItem()
                {
                    FirstName = "deepak",
                    Title = "desai",
                };
                emp.InsertOnSubmit(newItem);
                dc.SubmitChanges();
            }

Update item

EmployeeEntitiesDataContext dc = new EmployeeEntitiesDataContext ("http://ram:40705/sites/site1/");
               
                    EntityList<EmployeeItem> emp = dc.GetList<EmployeeItem>("Employee");
                    var updatedResult = (from empToUpd in emp
                                         where empToUpd.FirstName == "Yagnesh"
                                         select empToUpd).First();

                    updatedResult.FirstName = "Yagnesh2";
                    updatedResult.Title = "Kotecha";

                    dc.SubmitChanges();

Delete item

using (EmployeeEntitiesDataContext dc = new EmployeeEntitiesDataContext("http://ram:40705/sites/site1/"))
            {
                EntityList<EmployeeItem> emp = dc.GetList<EmployeeItem>("Employee");
                var result = (from empToDel in dc.Employee
                                  where empToDel.FirstName.StartsWith("c", +
                                  StringComparison.CurrentCultureIgnoreCase)
                                  select empToDel);
                emp.DeleteAllOnSubmit(result);
                dc.SubmitChanges();

            }

LINQ to SharePoint to seek name

One of the good feature of LINQ is to provide functinality to interact with SharePoint. LINQ is not only to "query" SharePoint list but also to provide CRUD operation on SharePoint list (known as LINQ to SP)

Noteworthy:

  • LINQ to SharePoint queries are translated to proper CAML queries
  • CAML queries are in turn later translated to SQL queries


Technically, the purpose of LINQ to SP is to replace the weird syntax of CAML query and provide user with Object Oriented way to do operations with SharePoint which is much easy to read / code and allow intelligence in Visual Studio during debug which was not feasible while working with CAML.

Lets begin with simple example.

Employee List


Here, I have created 1 example with employee list. We will write LINQ to SharePoint code to retrieve employee with name "Yagnesh".

Note: Use SPMetal exe which will generate Entity classes for DataContext. Get more info. on SPMetal here. If not, then developer has to create and decorate classes and its property by him self.

EmployeeEntitiesDataContext dc = new EmployeeEntitiesDataContext("http://ram:40705/sites/site1/");
EntityList<EmployeeItem> emp = dc.GetList<EmployeeItem>("Employee");
var updatedResult = (from empToUpd in emp
                                 where empToUpd.FirstName == "Yagnesh"
                                 select empToUpd).First();

First, we need to retrieve Data Context object. Data context object represent subset of content database which holds and work on list and list items.

EmployeeEntitiesDataContext dc = new EmployeeEntitiesDataContext("http://ram:40705/sites/site1");

Then, Getting reference to the list:

EntityList<EmployeeItem> emp = dc.GetList<EmployeeItem>("Employee");

EntityList<> Generice class is use to get any type of list available in SharePoint. Note, EmployeeItem is the class which represent all fields in Employee list.

Finally, LINQ query to seek employee name "Yagnesh".