This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
Hi, I'm having trouble trying to convert the below SQL query to a Linq statement. It mainly crashes on the error: Not implemented. First the query (which works): SELECT * FROM consults WHERE (ClientId NOT IN (SELECT client_id FROM questionnaire_responses WHERE (Completed = 1) AND (Consult = 1) GROUP BY client_id HAVING (COUNT(Consult) = 6)) ) AND (ConsultNumber = 2) I want to implement the subquery as: (from r in uow.QuestionnaireResponses where r.Completed == true && r.Consult == 1 group r by r.ClientId into clientList where clientList.Count() == 6 select clientList.Key) This caused the NotSupportedException. I guess because of the .Count(). So I tried using the below: (from r in uow.QuestionnaireResponses where r.Completed == true && r.Consult == 1 group r by r.ClientId into clientList select new { clientId = clientList.Key, responses = clientList }).ToList().Where(x => x.responses.Count() != 6).ToDictionary(x => x.clientId, x => x.responses) This worked and gave me the info. However, I'm unable to get this to work in the outer query. It gives me the compile error: That whole .ToDictionary in the subquery i added because otherwise I would get a list which would also be unusable. (from c in uow.Consults where c.ConsultNumber == 2 where !listUncompleted.Keys select c) Anyone has any idea how i can get this to work? Thanks in advance! |
|
|
Yes, the use of .Count() on the grouping in criteria is not supported which is why the exception is raised. In terms of your updated query, is this not a case of making your criteria compatible with the re-written inner query? e.g. Assuming I have interpreted the original query correctly :) (from c in uow.Consults
Jeremy |
|