Converting SQL to Linq query

Question

I'm trying to get the output of the following query into a Linq query

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

But I can't seem to figure out how to do this; this is what I have so far

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

but how would I continue?

the result is something like this:

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

The original tables looks like following

SearchQueries;

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

Clicks;

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

Try using a join : https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — jdweng, Oct 14 '16 at 12:42

rbr94 · Accepted Answer · 2016-10-14T12:52:13.840

1

Try to use GroupBy and Count: (I changed the order to using SearchQueries as "base table" in the expression, just to make it more easy to compare to the SQL-statement)

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);

edited Oct 14 '16 at 12:52

answered Oct 14 '16 at 12:45

rbr94

2,227
3
23
39

Hi, thanks! one additional question: when I added `.Where(g => !string.Equals(g.Key.query, g.Key.name, StringComparison.CurrentCultureIgnoreCase))` it broke the query :/ – Kiwi Oct 17 '16 at 06:51
@Kiwi what's the error? Is it correct that the property `name` must be capitalized? – rbr94 Oct 17 '16 at 07:36
Apparently there was something not working with the string.equals changing it to `g.Key.query != q.Key.name` fixed it – Kiwi Oct 17 '16 at 08:25

score 0 · Answer 2 · answered Oct 14 '16 at 12:41

Well, if you have a navigation property Searchquery on Click, as it looks like, you can do

var result =
    _db.Clicks
        .GroupBy(m => new {name = m.Name, query = m.Searchquery.Query)
        .Select(g => new {
           Query = g.Key.query,
           Name = g.Key.name,
           Hits = g.Count()
});

Converting SQL to Linq query

2 Answers2