This question is locked. New answers and comments are not allowed.
I have used LINQ to SQL to build a query that includes a left outer join, a select sub query and a union. Though this does not throw any logical exception the query execution always timed out. While debugging I noticed that the foreach loop on the result set causes to spew a lot of select statements and other execution statements in the output window, which is slowing down the process and hence causes time out.
I had to use a stored procedure instead to get it working.
Here is the linq to sql expression that I used.
var recentFriendMessages = (from messages in scope.Extent<Message>()
join groupInfo in
(from grpMessages in scope.Extent<GroupsMessage>()
join grps in scope.Extent<Group>() on grpMessages.GroupID equals grps.Id
select new { GroupID = grpMessages.GroupID, MessageID = grpMessages.MessageID, GroupName = grps.Name })
on messages.Id equals groupInfo.MessageID into messageGroups
from message in messageGroups.DefaultIfEmpty()
join users in scope.Extent<User>() on messages.UserID equals users.Id
where messages.UserID != userID
&& usersFriendIds.Contains(messages.UserID)
orderby messages.Posted descending
select new
{
users.UName,
UserID = users.Id,
GroupID = message == null ? -1 : message.GroupID,
messages.Posted,
GroupName = message == null ? "" : message.GroupName,
MessageID = messages.Id,
messages.Title
}).Take(3);
long messageIdFiller = 0;
string messageTitleFiller = string.Empty;
var recentFriendsGroupMembers = (from usrGrps in scope.Extent<UsersGroup>()
join users in scope.Extent<User>() on usrGrps.UserID equals users.Id
join grps in scope.Extent<Group>() on usrGrps.GroupID equals grps.Id
where usrGrps.UserID != userID && usersFriendIds.Contains(usrGrps.UserID)
orderby usrGrps.Date1 descending
select new
{
users.UName,
UserID = users.Id,
usrGrps.GroupID,
Posted = usrGrps.Date1,
GroupName = grps.Name,
MessageID = messageIdFiller,
Title = messageTitleFiller
}).Take(3);
var result= recentFriendMessages.Union(recentFriendsGroupMembers);
result = result.OrderByDescending(s => s.Posted);
result = result.Take(3);
foreach (var res in result) //as soon as the this statement is hit, output window gets filled with a large chunk of statements
{
}
Please suggest if this query can be optimized.
Thanks,
Ramya
I had to use a stored procedure instead to get it working.
Here is the linq to sql expression that I used.
var recentFriendMessages = (from messages in scope.Extent<Message>()
join groupInfo in
(from grpMessages in scope.Extent<GroupsMessage>()
join grps in scope.Extent<Group>() on grpMessages.GroupID equals grps.Id
select new { GroupID = grpMessages.GroupID, MessageID = grpMessages.MessageID, GroupName = grps.Name })
on messages.Id equals groupInfo.MessageID into messageGroups
from message in messageGroups.DefaultIfEmpty()
join users in scope.Extent<User>() on messages.UserID equals users.Id
where messages.UserID != userID
&& usersFriendIds.Contains(messages.UserID)
orderby messages.Posted descending
select new
{
users.UName,
UserID = users.Id,
GroupID = message == null ? -1 : message.GroupID,
messages.Posted,
GroupName = message == null ? "" : message.GroupName,
MessageID = messages.Id,
messages.Title
}).Take(3);
long messageIdFiller = 0;
string messageTitleFiller = string.Empty;
var recentFriendsGroupMembers = (from usrGrps in scope.Extent<UsersGroup>()
join users in scope.Extent<User>() on usrGrps.UserID equals users.Id
join grps in scope.Extent<Group>() on usrGrps.GroupID equals grps.Id
where usrGrps.UserID != userID && usersFriendIds.Contains(usrGrps.UserID)
orderby usrGrps.Date1 descending
select new
{
users.UName,
UserID = users.Id,
usrGrps.GroupID,
Posted = usrGrps.Date1,
GroupName = grps.Name,
MessageID = messageIdFiller,
Title = messageTitleFiller
}).Take(3);
var result= recentFriendMessages.Union(recentFriendsGroupMembers);
result = result.OrderByDescending(s => s.Posted);
result = result.Take(3);
foreach (var res in result) //as soon as the this statement is hit, output window gets filled with a large chunk of statements
{
}
Please suggest if this query can be optimized.
Thanks,
Ramya