LINQ query with a list VS splitting database

问题:

it's me again. I asked a question a few days ago, about a project on which I'm currently working. The idea is simple : I want to make an expert system able to guess the painting the user is thinking of, like Akinator but "just" for paintings. I have 3 tables in my database : Connaissance (knowledge), Oeuvre (painting) and Question (question asked to the user). Questions are asked one after the other (yes > ID reponse = 1 / no > ID reponse = 2)

Previously, I asked how to express in LINQ langage a query I've had written in SQL, to get the most critical question to ask : LINQ with subquery, group by and having count In fact, I try here to determine the question the most relevant. To do such a thing, I count up the question which retains the largest number of "Yes", passing in review each painting in my database. That's what the query written in my previous post does. But now, the problem is a little more complicated. For the second question to ask, I need to do the same reasoning WITHOUT taking into account the paintings "eliminated" the previous round. Such paintings cannot influence the determining of the future question because they are supposed eliminated (which is different from deleting them). That's why I would like to query not the entire database, but just a part of it.

I've had to ideas :

• 1st : Adding a column in my Connaissance table, a boolean one, and put in it if the painting is susceptible to be the one the user is thinking of (true/1) or not (false/0). I'm not super convinced, even if it would probably work and even if it's better than working on a copy of the real database.

• 2nd : get in a list the paintings eliminated, lap after lap, and ask in my query not to take into account the paintings included in my list. I tried something like that in SQL form :

  SELECT ID_Question
  FROM Connaissance
  WHERE ID_Reponse = 1 AND ID_Question NOT IN (SELECT ID_Question
  FROM Connaissance
  WHERE ID_Oeuvre IN (1,3,5,9) AND ID_Reponse = 1)
  GROUP BY ID_Question
  ORDER BY COUNT(ID_Reponse) DESC, ID_Question
  

I try to get here the ID of the question the most relevant without considering the paintings number 1,3,5,9 where the answer was yes (1) for the different questions in my database. I'm not even convinced if my query is a good one, I'm completely lost to be quite honest. It appears that I give me good ID questions but, maybe it's just a simply matter of chance. And even if the query was good, I tried to write it into LINQ, but it's just horrible, as planned, and doesn't work :

List<int> tab = new List<int> {1, 3, 5, 9};
var r = from c in dc.Connaissance
        where
          c.ID_Reponse == 1 && c.ID_Qu !=
            (from co in dc.Connaissance
             where
            tab.Contains(co.ID_Oeuvre) &&
            co.ID_Reponse == 1
             select new
             {
                 co.ID_Question
             }
             ).Contains(new { ID_Question = c.ID_Question })
                group c by new
                {
                    c.ID_Question,
                    c.ID_Reponse
                } into g
             orderby
          g.Count(p => p.ID_Reponse != null) descending,
          g.Key.ID_Question
        select new
        {
            g.Key.ID_Question
        }

Is there an easier way to do it? Am I wrong? I don't really know all I can do with SQL and LINQ langage, so thanks for all in advance and I hope I won't bother you again with all of that... and above all, I hope I was enough clear!