Some time back i was working on Microsoft Office SharePoint Server 2007, there is a general scenario where i have to query the list through CAML (Collaborative Application Mark Up Language) and show into the Grid. Have done that millions time before, i was unable to  that perform simple task, because of an exception.

after thoroughly search for source of the exception, i have found my self guilty for leaving the closing tag of  Where in the CAML query.

This time round in SP2010, keeping the mind the uneasiness developer have when working with CAML in MOSS 2007. Microsoft maintain the same legacy of Linq now in SharePoint, in the form LinqtoSharePoint.

without getting into  nitty gitty of LinqToSharePoint, i will show how it will work.

i have two list, BSSE 2003 and BSSE Course. BSSE Course have the over all course in the BSSE, while the BSSE 2003 shows the class having the student acquiring marks against those subject.

BSSE 2003,

BSSE Courses,

Well all i want is to do is to give grace marks who are in between 45 to 50, as i am considering the passing criteria is of 50 out of 100 marks.

and in the last i want to show the respective Mark sheet to the current user.

Now then,

Open Visual Studio 2010, Click on File–> New–>Project–> choose Visual WebPart Project tempate.

set the name of the solution as SptoLinq.

we have to create the proxy class for your SharePoint site to work with Linq. In order to do this, go to the command prompt,type cd C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\BIN

Here we use SPMetal to create the proxy class for our SharePoint Site

spmetal /web:http:// /namespace:Projects /code:Projects.cs

in my case

spmetal /web:http://sp2010 /namespace:Projects /code:Projects.cs

after running this, you will find the Project.cs file in the folder, copy that file and paste in to your project folder, you have to include that file in to your project.

Drag the GridView into the VisualWebPart1UserControl.ascx,name that gridview as myGV

//SPmetal create a DataContext inhereted class named as ProjectDataContext

ProjectsDataContext pdc = new ProjectsDataContext(SPContext.Current.Web.Url);
// Write the CAML into the query.xml

using (pdc.Log=System.IO.File.CreateText(@”c:\query.xml”))
{

//Querying for the student getting marks in between 49 to 25

var studentFailedQuery = from student in pdc.BSSE2003

where student.NumberAcquired < 50 && student.NumberAcquired >=45

select student;
// Giving the grace making 50 in each case

foreach (BSSE2003Item bsseStudent in studentFailedQuery)

{                bsseStudent.NumberAcquired = 50;
}
//submit changes

pdc.SubmitChanges();

as you can see from the above code, i have tried to search the students who get marks in between 49 and 45 and give them grace marks to 50.

One important point i want to make is that DataContext class eventually formulate the CAML to query the SharePoint Server rather, as you can notice that i am trying to write the log file.

///Query for the current users marks

var studentQuery = from student in pdc.BSSE2003

where student.StudentImnName == SPContext.Current.Web.CurrentUser.LoginName

select new

//showing the result in more presentable way

{   StudentName = student.StudentImnName,

Course = student.Course.CourseName,

NumberAcquired = student.NumberAcquired

};

myGV.DataSource = studentQuery;

myGV.DataBind();

whats the interesting point i want to make here is that my ProjectsDataContext class identifies that my list have lookup column which are attached to other list so it maintain the integrity of List as well, as you can see from below

from the above code you can see that i trying to show the Report Card grid for currently login user/student.

Here is the result 😀

it is visible that code only rounded of 45-49 into 50 ,

you can find the code from here http://cid-12301c22f859a82f.office.live.com/self.aspx/Demos/SptoLinq.rar

you can download that query.xml file as well from here http://cid-12301c22f859a82f.office.live.com/self.aspx/Demos/query.xml

Happy Coding

Regards,

Shakir Majeed Khan