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