1

enter image description here

I have data-table and i am trying to find sum of columns where Id starts with Particular value.I have tried some ways but getting error.

result=Convert.ToInt32(dtNew.Compute("Sum(ResPending)", "Substring(ID,0,1)='G'"));//error shows-- Substring() argument is out of range

And tried this way also

dtNew.AsEnumerable().Where(x => x.Field<string>("ID").ToString().StartsWith("G"));//Expression can not contain lambda expression
sp_m
  • 2,647
  • 8
  • 38
  • 62
  • The latter *looks* like it should be okay - please post a short but complete program demonstrating the problem, including the code to set up the table with dummy data. (You don't need the `ToString()` call, btw. Your `Field()` method call already returns a string.) – Jon Skeet May 25 '15 at 06:51
  • 4
    I suspect you executed the latter in Visual Studio "watch" window, no? – har07 May 25 '15 at 06:53
  • 2
    `//Expression can not contain lambda expression` this error usually when you try lambda in _Watch_ window, are you try it in code directly? – Grundy May 25 '15 at 06:54
  • @har07,yes i have executed in watch window and it show the above error. – sp_m May 25 '15 at 06:54
  • 1
    Related thread : [VS debugging “quick watch” tool and lambda expressions](http://stackoverflow.com/questions/725499/vs-debugging-quick-watch-tool-and-lambda-expressions) – har07 May 25 '15 at 06:55
  • @sharad as Jon Skeet's wrote, your code looks fine and might work in the body of your program, only VS watch doesn't support evaluating lambda expression – har07 May 25 '15 at 06:56
  • 1
    @har07 correction. VS upto and including 2013 does not support lambda expressions in the watcher. – Aron May 25 '15 at 07:12
  • 1
    sustring index starts with 1, http://stackoverflow.com/questions/23049995/using-substringexpression-startindex-length-with-bindingsource-on-datacolu – Dreamweaver May 25 '15 at 07:19

3 Answers3

2
result = Convert.ToInt32(dt.Compute("sum(ResPending)", "ID LIKE 'G*'"));

//this works for me. 

dtNew.AsEnumerable().Where(x => x.Field<string>("ID").ToString().StartsWith("G"));

this also works.

Below is the test code:

 Random ran = new Random();
        DataTable dt = new DataTable();
        dt.Columns.Add("ID");
        dt.Columns.Add("Name");
        dt.Columns.Add("ResPending", typeof(Int32));
        for (int i = 0; i < 11; i++)
        {
            DataRow dr = dt.NewRow();
            if (i % 2 == 0)
            {
                dr[0] = "G123" + i;
            }
            else
            {
                dr[0] = i;
            }

            dr[1] = "an";
            dr[2] = ran.Next(1, 100);
            dt.Rows.Add(dr);
        }

        int result = 0;
        //// result =  Convert.ToInt32(dt.Compute("sum(ResPending)", "ID LIKE 'G*'"));
        ////result   = Convert.ToInt32(dt.Compute("Sum(ResPending)", "Substring(ID,0,1)='G'")); ////this throws error. as index is 1 based
        var k  = dt.AsEnumerable().Where(x => x.Field<string>("ID").ToString().StartsWith("G"));
Dreamweaver
  • 1,328
  • 11
  • 21
  • It works because you're enumerating the data into a collection. It's a solution, but it might be hard on resources if the table is particularly big. I'd suggest enumerating only the ID's, then filter the ones you want, and then doing a second Where..In to get the records back. It should be much gentler on the resources, even though you're doing an extra DB query. – Captain Kenpachi May 25 '15 at 07:13
  • you saying about below line : dt.AsEnumerable().Where(x => x.Field("ID").ToString().StartsWith("G")) – Dreamweaver May 25 '15 at 07:15
  • The problem is that AsEnumerable() immediately executes a SELECT TOP 100 PERCENT. – Captain Kenpachi May 25 '15 at 07:16
  • I was just testing the code , I will never be using AsEnumerable in this case, as we can filter the record with inbuilt filters. It was just to validate the code. – Dreamweaver May 25 '15 at 07:20
2

You need to use 

Substring(ID,1,1)
Kaushik Thanki
  • 3,334
  • 3
  • 23
  • 50
0

This sample code returns 3 DataRows:

var coll = dt.AsEnumerable().Where(k => k.Field<string>(0).Contains("G")).Select(p => p).ToList();

Is this what you want?

Amit Kumar Ghosh
  • 3,618
  • 1
  • 20
  • 24