Friday, April 26, 2013

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>

No comments:

Post a Comment