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

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

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

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

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

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

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

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

Matt_Hirst · 13 Aug 2014 at 10:53

Removed it as I presumed I needed to select something.

regards,

Matt

Matt_Hirst · 13 Aug 2014 at 17:21

LINQ just doesn't seem to be able to do this, can't understand why, have now created the code in FETCHXML and going to give that a go.

regards,

Matt

Matt_Hirst · 13 Aug 2014 at 19:44

Thanks for your replies.

Maybe it's just me expecting too much - I just viewed this as something trivial to do and I can't believe I am getting no where after two days.

Will have a read through the links, thanks for them.

Once again thanks for you time on this.

regards,

Matt

Matt_Hirst · 14 Aug 2014 at 09:46

Think we have it sorted with LINQ, well early testing appears to work.

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
                                                 }).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 != null)
                                {
                                    if (String.IsNullOrEmpty(_txttowrite))
                                    {
                                        _txttowrite = item.product_name.Name;
                                    }
                                    else
                                    {
                                        _txttowrite += ", " + item.product_name.Name;
                                    }
                                }
                            }
                        }

p.orb_ParentProductid is a lookup field and seperating out the null check seems to have cured the problem.

Thanks for everyones help on this.

regards,

Matt