Querying a DataTable filtering for first char in field-CodePudding

I am new to Linq & I can't figure out this query.

I have a dataTable with has a field "RecordType" I want to get certain row fields filtered using the first letter of RecordType the filter is a string "A,B" supposed to get only records with RecordType starts with A or B

my query is:

string filter = "A,B";
var query = table.AsEnumerable()
                .Where (row => filter.Contains(row.Field<string>("RecordType").Trim().ToUpper().First()))
                .Select(s => s["FieldA"])
                .Distinct()
                .ToList();

I am getting "System.Data.DataRowExtensions.Field(...) returned null."

What am I doing wrong?

And how to select multiple fields in the Select ?

CodePudding user response：

To address the error that you received, we need to explicitly tell the field call that the return type could be null (aka: string?). Then, we need to check for a null value after the field call before trying to manipulate the string.

string filter = "A,B";
var query = table.AsEnumerable()
    .Where (row => filter.Contains(
        // get a nullable string from Field() and then use the "" value if it is null
        (row.Field<string?>("RecordType") ?? "")
            // pad the string to ensure there is at least one character to get in the substring call
            .PadRight(1)
            // get the first character of the string and upper-case it
            .Substring(0, 1).ToUpper()))
            .Select(s => s["FieldA"])
            .Distinct()
            .ToList();

CodePudding user response：

You need to filter using StartsWith()

var query = table.AsEnumerable()
       .Where (row => row.Field<string>("RecordType").StartsWith("A", StringComparison.InvariantCultureIgnoreCase) || row.Field<string>("RecordType").StartsWith("B", StringComparison.InvariantCultureIgnoreCase)))
       .Select(s => s["FieldA"])
       .Distinct()
       .ToList();

If you want to consider a filter variable, then you can combine above query with Any().

As your filter variable is separated by ,, I used .Split(',') to convert string to string array.

string filter = "A,B";
var query = table.AsEnumerable()
       .Where (row => filter.Split(',')
       .Any(x => row.Field<string>("RecordType").StartsWith(x, StringComparison.InvariantCultureIgnoreCase))
       .Select(s => s["FieldA"])
       .Distinct()
       .ToList();

CodePudding user response：

.First() will return the first row, not the first letter.

Try s.Substring(0, 1) == 'A' to check if the first character in the string is A