Tuesday, December 30, 2008

Linq to SharePoint List. (Where , Join )

This article give you information regarding how to use Liinq with SharePoint List to fetch data from SPList.

For Example there are three SharePoint List . Customers , Orders , OrderDetails. (Please See image below). In Orders list CustomerID is lookup field and in OrderDetails list OrderID is lookup field.

Customer

orders

Order Details

Now Get All Customer From Customer List using Linq.

SPSite site = new SPSite("http://localhost:45833"); 
SPWeb web = site.OpenWeb();
SPList lstCustomer = web.Lists["Customers"];

var customers = from SPListItem customer in lstCustomer.Items
                       select customer;

by use of SPListItem (As we use select customer in Linq query)
foreach (SPListItem item in customers)
{
Console.WriteLine(item.ID.ToString() + "-" +item["CustomerName"].ToString());
}

or by using ananymous type

var customers = from SPListItem customer in lstCustomer.Items
                       select new {ID = customer.ID.ToString(), Name= customer[“CustomerName”].ToString()};
foreach (var item in customers)
{
Console.WriteLine(item.ID + "-" + item.Name);
}

use of where clause with this

var customers = from SPListItem customer in lstCustomer.Items
                       where customer.ID == 1
                       select new { ID = customer.ID.ToString(), Name = customer["CustomerName"].ToString() };

Join Three List to get Related Data

SPList lstCustomer = web.Lists["Customers"];
SPList lstorders = web.Lists["Orders"];
SPList lstorderdetails = web.Lists["OrderDetails"];
var customerorders =

from SPListItem customer in lstCustomer.Items
join SPListItem order in lstorders.Items on customer.ID.ToString() equals order["CustomerID"].ToString().Split(new string[]{";#"},StringSplitOptions.None)[0]
join SPListItem orderdetail in lstorderdetails.Items on order.ID.ToString() equals orderdetail["OrderID"].ToString().Split(new string[] { ";#" }, StringSplitOptions.None)[0]                                
select new {Product = orderdetail["Product"].ToString() , Price = Convert.ToDouble(orderdetail["Price"].ToString()) , Qty = Convert.ToInt32(orderdetail["Qty"].ToString()) , OrderID = order.ID , OrderDate = order["OrderDate"].ToString() , CustomerID = customer.ID.ToString(), CustomerName = customer["CustomerName"].ToString() };


foreach (var item in customerorders)
               {
                   Console.WriteLine(item.Product + "  " + item.Price.ToString() + "," + item.Qty.ToString() + ","+ (item.Price * item.Qty).ToString() + ","+ item.OrderID.ToString() + "," + item.OrderDate + "," + item.CustomerName);
               }

                  Note :In above code, I used split operation on string in order to compare LookUp Field ID Value with ID of parent List. There is also posibility to join more than three tables.

For that you have to use nested this join inside other. Please give your comment or advice on this.

13 comments:

vedat said...

THAT IS GREAT
THAT IS WHT I WAS LOOK FOR
THANK YOU MAN

Anonymous said...

Thank you for this!

Anonymous said...

Thank you for this!

Anonymous said...

I am looking for this. Let me try.

Anonymous said...

Hi,
I want to apply similar join with multi lookup column.
Any idea.

Thanks,
Alok...

Jinal Patel said...

Hi Alok,

Can you please provide some more information ?

Like Sample two lists that you want to join this way.

Also tell me what will be your desire output ?

Thanks.

Anonymous said...

Jinal,
I am also facing the similar issue.
Problem is as below:
List1 contains 1 column:
1. ListItemName

Possible Values:
ListItemName
User1
User2

List2 contains 3 columns:
1. ItemName
2. ListItemName (OOB Multi Look up of List 1 on ListItemName column)
3. List2Url

Possible Values:
ItemName ListItemName List2Url
Item1 1;#user1;#2;#user2 abc
Item2 1;#user1 bcd
Item3 2;#user2 xyz


I have fetched items from both the lists and have 2 Lists in code.
Now i want to fetch items from List2 where user in List 1 using innerjoin.


Can you please let me know how to achieve this?

Regards,
Amit

Jinal Patel said...

Hi Amit,

This might be solution. I did not tested yet.

SPWeb web = site.OpenWeb();
SPList List1 = web.Lists["List1"];
SPList List2 = web.Lists["List2"];


var result= from SPListItem re in List1.Items
from SPListItem re2 in List2.Items
where re2["ListItemName"].ToString().Contains(re.ID.ToString() + ";#" + re["ListItemName"].ToString())
select new { ListItemName = re["ListItemName"].ToString(), ItemName = re2["ItemName"].ToString() };

Amit said...

Thanks Jinal,

This could be one of the solution,but this solution will fail in one of the scenerios where Name of 2 person is same:
List2 has 2 entries:
1;#Amit
21;#Amit

So when i query on condition where Name contains 1;#Amit,it will return me both the records instead of just first record



Regards,
Amit

Jinal Patel said...

Hi Amit,

Try This.

var result1 = from SPListItem re in List1.Items
from SPListItem re2 in List2.Items
where ((SPFieldLookupValueCollection)re2["ListItemName"]).Contains(new SPFieldLookupValue() { LookupId = re.ID})
select new { ListItemName = re["ListItemName"].ToString(), ItemName = re2["ItemName"].ToString() };

Dinesh said...

in thin case we have staic Query.
in my case--im prparing the query at run time .
var itemset="My Query";
after executing this ---im not able to get the result object.
is their any way to get the object with string query ???

Jinal Patel said...

Hi,
I will get back to you.

Jinal Patel said...

Hi Dinesh,

Actually linq expression i compiled when compiler compile the application or library.

There is no direct way to do this.

You can use dynamic class creation to do so.

You might get help at following location
.

http://social.msdn.microsoft.com/Forums/en/csharplanguage/thread/31ac96da-415e-424b-9e1f-6aec86c4c3ae