LINQ Complex Query

Question

I have a complex SQL query which I need to run in LINQ. I'm wondering is it possible to do it with LINQ, or do I need anything else? Could you please help me? Thanks.

SELECT DISTINCT_PLAY_COUNT,SUM(1) AS CLIENT_COUNT FROM
    (SELECT CLIENT_ID,SUM(1) AS DISTINCT_PLAY_COUNT FROM
        (SELECT CLIENT_ID,SONG_ID FROM PIEXHIBIT 
        WHERE PLAY_TS >= '10/08/2016 00:00:00' AND PLAY_TS <= '10/08/2016 23:59:59'
        GROUP BY CLIENT_ID,SONG_ID
    )
    GROUP BY CLIENT_ID
)
GROUP BY DISTINCT_PLAY_COUNT

Here is my class for it and what I accomplished so far;

    public class Exhibit
    {
        public string PLAY_ID { get; set; }
        public Int32 SONG_ID { get; set; }
        public Int32 CLIENT_ID { get; set; }
        public DateTime PLAY_TS { get; set; }

    }

    var sql = from Exhibit row in Exhibit
                              where row.PLAY_TS >= DateTime.Parse("10/08/2016 00:00:00") && row.PLAY_TS <= DateTime.Parse("10/08/2016 23:59:59")
                              select new { row.CLIENT_ID, row.SONG_ID };

Please never just post SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. Also, tell what type of LINQ you're targeting (to entities?), and show your own first efforts. They clarify more to us than you might think. — Gert Arnold, Jun 11 '17 at 20:24

score 1 · Accepted Answer · answered Jun 11 '17 at 21:13

1

Exhibits
   .Where(t => t.PLAY_TS >= new DateTime(2016, 8, 10) && t.PLAY_TS < new DateTime(2016, 8, 11))
   .Select(t => new { t.CLIENT_ID, t.SONG_ID })
   .Distinct()
   .GroupBy(c => c.CLIENT_ID)
   .Select(c => c.Count())
   .GroupBy(g => g)
   .Select(g => new { DISTINCT_PLAY_COUNT = g.Key, CLIENT_COUNT = g.Count() })
   .ToList();

I think this should work.

answered Jun 11 '17 at 21:13

Antonín Lejsek

6,003
2
16
18

It worked nicely but i want to ask something. Is it normal that output of LINQ and sql are different ? – developerCoder Jun 11 '17 at 21:49
I am afraid I do not understand the question. – Antonín Lejsek Jun 11 '17 at 21:52
I got different outputs. When i run SQL i got something else. When i run LINQ result is different. I'm asking that if this is normal ? – developerCoder Jun 11 '17 at 21:54
There may be a bug, the results should be the same. – Antonín Lejsek Jun 11 '17 at 21:56
Does in memory calculation(LINQ) better than SQL ? Maybe thats the case? – developerCoder Jun 11 '17 at 21:57
They should give the same result and they seem to be the same on my data. I would check if you use the same data and the same date range conditions. – Antonín Lejsek Jun 11 '17 at 22:06
@developerCoder No, Its not normal to have different outputs. You need to compare your two outputs to figure out what is different. Only the time taken should change. I think it's your "Distinct" that is causing an issue. But I don't know without seeing some data. We need something to compare in order to find the problem. – Morten Bork Jun 12 '17 at 07:49

score 0 · Answer 2 · answered Jun 11 '17 at 20:24

0

distincts are always a bit tricky with LINQ. It is possible:

Select distinct using linq (I am partial to overloading the equals method, even though that likely isn't the proper way to go.)

The link has a good load of suggestions as to what to do. The "Duplicate answer" thread also has some nice suggestions.

I would also like to suggest that your:

PLAY_TS <= 'DD/MM/YYYY 23:59:59'

should be changed to

PLAY_TS < 'DD+1/MM/YYYY 00:00:00'

Simply because it is possible to actually hit a milisecond issue where you technically didn't change date yet, because the milisecond hasn't hit 999 yet.

I hope you understand what I mean.

answered Jun 11 '17 at 20:24

Morten Bork

1,413
11
23

Thanks for date suggestion. I wonder if its possible to select nested in LINQ. – developerCoder Jun 11 '17 at 20:33
1

@developerCoder it is there is an answer (sort of) here: https://stackoverflow.com/questions/9044255/linq-query-with-three-nested-levels – Morten Bork Jun 11 '17 at 20:38
No need to use distinct with a group object. Just take 1st item of group. – jdweng Jun 12 '17 at 02:12

score 0 · Answer 3 · answered Jun 11 '17 at 20:26

0

I would suggest putting it in a stored procedure instead of using LINQ. Then executing the stored procedure by the code below:

using (var conn = new SqlConnection(connString)) //Connection string in there
using (var command = new SqlCommand("ProcedureNameHere", conn) {
    CommandType = CommandType.StoredProcedure }) {
        conn.Open();
        command.ExecuteNonQuery();
}

answered Jun 11 '17 at 20:26

fluffy

223
1
14

it will be good if i can do this query in database. I simply reading a csv file and need to achieve that sql's output while i'm reading that csv. Since my query is too complex i can't convert it to LINQ. – developerCoder Jun 11 '17 at 20:37

score -1 · Answer 4 · answered Jun 11 '17 at 22:05

Here is my suggestion. GroupBy will automatically give you distinct with my code. :

            var sqlDateTime = from Exhibit row in Exhibit.exhibits
                     where row.PLAY_TS >= DateTime.Parse("10/08/2016 00:00:00") && row.PLAY_TS <= DateTime.Parse("10/08/2016 23:59:59")
                     select new { cid = row.CLIENT_ID, songid = row.SONG_ID, date = row.PLAY_TS };

            var results = sqlDateTime.GroupBy(x => new { cid = x.cid, sid = x.songid })
                .Select(x => new { count = x.Count(), cid = x.Key.cid, sid = x.Key.sid }).ToList();

LINQ Complex Query

4 Answers4