?
Solved

Linq to Dataset How to handle Null column values?

Posted on 2010-09-16
4
Medium Priority
?
1,149 Views
Last Modified: 2013-11-11
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

0
Comment
Question by:kruegerste
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 1500 total points
ID: 33694390
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
 
LVL 4

Author Comment

by:kruegerste
ID: 33694552
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
 
LVL 4

Author Comment

by:kruegerste
ID: 33695088
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
 
LVL 4

Author Closing Comment

by:kruegerste
ID: 33695592
You were close, thanks.  Right idea, just changed to look for column value not null. Works
0

Featured Post

TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question