Is it possible to convert a similar SQL query to LINQ?

Question

SQL

Hello, please help me with this request.
He himself is:

SELECT * 
FROM [DB].[dbo].[items]
WHERE id IN(
  SELECT TOP (20) itemid
  FROM [DB].[dbo].[xref]
  WHERE EXISTS(
    SELECT id
    FROM [DB].[dbo].[vars]
    WHERE wordid = id AND ( word = 'WORD1' OR word = 'WORD2')
  )
  GROUP BY itemid
  ORDER BY COUNT(itemid) DESC, SUM(weight) DESC
)

Here's what happened to me, but it doesn't work as it should, which of course is logical (in the code I removed various checks to reduce it)... Help me do it right

string[] searchArray = {"WORD1","WORD2"};

int[] searchedVars = db.vars.Where(o => searchArray.Contains(o.word)).Select(o => o.id).ToArray();

int tmpword = searchedVars[0];
Expression<Func<xref, bool>> whExp = o => o.wordid == tmpword;


int searchedVarsCount = searchedVars.Count();

for (int i = 1; i < searchedVarsCount; i++)
{
    tmpword = searchedVars[i];
    whExp = whExp.Or(o => o.wordid == tmpword);
}

whExp = whExp.Expand();

int[] SearchedIndexes = db.xref.Where(whExp)
    .GroupBy(s => s.itemid)
    .Select(g => new
    {
        ItemId = g.Key,
        Score = g.Sum(s => s.weight),
        CountItemId = g.Count()
    })
    .OrderByDescending(r => r.CountItemId)
    .ThenByDescending(r => r.Score)
    .Skip(0)
    .Take(limit)
    .Select(o => o.ItemId)
    .ToArray();

List<items> Searched = db.items.Where(o => SearchedIndexes.Contains(o.id)).ToList();

Answer 1

I didn’t really understand the construction with whExp, here is my version:

string[] searchArray = {"WORD1","WORD2"};
var vars = db.vars.Where(v =>  searchArray.Contains(v.word)).Select(v => v.id);

var xrefs = dr.xref.Where(x => vars.Contains(x.wordid)).GroupBy(x => x.itemid)
    .Select(g => new
    {
        ItemId = g.Key,
        Score = g.Sum(s => s.weight),
        CountItemId = g.Count()
    })
    .OrderByDescending(r => r.CountItemId)
    .ThenByDescending(r => r.Score)
    .Take(20)
    .Select(o => o.ItemId);

var items = db.Items.Where(i => xrefs.Contains(i.id));