Linq to Dataset How to handle Null column values?

How do you handle null column values in Linq to Dataset?  If the column in the customers table below has any null values, the statement stops and the query is empty.  

Thanks.

//find all of the rows that have values that start with the provided prefix
                EnumerableRowCollection<DataRow> query =
                    from customer in customerData.Tables["customer"].AsEnumerable()
                    where customer.Field<string>(columnName).ToLower().StartsWith(prefix.ToLower())
                    select customer;

Open in new window

LVL 4
kruegersteAsked:
Who is Participating?
 
käµfm³d 👽Connect With a Mentor Commented:
What about this?
//find all of the rows that have values that start with the provided prefix
EnumerableRowCollection<DataRow> query =
    from customer in customerData.Tables["customer"].AsEnumerable()
    where customer != null && !Convert.IsDBNull(customer) && customer.Field<string>(columnName).ToLower().StartsWith(prefix.ToLower())
    select customer;

Open in new window

0
 
kruegersteAuthor Commented:
No, I'm talking about the column in the customer table, not the customer table itself.  

It basically loops through each row of the datatable and everything is good until the column its checking is null, then the whole statement ends and the query enumerator is nothing.  
0
 
kruegersteAuthor Commented:
Below is the full statement.  In the GetDistinctItems as it loops through each datarow, it executes this line in the Linq and returns a boolean value:

where customer.Field<string>(columnName).ToLower().StartsWith(prefix.ToLower())

and everything is fine, items are added to string array until  customer.Field<string>(columnName)  is null value, then it errors out and throws exception below:

'customer.Field<string>(columnName)' is null      

How do we execute this Linq query on a column in a table that may have null values?  Obviously these are matches so I would like them to be ignored instead of throwing exception.

 try
            {
                UtilityBF utilityBF = new UtilityBF();
                CustomerData customerData = new CustomerData();
                customerData = GetCustomersAll();

                //find all of the rows that have values that start with the provided prefix
                EnumerableRowCollection<DataRow> query =
                    from customer in customerData.Tables["customer"].AsEnumerable()
                    where customer.Field<string>(columnName).ToLower().StartsWith(prefix.ToLower())
                    select customer;

                return utilityBF.GetDistinctQueryItems(query, columnName, count);
            }
            catch (HandledException ex)
            {
                throw ex;
            }

===================================================

public string[] GetDistinctQueryItems(EnumerableRowCollection<DataRow> query, string columnName, int count)
        {
                List<string> items = new List<string>();

                foreach (DataRow dataRow in query)
                {
                    string value = dataRow[columnName].ToString();
                    if (!items.Contains(value))
                    {
                        items.Add(value);
                    }
                }
                if (items.Count == 0)
                {
                    items.Add("no match");
                }

                return items.ToArray();
        }

Open in new window

0
 
kruegersteAuthor Commented:
You were close, thanks.  Right idea, just changed to look for column value not null. Works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.