IQueryable returns null on invoking Count c#

Question

I have a problem trying to get the count out of the following query:

var usersView = PopulateUsersView(); //usersView is an IQueryable object
var foo = usersView.Where(fields => fields.ConferenceRole.ToLower().Contains("role"));

Where UsersView is a class which is populated from an EF entity called users (refer to the first line in the code above)

This is the class definition for the UsersView class:

public class UsersView
{
    public int UserId { get; set; }
    public string Title { get; set; }
    public string Name { get; set; }
    public string Surname { get; set; }
    public string Street1 { get; set; }
    public string Street2 { get; set; }
    public string City { get; set; }
    public string PostCode { get; set; }
    public string CountryName { get; set; }
    public string WorkPlaceName { get; set; }
    public string Gender { get; set; }
    public string EMail { get; set; }
    public string Company { get; set; }
    public string RoleName { get; set; }
    public string ConferenceRole { get; set; }
}

As I said trying to execute the line foo.Count() returns Null Exception and this might be because the ConferenceRole column allows Null in the database.

Now what I can't understand is that when I invoke the same query directly on the ObjectQuery the Count of records (i.e. invoking foo2.Count()) is returned without any exceptions.

var foo2 = entities.users.Where(fields => fields.ConferenceRole.ToLower().Contains("role"));

Is it possible to the same query above but using the IQueryable usersView object instead?

(It is crucial for me to use the usersView object rather than directly querying the entities.users entity)

EDIT

Below is the code from the PopulateUsersView method

private IQueryable<UsersView> PopulateUsersView()
    {
        using (EBCPRegEntities entities = new EBCPRegEntities())
        {
            var users = entities.users.ToList();
            List<UsersView> userViews = new List<UsersView>();
            foreach (user u in users)
            {
                userViews.Add(new UsersView()
                {
                    UserId = u.UserId,
                    Title = u.Title,
                    Name = u.Name,
                    Surname = u.Surname,
                    Street1 = u.Street1,
                    Street2 = u.Street2,
                    City = u.City,
                    PostCode = u.Post_Code,
                    CountryName = u.country.Name,
                    WorkPlaceName = u.workplace.Name,
                    Gender = u.Gender,
                    EMail = u.E_Mail,
                    Company = u.Company,
                    RoleName = u.roles.FirstOrDefault().Name,
                    ConferenceRole = u.ConferenceRole
                });
            }
            return userViews.AsQueryable();
        }
    }

Thanks

UPDATE...

Thanks guys I finally found a good answer to the difference between the IQueryable and the ObjectQuery objects.

As a solution I am checking if the ConferenceRole is null and then checking with the contains method as many of you guys have said.

Answer 1

My guess is that your PopulateUsersView() method is actually executing a query and returning an IQueryable Linq-to-Objects object - while the foo2 line executes the query only in the SQL layer. If this is the case, the obviously PopulateUsersView() is going to be quite an inefficient way to perform the Count

To debug this:

• can you post some code from PopulateUsersView()?
• can you try running both sets of code through the EF tracing provider to see what is executed in SQL? (see http://code.msdn.microsoft.com/EFProviderWrappers)

---

Update

@Ryan - thanks for posting the code to PopulateUsersView

Looks like my guess was right - you are doing a query which gets the whole table back into a List - and its this list that you then query further using Linq2Objects.

@ntziolis has provided one solution to your problem - by testing for null before doing the ToLower(). However, if your only requirement is to Count the non-empty items list, then I recommend you look at changing the PopulateUsersView method or changing your overall design. If all you need is a Count then it would be much more efficient to ensure that the database does this work and not the C# code. This is espeically the case if the table has lots of rows - e.g. you definitely don't want to be pulling 1000s of rows back into memory from the database.

---

Update 2

Please do consider optimising this and not just doing a simple != null fix.

Looking at your code, there are several lines which will cause multiple sql calls:

• CountryName = u.country.Name
• WorkPlaceName = u.workplace.Name
• RoleName = u.roles.FirstOrDefault().Name

Since these are called in a foreach loop, then to calculate a count of ~500 users, then you will probably make somewhere around 1501 SQL calls (although some roles and countries will hopefully be cached), returning perhaps a megabyte of data in total? All this just to calculate a single integer Count?

Answer 2

Try to check whether ConferenceRole is null before calling a method on it:

var foo = usersView.Where(fields => fields.ConferenceRole != null 
    && fields.ConferenceRole.ToLower().Contains("role"));

This will enable you to call the count method on the user view.

So why does it work against the ObjectQuery?

When executing the query against the ObjectQuery, LinqToSql is converting your query into proper sql which does not have problems with null values, something like this (it's sample markup sql only the actual query looks much different, also '=' is used rather than checking for contains):

SELECT COUNT(*) from USERS U WHERE TOLOWER(U.CONFERENCEROLE) = 'role'

The difference to the :NET code is: It will not call a method on an object but merely call a method and pass in the value, therefore no NullReference can occur in this case.

In order to confirm this you can try to force the .NET runtime to execute the SQL prior to calling the where method, by simply adding a ToList() before the .Where()

var foo2 = entities.users.ToList()
    .Where(fields => fields.ConferenceRole.ToLower().Contains("role"));

This should result in the exact same error you have seen with the UserView.

And yes this will return the entire user table first, so don't use it in live code ;)

UPDATE
I had to update the answer since I c&p the wrong query in the beginning, the above points still stand though.