CODEWITHSUNDEEP
c#sqllinqlambda
lilly
lilly

Reputation: 21

C# lambda-> All rows Select Add Row_Number

I have a table: 

DataTable store_temp = new DataTable(); 
store_temp.Columns.Add("patn");
store_temp.Columns.Add("rf");
store_temp.Columns.Add("name");
store_temp.Columns.Add("conv");
store_temp.Columns.Add("conv_type");
store_temp.Columns.Add("recorddate");
store_temp.Columns.Add("executiondate");

My C# code :

int i = 0;
var rowsgroups = (from row in store_temp.AsEnumerable().GroupBy(row =>
row.Field<string>("patn"))
.OrderBy((g => g.OrderByDescending(y => y.Field<string("executiondate")).ThenByDescending(y =>
y.Field<string>("rf"))))
select new
{
    patn = row.ElementAt(i),
    rf_num = ++i,
}).ToArray();

I want the lambda experession, which is equivalent to: 

select patn, rf,

> row_number() over( partition by patn order by executiondate,rf ) as rf_num,

     name, conv,conv_type, recorddate, executiondate 
      from store_temp2

But, lambda syntax ... var rowsgroups has just a one row.. I want to show all rows in store_temp. What should I do to fix the query?

Upvotes: 1

Views: 2598

Answers (3)

Ivan Stoev
Ivan Stoev

Reputation: 205729

row_number() over(partition by patn order by executiondate, rf)

means in LINQ you need to group by patn, then order each group by executiondate, rf, then use the indexed Select overload to get row numbering inside the group, and finally flatten the result with SelectMany.

With that being said, the equivalent LINQ query could be something like this:

var result = store_temp.AsEnumerable()
    .GroupBy(e => e.Field<string>("patn"), (key, elements) => elements
        .OrderBy(e => e.Field<string>("executiondate"))
        .ThenBy(e => e.Field<string>("rf"))
        .Select((e, i) => new
        {
            patn = key,
            rf = e.Field<string>("rf"),
            rf_num = i + 1,
            name = e.Field<string>("name"),
            conv = e.Field<string>("conv"),
            conv_type = e.Field<string>("conv_type"),
            recorddate = e.Field<string>("recorddate"),
            executiondate = e.Field<string>("executiondate")
        }))
    .SelectMany(elements => elements)
    .ToArray();

Upvotes: 2

Moumit
Moumit

Reputation: 9600

I don't think you required any groupby as per your required sql

        var i=0;
        var rowsgroups = (from row in store_temp.AsEnumerable()
                          orderby row.Field<string>("executiondate") descending,
                          row.Field<string>("rf") descending
                          select new
                            {
                                patn = row.Field<string>("patn"),
                                rf_num = ++i,
                                name = row.Field<string>("name"),
                                conv = row.Field<string>("conv"),
                                conv_type = row.Field<string>("conv_type"),
                                recorddate = row.Field<string>("recorddate"),
                                executiondate = row.Field<string>("executiondate") 
                            }).ToArray();

Upvotes: 0

jdweng
jdweng

Reputation: 34427

Try something like this

select new
{
    rowNum = store_temp.Rows.IndexOf(row),
    patn = row.ElementAt(i),
    rf_num = ++i,
}).ToArray();

Upvotes: 0

Related Questions