List Join operation in SharePoint 2007 / 2010


Approach 1



private static void FetchAllListsDataJoinUsingLinq()
{
    using (SPSite _spSite = new SPSite("<Site Url>"))
    {
        using (SPWeb web = _spSite.OpenWeb())
        {
            SPList placeList = web.GetList(web.Url + "/lists/TouristPlace");
            SPQuery ospPlaceQuery = new SPQuery();
            DataTable dtPlace = placeList.GetItems(ospPlaceQuery).GetDataTable();
            SPList cityList = web.GetList(web.Url + "/lists/City");
            SPQuery ospCityQuery = new SPQuery();
            DataTable dtCity = cityList.GetItems(ospCityQuery).GetDataTable();
            SPList countryList = web.GetList(web.Url + "/lists/Country");
            SPQuery ospCountryQuery = new SPQuery();
            DataTable dtCountry = countryList.GetItems(ospCountryQuery).GetDataTable();

            List<TouristPlaceDetails> joinData = (from place in dtPlace.AsEnumerable()
                                                    join city in dtCity.AsEnumerable() onplace.Field<string>("CityName") equals city.Field<string>("Title")
                                                    join country in dtCountry.AsEnumerable() oncity.Field<string>("Country") equals country.Field<string>("Title")
                                                    select new TouristPlaceDetails
                                                    {
                                                        Name = place.Field<string>("Title"),
                                                        History = place.Field<string>("History"),
                                                        City = place.Field<string>("CityName"),
                                                        Population = city.Field<string>("Population"),
                                                        Country = country.Field<string>("Title"),
                                                        AgentName = country.Field<string>("AgentName"),
                                                        AgentNumber = country.Field<string>("AgentNumber")
                                                    }).ToList();
        }
    }
}


Approach 2
private static void JoinMultipleListsUsingLinqSharePoint2010()
        {
            LearnDataContext joinDataContext = new LearnDataContext("<Site Url>");
            List<TouristPlaceDetails> joinData = (from place in joinDataContext.TouristPlace.ToList()
                                                  join city in joinDataContext.City on place.CityNameequals city
                                                  join country in joinDataContext.Country on city.Countryequals country
                                                  select new TouristPlaceDetails
                                                  {
                                                      Name = place.Title,
                                                      History = place.History,
                                                      City = place.CityName.Title,
                                                      Population = city.Population,
                                                      Country = country.Title,
                                                      AgentName = country.AgentName,
                                                      AgentNumber = country.AgentNumber
                                                  }
                           ).ToList();

        }

Approach 3

private static void JoinMultipleListsUsingJoinsProperty()
{
    using (SPSite _spSite = new SPSite("<Site Url"))
    {
        using (SPWeb web = _spSite.OpenWeb())
        {
            SPList reportList = web.GetList(web.Url + "/lists/TouristPlace");
            SPQuery ospQuery = new SPQuery();
            ospQuery.Joins = @"<Join Type='LEFT' ListAlias='City'>
                                        <Eq>
                                            <FieldRef Name='CityName' RefType='Id' />
                                            <FieldRef List='City' Name='ID' /> 
                                        </Eq>
                                    </Join>
                                    <Join Type='LEFT' ListAlias='Country'>
                                        <Eq>
                                            <FieldRef List='City' Name='Country' RefType='Id' />
                                            <FieldRef List='Country' Name='ID' /> 
                                        </Eq>
                                    </Join>";
            ospQuery.ProjectedFields = @"<Field
                                            Name='Population'
                                            Type='Lookup'
                                            List='City'
                                            ShowField='Population'/>
                                            <Field
                                            Name='Country'
                                            Type='Lookup'
                                            List='Country'
                                            ShowField='Title'/>
                                            <Field
                                            Name='AgentName'
                                            Type='Lookup'
                                            List='Country'
                                            ShowField='AgentName'/>
                                            <Field
                                            Name='AgentNumber'
                                            Type='Lookup'
                                            List='Country'
                                            ShowField='AgentNumber'/>";
            ospQuery.ViewFields = @"<FieldRef Name='Title'/>
                                        <FieldRef Name='History'/>
                                        <FieldRef Name='CityName'/>
                                        <FieldRef Name='Population'/>
                                        <FieldRef Name='Country'/>
                                        <FieldRef Name='AgentName'/>
                                        <FieldRef Name='AgentNumber'/>";
            SPListItemCollection dtResult = reportList.GetItems(ospQuery);
            List<TouristPlaceDetails> joinResult = new List<TouristPlaceDetails>();

            for (int i = 0; i < dtResult.Count; i++)
            {
                joinResult.Add(new TouristPlaceDetails
                                    {
                                        Name = Convert.ToString(dtResult[i]["Title"]),
                                        History = Convert.ToString(dtResult[i]["History"]),
                                        City = Convert.ToString(dtResult[i]["CityName"]),
                                        Population = Convert.ToString(dtResult[i]["Population"]),
                                        Country = Convert.ToString(dtResult[i]["Country"]),
                                        AgentName = Convert.ToString(dtResult[i]["AgentName"]),
                                        AgentNumber = Convert.ToString(dtResult[i]["AgentNumber"])
                                    }
                                );
            }
        }
    }
}

 Set Joins property in SPQuery in the below mentioned format:

<Join Type='LEFT' ListAlias='customers'>
          <Eq>
                <FieldRef List='<Child List Name (Optional)>' Name='<Lookup Column Name>' RefType='Id' />
                <FieldRef List='<Parent List Name>' Name='ID' />
          </Eq>
 </Join>

More Info

http://www.a2zmenu.com/Tutorials/SharePoint/List-Join-operation-in-SharePoint-2007---2010.aspx



Comments

Post a Comment

Popular posts from this blog

IRM and the Object Model

A couple of weeks earlier, I saw an interesting issue. It was a normal day as far as days go in support and in comes this issue, where the person in question wants to investigate or rather should I say automate the process of creating IRM settings on document libraries. I wasn't working on this issue and a colleague took this over. But then somewhere it was lurking, how the hell do you do that? The natural starting point is getting a site/web context and going down to the list and then getting dirty with the properties exposed by the particular SPList object. So after finishing what I had on my plate, I dug into figuring out how exactly to set the IRM settings for a list using the object model. So here goes: Step 1 Enable IRM on the farm SPWebService svc = SPFarm.Local.Services.GetValue<SPWebService>(); SPIrmSettings irmSettings    = svc.IrmSettings;irmSettings.IrmRMSEnabled    = true;//set true or false based on the situation irmSettings.IrmRMSUseAD      = true; irmSett
Read more

This content database has a schema version which is not supported in this farm

Image
 Today i have faced one issue while mount content database to server. the issue is " This content database has a schema version which is not supported in this farm." I was getting this error when i ran Mount-SPContentDatabase using powershell.  I tried checking SP versions and other things finally got solution in below way Reason: Once you have taken the backup from SharePoint 2013 content database, usually you will restore the database in the target SQL server. After restoring the database, you will try to restore content database in any one of Webapplication most probably new one. In this stage, you will get the above error message. Solution: Your source SQL server schema and the destination SQL server schema might not be matched. So you need to update the "Versions" table values in the "Restored Database". 1. Open " Versions " table from any of the existing "Content database"  from the Target SQL Server machine in "
Read more

Activate and Deactivate Feature through PowerShell

Image
I was looking at the analytics of the site the other day and I noticed that my post,  Adding and Deploying Solutions with PowerShell , currently has the most traffic on the site.  I had always intended to write a follow up post on this, so here it is.  Today I’m going to talk about how to enable and disable features using PowerShell.  It’s great that you know how to deploy solutions now with PowerShell, but now you want to activate your features. In SharePoint 2007 to activate a feature from the command line you might have used a statement like the one below.  I’ll use the  Reporting  feature on my server named  sp2010  as an example.  The value specified in the  name  parameter refers to the actual folder name of the feature that is in your SharePoint Root folder (14 hive). stsadm.exe –o activatefeature –name Reporting  – url http://sp2010 To get started with PowerShell, run the SharePoint 2010 Management Console located in your Microsoft SharePoint 2010 Products folder on your s
Read more