C# How to query Datatable by LINQ

Question

I know how to query data table by LINQ but broker names coming in the columns of data table for which i am not able to query it. see how data is coming. screen shot attached.

see BofA Merrill Lynch or Deutsche Bank these are broker name which is coming as a column.

This way i generally query data table by LINQ.

                ds.Tables[2].AsEnumerable().Where(x => x.Field<int>("EarningID") == earningsid
                    && x.Field<string>("EarningsType") == earningsType
                    && x.Field<string>("EarningsType") == earningsType
                    && x.Field<string>("DisplayInCSM") == DisplayInCSM
                    && x.Field<string>("DisplayInCSM") == DisplayInCSM
                    && x.Field<string>("Type") == Type
                    && x.Field<string>("Broker") == BrokerCode
                    && x.Field<string>("Period") == Period
                    );

Now tell me how could i add one more clause in where condition that broker name.

How could i mention column name in where clause like && x.Field<string>("Deutsche Bank") as a result LINQ query should return value say 19738.5877

in my above linq query i mention few condition in where but how could i mention Deutsche Bank column name in where and i should get value 19738.5877

please share the right LINQ query where i can also mention column name in where with other condition. thanks

Answer 1

please share the right LINQ query where i can also mention column name in where with other condition. thanks

It works just like any other column you're querying. I don't understand what the issue is here.

//Handle DbNull here...
&& (!x.IsDBNull("Deutsche Bank") && x.Field<decimal>("Deutsche Bank") == ??)

Personally it sounds to me like you want the value back. All the rows in your table have a column with this name so unless you want to filter out rows that have values in a range for this column then the where clause isn't useful here. Perhaps you want to filter out any row that doesn't have a value in this column, that's a standard null check.

&& !String.IsNullOrEmpty(x.Field<string>("Deutsche Bank"))

To get the value from the table just use the column name there as well.

//Assuming you have already filtered out the nulls...
foreach (var row in ds.Tables[2].Rows.OfType<DataRow>())
{
    var bankAmount = row.Fields<decimal>("Deutsche Bank");
}

Now tell me how could i add one more clause in where condition that broker name.

Not sure what you mean here but I'm going to guess you want to be able to filter on Deutsche Bank or Goldman or one of those other columns but you want to decide at runtime. In that case instead of using a string literal for the column name, move to a string variable and programmatically determine which one you want to use.

string brokerName = "Deutsche Bank";  //Picking one at random, use your own logic to get the right one

//Remaining code remains unchanged inside linq
&& !x.IsDBNull("brokerName")

//Same for later in code
foreach (var row in ds.Tables[2].Rows.OfType<DataRow>())
{
    var bankAmount = row.Fields<decimal>(brokerName);
}

Answer 2

I have done the job this way

                        string brokername = GetBrokerName(ds.Tables[0], BrokerCode);

                        int rowindex = ds.Tables[2].AsEnumerable().Select(x => x.Field<int?>("EarningID") == earningsid
                            && x.Field<string>("EarningsType") == earningsType
                            && x.Field<string>("DisplayInCSM") == DisplayInCSM
                            && x.Field<string>("Type") == Type
                            && x.Field<string>("Broker") == BrokerCode
                            && x.Field<string>("Period") == Period
                            ).ToList().FindIndex(col => col);

                        string broker_value = (ds.Tables[2].Rows[rowindex][brokername] == DBNull.Value ? "" : ds.Tables[2].Rows[rowindex][brokername].ToString());
                        if (broker_value!="")
                        {

                        }

Thanks