c# - 将 SQL 转换为 Linq 查询

Question

我正在尝试将以下查询的输出放入 Linq 查询中

SELECT SearchQueries.Query,
       Clicks.Name,
       COUNT (SearchQueries.Query) AS Hits
FROM SearchQueries
INNER JOIN Clicks ON Clicks.SearchqueryId = SearchQueries.Id
GROUP BY SearchQueries.Query, Clicks.Name
ORDER BY Hits DESC

但我似乎不知道该怎么做；这是我目前所拥有的

var result =
    _db.Clicks.Select(q => q)
        .GroupBy(q => q.Name, g => g.Searchquery.Query)
        .ToDictionary(g=> g.Key, g => g);

但我该如何继续呢？

结果是这样的:

+---------------+-------------------+------+
|Query          | Name              | Hits |
+---------------+-------------------+------+
|tag            | dnfsklmfnsd       | 53   |
|tag2           | dgsqfsdf          | 17   |
+---------------+-------------------+------+

原始表格如下所示

搜索查询；

+---+-------+
|Id | Query |
+---+-------+
| 1 | tag   | x 53
| 2 | tag2  | x 17
+---+-------+

点击次数；

+---+-------------------+---------------+
|Id | Name              | SearchqueryId |
+---+-------------------+---------------+
| 1 | dnfsklmfnsd       | 1             |
| 2 | dgsqfsdf          | 2             |
+---+-------------------+---------------+

Answer 1

尝试使用 GroupBy 和 Count:(我将顺序更改为使用 SearchQueries 作为表达式中的“基表”，只是为了使其更容易与 SQL 语句进行比较)

var result =
    _db.SearchQueries
       .GroupBy(sq => new { name = sq.Clicks.Name, query = sq.Query)
       .Select(sq => new {
                           Query = sq.Query,
                           Name = sq.Clicks.Name,
                           Hits = sq.Count()
                         })
       .OrderByDescending(sq => sq.Hits);