How to use partition in Linq C#?

user_207047 0
Viewed 341

I have the following SQL table where I need to select one top exchange for each currency

Currency  Exchange Date
USD       NewYork  01/12/20
USD       NewYork  01/11/20
USD       NewYork  01/10/20
USD       Montreal 01/10/20
CAD       Montreal 01/07/20
CAD       Montreal 01/06/20
CAD       Beijing  01/06/20

I am using this query that uses partition

select distinct currency,
       first_value(exchange) over (partition by currency order by count(*) desc) exchange,
       max(count(*)) over (partition by currency) frequency
from tablename
group by currency, exchange

Result

Currency Exchange Frequency 
USD      NewYork  3
CAD      Montreal 2

I need to to implement this in Linq

entity-framework linq c#
2 Answers

Try following :

DataTable dt = new DataTable();
dt.Columns.Add("Currency", typeof(string));
dt.Columns.Add("Exchange", typeof(string));
dt.Columns.Add("Date", typeof(DateTime));

dt.Rows.Add(new object[] {"USD", "NewYork", DateTime.Parse("01/12/20")});
dt.Rows.Add(new object[] {"USD", "NewYork", DateTime.Parse("01/11/20")});
dt.Rows.Add(new object[] {"USD", "NewYork", DateTime.Parse("01/10/20")});
dt.Rows.Add(new object[] {"USD", "Montreal", DateTime.Parse("01/10/20")});
dt.Rows.Add(new object[] {"CAD", "Montreal", DateTime.Parse("01/07/20")});
dt.Rows.Add(new object[] {"CAD", "Montreal", DateTime.Parse("01/06/20")});
dt.Rows.Add(new object[] { "CAD", "Beijing", DateTime.Parse("01/06/20") });

var results = dt.AsEnumerable()
    .GroupBy(x => x.Field<string>("Currency"))
    .Select(x =>
        x.GroupBy(y => y.Field<string>("Exchange"))
            .Select(z =>
                new
                {
                    Currency = x.Key,
                    Exchange = z.Key,
                    Frequency = z.Count()
                })
            .OrderByDescending(z => z.Frequency)
            .First()
    ).ToList();
user_50152380

Running this query (from jdweng's answer)

Exchanges.GroupBy(x => x.Field<string>("Currency"))
    .Select(x =>
        x.GroupBy(y => y.Field<string>("Exchange"))
            .Select(z =>
                new
                {
                    Currency = x.Key,
                    Exchange = z.Key,
                    Frequency = z.Count()
                })
            .OrderByDescending(z => z.Frequency)
            .First()
    ).ToList()

in LINQPad results in

+----------+----------+-----------+
| Currency | Exchange | Frequency |
+----------+----------+-----------+
| USD      | New York |         3 |
| CAD      | Montreal |         2 |
+----------+----------+-----------+

And this generated SQL

SELECT [t4].[test], [t1].[Currency], [t4].[Exchange], [t4].[value] AS [Frequency]
FROM (
    SELECT [t0].[Currency]
    FROM [Exchanges] AS [t0]
    GROUP BY [t0].[Currency]
    ) AS [t1]
OUTER APPLY (
    SELECT TOP (1) 1 AS [test], [t3].[Exchange], [t3].[value]
    FROM (
        SELECT COUNT(*) AS [value], [t2].[Exchange]
        FROM [Exchanges] AS [t2]
        WHERE (([t1].[Currency] IS NULL) AND ([t2].[Currency] IS NULL)) OR (([t1].[Currency] IS NOT NULL) AND ([t2].[Currency] IS NOT NULL) AND ([t1].[Currency] = [t2].[Currency]))
        GROUP BY [t2].[Exchange]
        ) AS [t3]
    ORDER BY [t3].[value] DESC
    ) AS [t4]
ORDER BY [t4].[value] DESC
user_93639730
Related