WHERE clause in LINQ query seems to be failing

Question

Please forgive any naivety, I'm brand new to the world of C#. Let me know if I've left out helpful information.

I'm building a custom SiteMapProvider for the Customer Portal for Dynamics CRM 2011. First I initialize an array:

public Adx_webpage[] WebPages;

which gets populated thusly:

public MyProvider()
{
    WebPages = (FROM p in CrmContext.Adx_webpageSet WHERE p.Adx_HiddenFromSitemap != true SELECT p).ToArray();
}

Later, I try to query WebPages[] like so:

Adx_webpage[] childPages = (FROM p in WebPages WHERE p.adx_parentpageid.Id == page.Id SELECT p).ToArray();

When I run this through the debugger, I get a NullReferenceException which points at the condition in my WHERE clause saying that p.adx_parentpageid.Id is null, which is true for the home page of the site. Which leads to the question:

Why would this query turn up the home page as a p in my query? What am I misunderstanding?

Are you lazy loading your entities? It might be that you need to load the child objects if they are all null. — user1231231412, Dec 06 '11 at 21:48
What is the data type of p.adx_parentpageid.Id ? I am thinking that its actually p.adx_parentpageid that is null and it throws with you try to access the Id property. If p.adx_parentpageid can be null then you need to code for that condition. — user957902, Dec 06 '11 at 22:14
@saturdayplace Sorry I didn't add more info. before. Here's an article talking about this specifically for CRM 2011 http://msdn.microsoft.com/en-us/library/gg695791.aspx — user1231231412, Dec 06 '11 at 22:45
@JonC - Do the related entities need to be explicitly loaded even if they're of the same type as the original? It seems odd that my homepage would come up in the WHERE clause when it should be specifically excluded (it doesn't HAVE a parentpageid, so it can never == page.Id) — saturdayplace, Dec 06 '11 at 22:54

score 4 · Accepted Answer · answered Dec 06 '11 at 23:26

Your first line

    WebPages = (FROM p in CrmContext.Adx_webpageSet WHERE p.Adx_HiddenFromSitemap != true SELECT p).ToArray();

Will return you all pages that are not hidden in your site map. But this is also including your home page which has no parent id. So when your second query enumerates over this collection it is going to attempt to access this property which is null and throw a null reference exception. You just need to cater for this in your query.

Adx_webpage[] childPages = 
(FROM p in WebPages WHERE 
p.adx_parentpageid.Id != null &&
p.adx_parentpageid.Id == page.Id SELECT p).ToArray();

user957902 · Answer 2 · 2011-12-06T23:22:40.467

1

The issue is the .ToArray() on you first query. This linq query is creating a native query to your CrmContext provider:

WebPages = (FROM p in CrmContext.Adx_webpageSet WHERE p.Adx_HiddenFromSitemap != true SELECT p).ToArray();

The .ToArray() is causing the linq query to be run immediatly and returns a plain old array of objects. The array is not attached to your CrmContext object.

However, this query is using Linq to Objects to iterate over the array returned in the first call. It is not generateing a native query to your CrmContext provider.

Adx_webpage[] childPages = (FROM p in WebPages WHERE p.adx_parentpageid.Id == page.Id SELECT p).ToArray());

Its effectively the same as using a foreach loop to iterate over each element in the array so you have to worry about checking for possible null values that were returned by your first query.

edited Dec 06 '11 at 23:22

answered Dec 06 '11 at 23:16

user957902

3,010
14
18

OK, that makes sense. I'd like to hit the database only once, which is why I'm doing the first query to get ALL the pages in the DB, and then querying the resulting array thereafter; LINQ seems to make a lot of sense for that. Is there a better way of doing it? – saturdayplace Dec 06 '11 at 23:26
Linq to Objects is fine for that. You just have to remember that it is just running .net code underneath and you are not shielded from null references like you would be for a native query. If an object has no relationship its not going to be simply excluded, its going to throw a null reference exception. – user957902 Dec 06 '11 at 23:39

score 1 · Answer 3 · answered Dec 06 '11 at 23:24

I don't understand your question. You're saying that the homepage is a web page and it is NOT hidden from the sitemap, so why wouldn't you expect the homepage to show up in your query as a p?

Anyway, you can simply skip anything with p == null:

Adx_webpage[] childPages = (FROM p in WebPages
                            WHERE p.adx_parentpageid != null &&
                                  p.adx_parentpageid.Id == page.Id
                            SELECT p).ToArray();

WHERE clause in LINQ query seems to be failing

3 Answers3