Using LINQ Query to Filter but it is selecting the particular Key Numerous times

Question

Hi all i am trying to Query from a table (_dtlayout) using the LINQ to SQL. I am trying to select a column name FK_DataPoint. But when i Debug it out it is hitting that column numerous times. It does not throw any ERROR but for Performance wise it is a major issue. I know i am using a Where clause which goes to each rows and filters it. But is there a better way to put this code together in order to filter this DATAPOINT Column. Thank you .

int? dataPointKey = _dtLayout.AsEnumerable().Where(row => 
            row.Field<int?>"FK_FinancialStatementTemplateSectionItem") ==  
            colDef.FK_FinancialStatementTemplateSectionItem 
            && row.Field<int?>("RowNumber")== columnItem.RowNumber 
            && row.Field<int?>("ColumnNumber") ==colDef.ColumnNumber 
            && row.Field<int?>("ItemNumber") == columnItem.ItemNumber)
            .Select (row=>row.Field<int?>("FK_DataPoint")).FirstOrDefault();

Thanks

have you tried the DataTable.Select() function? http://msdn.microsoft.com/en-us/library/way3dy9w(v=vs.110).aspx — Tim.Tang, Aug 08 '14 at 02:04

score 2 · Accepted Answer · answered Aug 08 '14 at 02:00

2

You can do the filtering with FirstOrDefault then get the value you want:

var row = _dtLayout.AsEnumerable().FirstOrDefault(row => 
        row.Field<int?>"FK_FinancialStatementTemplateSectionItem") ==  
        colDef.FK_FinancialStatementTemplateSectionItem 
        && row.Field<int?>("RowNumber")== columnItem.RowNumber 
        && row.Field<int?>("ColumnNumber") ==colDef.ColumnNumber 
        && row.Field<int?>("ItemNumber") == columnItem.ItemNumber);

int? dataPointKey = row != null ? row.Field<int?>("FK_DataPoint") : null;

answered Aug 08 '14 at 02:00

Selman Genç

100,147
13
119
184

Isn't the `.AsEnumerable()` querying the whole table first? – Stefan Aug 08 '14 at 02:02
1

I'm assuming _dtLayout is a datatable... if it's not then yes it fetches all the record from db. – Selman Genç Aug 08 '14 at 02:03
there is no difference between `FirstOrDefault` VS `Where().FirstOrDefault()` , your code is just code conciseness. http://stackoverflow.com/questions/8059285/c-sharp-linq-whereexpression-firstordefault-vs-firstordefaultexpression – Tim.Tang Aug 08 '14 at 02:17
EnumerableRowCollection does not have a definition for a Field and best extension System.Data.DataRowExtension.Field(System.Data.DataRow) has some invalid Extensions. IT is throwing this error in the Condition Operator where the Value is TRUE we take the FK_DataPoint from the VAR row. – user3920526 Aug 08 '14 at 15:00
I'm pretty sure you are not using the code I provided you – Selman Genç Aug 08 '14 at 15:01
I tried it doesn't work . So I am trying to find a work around. Thanks for your concern. I have posted the error. We are trying to get a Field of type but the FIELD is not present for the Variable row – user3920526 Aug 08 '14 at 15:11
I know the issue now. The LinQ query that I am using is filtering the correct DATAPOINT. I have this Query in a Loop which depends upon Account Period Selected, Fund , CLass many scenarios so it has to be completely changed. A big Headache anyways thanks for you prospective. – user3920526 Aug 20 '14 at 17:55

Using LINQ Query to Filter but it is selecting the particular Key Numerous times

1 Answers1