LINQ question

Matt_Hirst · 12 Aug 2014 at 13:05

I have the following code

Code:

using (GeneratedCode orgcontext = new GeneratedCode(service))
                    {

                        var retrievedProducts = (from p in orgcontext.ProductSet
                                                 join qd in orgcontext.QuoteDetailSet
                                                 on p.ProductId equals qd.sp_SellingProductId.Id
                                                 where qd.QuoteId.Id == entity.Id && p.orb_ParentProductid != null                                                
                                                 select new
                                                 {
                                                     product_name = p.orb_ParentProductid.Name
                                                 }).Distinct();

                        var _txttowrite = string.Empty;
                        if (retrievedProducts != null)
                        {
                            // for each item returned write the output to a string.

                            foreach (var item in retrievedProducts)
                            {
                                if (item.product_name != "")
                                {
                                    if (String.IsNullOrEmpty(_txttowrite))
                                    {
                                        _txttowrite = item.product_name;
                                    }
                                    else
                                    {
                                        _txttowrite += ", " + item.product_name;
                                    }
                                }
                            }


                        }
                        // Write data to current quote.
                        Quote currentQuote = (Quote)service.Retrieve(Quote.EntityLogicalName, entity.Id, new ColumnSet(false));

                        currentQuote.ancon_quoteproducts = _txttowrite;
                        currentQuote.ancon_activatedate = DateTime.Now;

                        service.Update(currentQuote);
                    }
                }

It all works as expected however one of the returned values in the query is a null how can I filter these out or deal with these, not touched programming for a short while and cant seem to crack this.

regards,

Matt

Dj_Jestar · 12 Aug 2014 at 13:46

suffix with

Code:

&& p.orb_ParentProductid.Name != null

Now that that is done, we can look at refactoring the rest of your code.

All of this:

Code:

 var _txttowrite = string.Empty;
                        if (retrievedProducts != null)
                        {
                            // for each item returned write the output to a string.

                            foreach (var item in retrievedProducts)
                            {
                                if (item.product_name != "")
                                {
                                    if (String.IsNullOrEmpty(_txttowrite))
                                    {
                                        _txttowrite = item.product_name;
                                    }
                                    else
                                    {
                                        _txttowrite += ", " + item.product_name;
                                    }
                                }
                            }


                        }

can become:

Code:

var _txttowrite = retrievedProducts == null
  ? string.Empty
  : string.Join(", ", retrievedProducts.Where(x => !string.IsNullOrWhiteSpace(x.product_name).Select(x => x.product_name));

Matt_Hirst · 12 Aug 2014 at 14:11

Have updated the code to

Code:

var retrievedProducts = (from p in orgcontext.ProductSet
                                 join qd in orgcontext.QuoteDetailSet
                                 on p.ProductId equals qd.sp_SellingProductId.Id
                                 where qd.QuoteId.Id == entity.Id && p.orb_ParentProductid.Name != null                                           
                                 select new
                                 {
                                      product_name = p.orb_ParentProductid.Name
                                  }).Distinct();

This code fails with a "Invalid 'where' condition. An entity member is invoking an invalid property or method"

any ideas?

With this line

Code:

var _txttowrite = retrievedProducts == null? string.Empty: string.Join(", ", retrievedProducts.Where(x => !string.IsNullOrWhiteSpace(x.product_name).Select(x => x.product_name));

I get an error in the IDE at the !string.IsNullOrWhiteSpace(x.product_name).Select(x => x.product_name)); when I hover over it says bool does not contain a definition for select accepting a first argument of type bool.

regards,

Matt

Haircut · 12 Aug 2014 at 14:28

The second error is simply mis-aligned brackets.
You need a closing bracket after the string.IsNullOrWhitespace

Dj_Jestar · 12 Aug 2014 at 14:30

ah yes, it won't work with nested properties. And an internet point to Haircut

Also you didn't quite do it right as I meant literally suffix as in copy and paste that onto the end not replace the ending.

Anywho.. try replacing the entire method with this:

Code:

var retrievedProducts = orgcontext.ProductSet
	.Join(orgcontext.QuoteDetailSet, 
		p => p.ProductId, 
		q => q.sp_SellingProductId.Id, 
		(p, q) => p.orb_ParentProductid.Name)
	.Where(s => !string.IsNullOrWhiteSpace(s))
	.Distinct();

var currentQuote = (Quote)service.Retrieve(Quote.EntityLogicalName, entity.Id, new ColumnSet(false));

currentQuote.ancon_quoteproducts = string.Join(", ", retrievedProducts.Where(x => !string.IsNullOrWhiteSpace(x.product_name).Select(x => x.product_name));
currentQuote.ancon_activatedate = DateTime.Now;

service.Update(currentQuote);

Matt_Hirst · 12 Aug 2014 at 14:45

Thanks for all your help.

Tried that code and x.product_name is erroring with

Error 1 'string' does not contain a definition for 'product_name' and no extension method 'product_name' accepting a first argument of type 'string' could be found (are you missing a using directive or an assembly reference?) E:\MH Test Software\C#\MH-Plugins\Plugin_Project\Plugin_Project\PreQuoteUpdate.cs 59 136 Plugin_Project

Also is this line

currentQuote.ancon_quoteproducts = string.Join(", ", retrievedProducts.Where(x => !string.IsNullOrWhiteSpace(x.product_name).Select(x => x.product_name));

missing a closing bracket?

Matt

Dj_Jestar · 12 Aug 2014 at 14:49

yes and yes. What a wally, eh?

Both errors should be fixed by changing "string.IsNullOrWhiteSpace(x.product_name)" to "string.IsNullOrWhiteSpace(x))"

Note the extra closing parenthesis.

Matt_Hirst · 12 Aug 2014 at 14:57

Have updated the quote and there are no errors in the IDE.

Code:

                        var retrievedProducts = orgcontext.ProductSet
	                                            .Join(orgcontext.QuoteDetailSet, 
		                                        p => p.ProductId, 
		                                        q => q.sp_SellingProductId.Id, 
		                                        (p, q) => p.orb_ParentProductid.Name)
	                                            .Where(s => !string.IsNullOrWhiteSpace(s))
	                                            .Distinct();

                        var currentQuote = (Quote)service.Retrieve(Quote.EntityLogicalName, entity.Id, new ColumnSet(false));

                        currentQuote.ancon_quoteproducts = string.Join(", ", retrievedProducts.Where(x => !string.IsNullOrWhiteSpace(x)).Select(x => x));
                        currentQuote.ancon_activatedate = DateTime.Now;

                        service.Update(currentQuote);

When I run the code I receive the error Invalid 'Where' condition. An entity member is invoking an invalid property or method.

regards,

Matt

Matt_Hirst · 12 Aug 2014 at 15:06

In your linq statement, although I find it hard to follow where are you filtering for the QuoteId.Id == entity.Id?

Or am I being stupid?

regards,

Matt

Dj_Jestar · 12 Aug 2014 at 15:12

It would seem EF does not support IsNullOrWhiteSpace. Change it for a null check.

Matt_Hirst · 12 Aug 2014 at 15:20

I am lost if I am honest now... :confused:

What do I need to change and where, sorry for being a bit thick here.

Matt

Dj_Jestar · 12 Aug 2014 at 16:45

Code:

!string.IsNullOrWhiteSpace(x)

that

Code:

x != null

to that

Matt_Hirst · 12 Aug 2014 at 17:01

Updated code to

Code:

                        var retrievedProducts = orgcontext.ProductSet
                        .Join(orgcontext.QuoteDetailSet,
                        p => p.ProductId,
                        q => q.sp_SellingProductId.Id,
                        (p, q) => p.orb_ParentProductid.Name)
                        .Where(s => !string.IsNullOrWhiteSpace(s))
                        .Distinct();

                        var currentQuote = (Quote)service.Retrieve(Quote.EntityLogicalName, entity.Id, new ColumnSet(false));

                        currentQuote.ancon_quoteproducts = string.Join(", ", retrievedProducts.Where(x => x!= null).Select(x => x));
                        currentQuote.ancon_activatedate = DateTime.Now;

                        service.Update(currentQuote);

Builds in IDE, but when I run I get:

"Invalid Where condition. An entity member is invoking an invalid property or method."

regards,

Matt

Dj_Jestar · 12 Aug 2014 at 17:02

There's the other IsNullOrWhiteSpace that needs replacing.

Dude.. I'm starting to feel like I am chewing your food for you here.

Matt_Hirst · 13 Aug 2014 at 09:06

Thanks for all your help, as said above I am back looking at code after a break, however, code is now:

Code:

                        var retrievedProducts = orgcontext.ProductSet
                        .Join(orgcontext.QuoteDetailSet,
                        p => p.ProductId,
                        q => q.sp_SellingProductId.Id,
                        (p, q) => p.orb_ParentProductid.Name)
                        //.Where(s => !string.IsNullOrWhiteSpace(s))
                        .Where(s => s!= null)
                        .Distinct();

                        var currentQuote = (Quote)service.Retrieve(Quote.EntityLogicalName, entity.Id, new ColumnSet(false));

                        currentQuote.ancon_quoteproducts = string.Join(", ", retrievedProducts.Where(x => x!= null).Select(x => x));
                        currentQuote.ancon_activatedate = DateTime.Now;

                        service.Update(currentQuote);

When it runs it fails with the message:

Invalid 'where' condition. An entity member is invoking an invalid property or method.

regards,

Matt

touch · 13 Aug 2014 at 09:36

This line:

Code:

.Where(s => s!= null)

I dont think you can check if the whole object is null like that, you need to check for one of the specific attributes.
Something like:

Code:

.Where(s => s.ProductId != null)

Haircut · 13 Aug 2014 at 09:38

What have you tried so far?
It seems that you're not actually debugging anything yourself and just posting the errors up here.

Are you using Dynamics CRM?
Googling that error message suggests it's coming from that.

I'm not sure what the LINQ query provider is like for that, but it's entirely up possible (though I would have thought unlikely) that it doesn't support comparison with null.

Matt_Hirst · 13 Aug 2014 at 10:12

Thanks for the reply yes it is Dynamics CRM so not very easy to debug with unfortunately - or at least I am yet to find a way to do it.

I have gone back to my original code, which is:

Code:

var retrievedProducts = (from p in orgcontext.ProductSet
                                                 join qd in orgcontext.QuoteDetailSet
                                                 on p.ProductId equals qd.sp_SellingProductId.Id
                                                 where qd.QuoteId.Id == entity.Id
                                                 select new
                                                 {
                                                     product_name = p.orb_ParentProductid.Name
                                                 });//.Distinct();
                       
                        var _txttowrite = "";
                        if (retrievedProducts != null)
                        {
                                 //for each item returned write the output to a string.

                            foreach (var item in retrievedProducts.Where(a => a.product_name != null).Select(a => a))
                            {

                                if (String.IsNullOrEmpty(_txttowrite))
                                {
                                    _txttowrite = item.product_name;
                                }
                                else
                                {
                                    _txttowrite += ", " + item.product_name;
                                }                                
                            }
                        }

This line I have modified:

Code:

foreach (var item in retrievedProducts.Where(a => a.product_name != null).Select(a => a))

from

Code:

foreach (var item in retrievedProducts)

from a google session and Stackoverflow suggests here: http://stackoverflow.com/questions/...set-to-an-instance-of-an-object-c-sharp-error

running this new code I get the error:

The method 'Where' cannot follow the method 'Select' or is not supported. Try writing the query in terms of supported methods or call the 'AsEnumerable' or 'ToList' method before vcalling unsupported methods.

I remember having issues like this previous with LINQ but like I said it is a while since I have worked with LINQ.

regards,

Matt

touch · 13 Aug 2014 at 10:41

Matt_Hirst said:
The method 'Where' cannot follow the method 'Select' or is not supported.

What is the .select(a => a) actually doing? do you need to have it there?

Matt_Hirst · 13 Aug 2014 at 10:53

Removed it as I presumed I needed to select something.

regards,

Matt