How to handle a linq statement that queries a datatable where no rows are returned

Question

I have a simple datatable like this:

F1   F2
A     1
B     2

I want to return the value of F2 where F1 is a certain value. This code works when the row exists:

DataTable dt = null;
DataRow dr = null;
string strTemp = null;

//define datatable
dt = new DataTable();
dt.Columns.Add("F1");
dt.Columns.Add("F2");

//add 2 rows
dr = dt.NewRow();
dr["F1"] = "1";
dr["F2"] = "A";
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["F1"] = "2";
dr["F2"] = "B";
dt.Rows.Add(dr);

//this returns "A"
strTemp =
    dt.AsEnumerable().
    Where(x => x.Field<string>("F1") == "1").
    FirstOrDefault().Field<string>("F2");

But I get a value cannot be null error when I try to search for a value that does not exist:

strTemp =
    dt.AsEnumerable().
    Where(x => x.Field<string>("F1") == "123"). //this gets an error because 123 does not exist
    FirstOrDefault().Field<string>("F2");

How can I handle this? (Also is this best practice for doing the search?)

Does this existing post and response help: https://stackoverflow.com/questions/48350739/c-sharp-datatable-select-columns-with-null-value-linq — DanielG, Dec 08 '21 at 18:55
"How can I handle this?" check for a null value before asking for the value of the `F2` field. There are multiple ways to do that, depending on what you want the value of `strTemp` to be in that scenario. — D Stanley, Dec 08 '21 at 18:57
`FirstOrDefault()?.Field("F2")` would be the easiest, but you need to make sure you know _why_ it works. — D Stanley, Dec 08 '21 at 19:00
@ D Stanley - Yes, that's basically my question. How do I check for no records returned? — Madison320, Dec 08 '21 at 19:01
`FirstOrDefault()` will return null if no records are found. So capture that first and check it for null. If it's not null, then get the value of the `F2` field. The `?.` operator encapsulates that if you want the result to be `null` if no record is found. — D Stanley, Dec 08 '21 at 19:22

Caius Jard · Accepted Answer · 2021-12-08T19:10:32.573

Like this:

var strTemp =
    dt.AsEnumerable()
    .FirstOrDefault(x => x.Field<string>("F1") == "123")
    ?.Field<string>("F2");

It's not the .Where that is erroring; it's the attempt to call .Field() on the null returned from .FirstOrDefault(). If you use ?.Field instead, then the method won't actually be called if FOD has returned null, it'll just zip straight to setting strTemp to null. See null conditional operators in the fine manual for more info

(So bear that in mind before you use strTemp)

How do I check for no records returned?

That's a slightly different question, and probably obviated by the above, but you can look at Enumerable.Any if you want to know for other contexts

Also is this best practice for doing the search?

For datatables, it's more performant to search on the primary key, or if you will do a lot of searching in a column, index it with perhaps:

var lu = table.Rows.Cast<DataRow>().ToLookup(r => (string)r["F1"])

This would generate a lookup that you can use to quickly search for all DataRow with "Hello" in F2:

foreach(var ro in lu["Hello"])
  ...

@Madison320 NP. I added some other bits you asked about too.. — Caius Jard, Dec 08 '21 at 19:13

How to handle a linq statement that queries a datatable where no rows are returned

1 Answers1