My query is as follows:
string[] keys = txtSearch.Text.Split(' ');
var query = (from m in db.Messages
join r in db.Recievers on m.Id equals r.Message_Id
where (keys.All(k => (m.MessageText + m.Comments.Select(cmt => cmt.CommentText).ToString()).Contains(k)))
select m.Id).Distinct();
I get the following error:
Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.
The problem is that your query can't be translated to SQL (or at least the provider is not programmed to do so). If fact I don't know how that would query would look in SQL: "Give me all messages where all of these key words are contained in the text or comments"
My first thought is to to multiple requests, one for each key value:
List<int> allIDs = new List<int>();
foreach(string key in keys)
{
var query = (from m in db.Messages
join r in db.Recievers on m.Id equals r.Message_Id
where m.MessageText.Contains(key) || m.Comments.Any(cmt => cmt.CommentText.Contains(key)
select m.Id).Distinct();
allIds.AddRange(query);
}
but you may even need to search for messages and comments in separate queries.
obviously you'd prefer to do it in one query, but I don't see how that can be done in SQL without using cursors anyways. If you can come up with a SQL statement that gives you the right results, then it may be easier to just call that SQL statement directly rather than trying to come up with a Linq statement that is compiled to equivalent SQL.
You can only use Array.Contains() in linq to SQL queries when using local collections.
You need to change your query based on this rule.
The All operator returns a boolean, it also determines if all elements satisfy a condition. As you're enumerating, as soon as a condition isn't met, the enumeration stops and returns a true or false.
I believe you're expecting a Where functionality, return all the specified elements that satisfy a match on your search.
I don't believe you're utilizing the query correctly.
Select(cmt => cmt.CommentText).ToString()?