Solved

Linq to Dataset How to handle Null column values?

Posted on 2010-09-16
4
1,122 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
  • 3
4 Comments
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

758 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now