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 : code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b — jdweng
– jdweng, Commented Oct 14, 2016 at 12:42

rbr94 · Accepted Answer · 2016-10-14 12:52:13Z

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, 2016 at 12:52

answered Oct 14, 2016 at 12:45

rbr94

2,2874 gold badges27 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kiwi Over a year ago

Hi, thanks! one additional question: when I added .Where(g => !string.Equals(g.Key.query, g.Key.name, StringComparison.CurrentCultureIgnoreCase)) it broke the query :/

rbr94 Over a year ago

@Kiwi what's the error? Is it correct that the property name must be capitalized?

Kiwi Over a year ago

Apparently there was something not working with the string.equals changing it to g.Key.query != q.Key.name fixed it

Raphaël Althaus · Accepted Answer · 2016-10-14 12:41:07Z

0

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

answered Oct 14, 2016 at 12:41

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Collectives™ on Stack Overflow

Converting SQL to Linq query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related