How to write LINQ query to split and find the column min and maximum and return based on string values.

Question

0.00/5 (No votes)

See more:

Hi,

How to find minimum and maximum value and filter row by row based on the parameter.

1. I have data as follow:

C#

DataTable dt = new DataTable();
        dt.Columns.AddRange(new[] { new DataColumn("Studentno"),new DataColumn("GRADE"),new DataColumn("SUB B"), new DataColumn("SUB C(MIN)"), new DataColumn("SUB C(MAX)"),
        new DataColumn("SUB D(MIN)") ,new DataColumn("SUB D(MAX)"), new DataColumn("FROM"),new DataColumn("TO"),new DataColumn("SUB E(MIN)"), new DataColumn("SUB E(MAX)") });
        dt.Rows.Add(101, "A", "100", "80", "200", "200", "550", 20, "100", "200", "500");
        dt.Rows.Add(101, "B", "100", "90", "300", "270", "200", 20, "100", null, null);
        dt.Rows.Add(101, "B", "100", "200", "300", "100", "250", 20, "100", "100", "100");
        dt.Rows.Add(101, null, "100", "200", "300", "100", "200", 20, "100", null, null);

2. I have two input strings, which i have to "convert" into filter criteria:

C#

string string1 = "GRADE#SUB B";
string string2 = "SUB C=100#SUB D=250#SUB E=300";

First i want to get the row which contains in the range between within 100 ((i.e) SUB C (MIN) and SUB C (MAX)) (take string2 and split the string and take first in that. SUB C=100).

It should return two records:

101,"A", "100", "100", "50", "200", "250", "700", 20, "100"
101, "B", "100", "150", "70", "250", "200", "100", 20, "100"

3. After that, with in the above two records, i want to filter one more condition need to check. This is also need to check within Min and Max of SUB D=300.
It should return only one record:

101,"A", "100", "100", "50", "200", "250", "700", 20, "100"

4. In that, finally i want to get the records for the field of GRADE and SUB B.

So the final output would be

GRADE=A
SUB B=100

Note : Filter and check row within the row using string2 and display the result in the form of string1.

pls. let us know if you need any more details.

The output i want either simple C# or Linq Query..???

What I have tried:

DataTable dt = new DataTable();
        dt.Columns.AddRange(new[] { new DataColumn("Studentno"),new DataColumn("GRADE"),new DataColumn("SUB B"), new DataColumn("SUB C(MIN)"), new DataColumn("SUB C(MAX)"),
        new DataColumn("SUB D(MIN)") ,new DataColumn("SUB D(MAX)"), new DataColumn("FROM"),new DataColumn("TO"),new DataColumn("SUB E(MIN)"), new DataColumn("SUB E(MAX)") });
        dt.Rows.Add(101, "A", "100", "80", "200", "200", "550", 20, "100", "200", "500");
        dt.Rows.Add(101, "B", "100", "90", "300", "270", "200", 20, "100", null, null);
        dt.Rows.Add(101, "B", "100", "200", "300", "100", "250", 20, "100", "100", "100");
        dt.Rows.Add(101, null, "100", "200", "300", "100", "200", 20, "100", null, null);

string string1 = "GRADE#SUB B";
string string2 = "SUB C=100#SUB D=250#SUB E=300";
string[][] subCD = string2.Split('#').Select(s => s.Split('=')).ToArray();

<pre> //Split the conidtion string and format the filter expression
            string[] condition = string2.Split('#');
            string filter = "";
            foreach (string subcondition in condition)
            {
                if (!String.IsNullOrEmpty(filter))
                {
                    filter += " AND ";
                }
                string[] subconditions = subcondition.Split('=');
                filter += "[" + subconditions[0] + "] = '" + subconditions[1] + "'"; 
            }

            //filter the datatable
            var result = dt.Select(filter);

            //split the column names
            string[] fields = string1.Split('#');
            //get the column value from the filter result
            string Grade = result.FirstOrDefault().Field<string>(fields[0]);
            string SUBB = result.FirstOrDefault().Field<string>(fields[1]);

Posted 11-Oct-17 0:55am

gani7787

Updated 11-Oct-17 7:20am

Maciej Los

v4

Add a Solution

Comments

OriginalGriff 11-Oct-17 7:36am

And?
How much of that have you done?
What help do you need?

gani7787 11-Oct-17 8:08am

The code is not filtering properly.

i want to convert into linq in the above code.

because i have more where condition..

that's why...??

Maciej Los 11-Oct-17 11:01am

It won't work, because your filter criteria does not match to the names of columns. There are not columns: SUB C, SUB D and even SUB E. There are columns: SUB C(MIN), SUB C(MAX), SUB D(MIN), SUB D(MAX), etc. Got it?
If i understand you well, you have to define search criteria as follow: SUB C(MIN)>=100 AND C(MAX)<=100 and so on...

Maciej Los 11-Oct-17 12:43pm

Another issue is data type, for example: this equation is impossible [SUB C(MIN)]>="100" because "100" is not a number, but string!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2017-10-11T07:19:00

Solution 1

First of all, please read my comment to your question... Considering, that i've been forced to make indispensable corrections, check below code:

C#

DataTable dt = new DataTable();
        dt.Columns.AddRange(new[]
		{
			new DataColumn("Studentno", typeof(int)), new DataColumn("GRADE", typeof(string)),
			new DataColumn("SUB B", typeof(int)),
			new DataColumn("SUB C(MIN)", typeof(int)),
			new DataColumn("SUB C(MAX)", typeof(int)),
        	new DataColumn("SUB D(MIN)", typeof(int)),
			new DataColumn("SUB D(MAX)", typeof(int)),
			new DataColumn("FROM", typeof(int)),
			new DataColumn("TO", typeof(int)),
			new DataColumn("SUB E(MIN)", typeof(int)),
			new DataColumn("SUB E(MAX)", typeof(int))
		});
        dt.Rows.Add(new object[]{101, "A", 100, 80, 200, 200, 550, 20, 100, 200, 500});
        dt.Rows.Add(new object[]{101, "B", 100, 90, 300, 270, 200, 20, 100, null, null});
        dt.Rows.Add(new object[]{101, "B", 100, 200, 300, 100, 250, 20, 100, 100, 100});
        dt.Rows.Add(new object[]{101, null, 100, 200, 300, 100, 200, 20, 100, null, null});
 

string iniCriteria1 = "SUB C=100#SUB D=250#SUB E=300";
string iniCriteria2 = "GRADE#SUB B";

var sCriteria1 = String.Join(" AND ", iniCriteria1.Split(new string[] {"#"}, StringSplitOptions.RemoveEmptyEntries)
	.Select(x=> "([" + x.Split('=')[0] + "(MIN)]<=" + x.Split('=')[1] + " AND [" +
					x.Split('=')[0] + "(MAX)]>=" + x.Split('=')[1] + ")").ToList());
//sCriteria1.Dump();

var rows = dt.Select(sCriteria1);
//rows.Dump();

foreach(DataRow r in rows)
{
	var cols = iniCriteria2.Split(new string[]{"#"}, StringSplitOptions.RemoveEmptyEntries);
	foreach(string c in cols)
	{
		Console.WriteLine("{0}={1}", c, r[c]);
	}
}

Result:

GRADE=A
SUB B=100

Note: i've tested above code by using LinqPad[^]

Posted 11-Oct-17 7:19am

Maciej Los

Updated 11-Oct-17 7:22am

v2

Comments

gani7787 12-Oct-17 1:02am

Thanks for your excellent code..

is it possible to add where condition in that for each loop.

i want to more filter like below.. (using linq is used earlier)

where

let FrS = Convert.ToDouble(row.Field<double>("FROM"))
let ToS = Convert.ToDouble(row.Field<double>("TO"))
row.Field<string>("GRADE") == grd &&
val >= FrS && val <= ToS

Note : What happened if the column value is null. how to handle the null condition..??

I have tried the below code. i'm getting error. cannot find column A1.

// For From and To comparison
string FrS = "From";
string ToS = "To";
double msize = 20;
sqlcmd += " and ( [" + FrS + "] < " + msize + " and [" + ToS + "] > " + msize + " ) ";

// For dept
string strdeptvalue = "A1";
string strdept = "Dept";
sqlcmd += " and ( [" + strdept + "] = " + strdeptvalue + ") ";

How to change your code like above conditions...?????

below is my updated datatable..

DataTable dt = new DataTable();
dt.Columns.AddRange(new[] { new DataColumn("Studentno",typeof(int)),new DataColumn("GRADE"),new DataColumn("SUB B",typeof(int)),
new DataColumn("SUB C(MIN)",typeof(int)), new DataColumn("SUB C(MAX)",typeof(int)),new DataColumn("SUB D(MIN)",typeof(int)) ,
new DataColumn("SUB D(MAX)",typeof(int)), new DataColumn("FROM"),new DataColumn("TO"),new DataColumn("SUB E(MIN)",typeof(int)),
new DataColumn("SUB E(MAX)",typeof(int)),new DataColumn("Dept",typeof(string)) });
dt.Rows.Add(101, "A", 100, 80, 200, 200, 550, 20, 100, 200, 500,"A1");
dt.Rows.Add(101, "B", 100, 90, 300, 270, 200, 20, 100, null, null,"B1");
dt.Rows.Add(101, "B", 100, 200, 300, 100, 250, 20, 100, 100, 100,"C1");
dt.Rows.Add(101, null, 100, 200, 300, 100, 200, 20, 100, null, null,"D1");

gani7787 12-Oct-17 1:39am

my complete code is below..tested and getting error in column not found.

DataTable dt = new DataTable();
dt.Columns.AddRange(new[] { new DataColumn("Studentno",typeof(int)),new DataColumn("GRADE"),new DataColumn("SUB B",typeof(int)),
new DataColumn("SUB C(MIN)",typeof(int)), new DataColumn("SUB C(MAX)",typeof(int)),new DataColumn("SUB D(MIN)",typeof(int)) ,
new DataColumn("SUB D(MAX)",typeof(int)), new DataColumn("FROM"),new DataColumn("TO"),new DataColumn("SUB E(MIN)",typeof(int)),
new DataColumn("SUB E(MAX)",typeof(int)),new DataColumn("Dept",typeof(string)) });
dt.Rows.Add(101, "A", 100, 80, 200, 200, 550, 20, 100, 200, 500,"A1");
dt.Rows.Add(101, "B", 100, 90, 300, 270, 200, 20, 100, null, null,"B1");
dt.Rows.Add(101, "B", 100, 200, 300, 100, 250, 20, 100, 100, 100,"C1");
dt.Rows.Add(101, null, 100, 200, 300, 100, 200, 20, 100, null, null,"D1");

List<string> AllColumns1 = new List<string>() { "GRADE", "SUB B" };

string[] AllColumns = AllColumns1.ToArray();
string string2 = "SUB C=100#SUB D=250#SUB E=300";

string[] paras = string2.Split('#');
List<datarow> list = new List<datarow>();
string sqlcmd = "";

for (int i = 0; i < paras.Length; i++)
{
string[] par = paras[i].Split('=');
string mixpar = par[0] + "(MIN)";
string maxpar = par[0] + "(MAX)";
int mvalue = Convert.ToInt32(par[1]);
sqlcmd += " ( [" + mixpar + "] < " + mvalue + " and [" + maxpar + "] > " + mvalue + " ) ";
if ((i + 1) != paras.Length)
{
sqlcmd += " and ";
}

}

// For From and To comparison
string FrS = "From";
string ToS = "To";
double msize = 20;
sqlcmd += " and ( [" + FrS + "] < " + msize + " and [" + ToS + "] > " + msize + " ) ";

// For dept
string strdeptvalue = "A1";
string strdept = "Dept";
sqlcmd += " and ( [" + strdept + "] = " + strdeptvalue + ") ";

DataRow[] result = dt.Select(sqlcmd);
List<string> result1 = new List<string>();

for (int i = 0; i < AllColumns.Length; i++)
{

foreach (DataRow row in result)
{
result1.Add(AllColumns[i] + "=" + row[AllColumns[i]]);
}

}

Maciej Los 12-Oct-17 2:01am

Sorry, but these questions wasn't originally posted.
But:
1) "What happened if the column value is null. how to handle the null condition..??".
Well, it's your job. You have to check it.

2) "How to change your code like above conditions.?".
I think i already answered this question. I showed you up the way how to achieve that. Now, you have to study my code.

Tip: this is a very good practice to accept helpful answers (use green button).

Cheers,
Maciej

gani7787 12-Oct-17 2:17am

Yes. it tried different logic and code.

Thanks..this also will be helpful definitely..

but, what is the error.

i'm giving correct column name and it's value. then why it's showing the error.

Actually [A1] is not a column. it is a value..???

Maciej Los 12-Oct-17 2:25am

Use debugger. "A1" is not a column (i did not see it in the definition of datatable), it's a value of "Dept" column!

gani7787 12-Oct-17 2:48am

Yes. i debugg and found the solution.

we need to enclose '(single quotes) before and after of A1.

Thanks buddy...

Maciej Los 12-Oct-17 3:31am

You're very welcome. :)

Cheers,
Maciej