I have three lists on Sharepoint 2010 and I have working code that gets the lists and relates them. My problem is that it takes around 15 seconds to load my page. I am a rank beginner with LINQ to Sharepoint and LINQ in general. MY question is: Is there a way to make this code run faster?
SeatingChartContext dc = new SeatingChartContext(SPContext.Current.Web.Url);
EntityList<Seating_chartItem> seatCharts = dc.GetList<Seating_chartItem>("seating_chart");
EntityList<UsersItem> users = dc.GetList<UsersItem>("users");
EntityList<Excluded_usersItem> exusers = dc.GetList<Excluded_usersItem>("excluded_users");
// EntityList<LogsItem> logs = dc.GetList<LogsItem>("logs");
List<Seating_chartItem> seatList = (from seat in seatCharts where seat.Room == 0 where seat.Floor == floor select seat).ToList();
List <UsersItem> usersList = (from user in users select user).ToList();
List <Excluded_usersItem> xusersList = (from xuser in exusers select xuser).ToList();
var results = from seat in seatList
join user in usersList on
seat.User_id equals user.User_id
where seat.Room == 0
where seat.Floor == floor
where !(from xuser in xusersList select xuser.User_id).Contains(user.User_id)
select new
{
sid = seat.Seat_id,
icon = seat.Icon,
topCoord = seat.Top_coord,
leftCoord = seat.Left_coord,
name = user.Name,
phone = user.Phone,
mobile = user.Mobile,
content = seat.Content
};
The time this code takes is frustrating, to say the least.
Thanks.
One immediate thing: You are re-querying xusersList
everytime within your join:
where !(from xuser in xusersList select xuser.User_id).Contains(user.User_id)
Instead just first extract the user ids only (since that is the only thing you need)
var xusersList = (from xuser in exusers select xuser.User_id).ToList();
then use it directly:
where !xusersList.Contains(user.User_id)
Even better - determine the valid users before your query:
usersList = usersList.Where( user => !xusersList.Contains(user.User_id))
.ToList();
Now you can just completely remove this where condition from your query.
Also these where conditions seem to be unneeded:
where seat.Room == 0
where seat.Floor == floor
since you have filtered your seatList
this way already.
Having said that you should log some performance data to see what actually takes the most time - is it acquiring the inital lists or your actual join/linq query?