C# lambda-> All rows Select Add Row_Number

Question

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?

I want just a add column row_number .. and change the sql to lambda experession.. — lilly
– lilly, Commented Feb 29, 2016 at 8:25
Your required lamba .. sql does not' contain any group by .. it only required order-by for row_number.. did you miss there any thing?? — Moumit
– Moumit, Commented Feb 29, 2016 at 8:39

Ivan Stoev · Accepted Answer · 2016-02-29 09:15:39Z

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

jdweng · Accepted Answer · 2016-02-29 08:32:17Z

0

Try something like this

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

answered Feb 29, 2016 at 8:32

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

4 Comments

lilly Over a year ago

that is a error .. store_temp.Rows.IndexOf(row) Error 1 The best overloaded method match for 'System.Data.DataRowCollection.IndexOf(System.Data.DataRow)' has some invalid arguments

jdweng Over a year ago

Does your code give an error? The variable row should be a DataRow, not sure why you not are getting an error without my change. Code should work if current code is working.

lilly Over a year ago

Error2 .. Argument 1: cannot convert from 'System.Linq.IGrouping<string,System.Data.DataRow>' to 'System.Data.DataRow'.. :'( .....

jdweng Over a year ago

You can't cast a List<> object to a singular. So you need to add to code FirstOrDefault() method to Linq. The real problem is the Group is a List<DataRow>. So you need two nested 'select' methods. First to enumerate through the groups (List<DataRow>). The 2nd to enumerate through the rows in the groups.

Moumit · Accepted Answer · 2016-02-29 08:46:35Z

0

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

answered Feb 29, 2016 at 8:46

Moumit

9,8709 gold badges59 silver badges66 bronze badges

1 Comment

lilly Over a year ago

no,..nono i want .. partition by patn column.. .. how can is use the partition by in lambda expression???

Collectives™ on Stack Overflow

C# lambda-> All rows Select Add Row_Number

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related