How can I get a column out of this join - LINQ

I got help from Fernando:  

http://www.experts-exchange.com/acceptAnswer.jsp?aid=40502019

I have this LINQ. You see:

join pp in _dataContext.ProductsInPromotion on  data.ProductId equals pp.ProductId into pInfoGroup

Open in new window


I want to select pp.Rebate from it. This column comes from the left join.

This is what I have

var consumerList = (from data in _dataContext.Consumers
               join s in _dataContext.StateProvinces on data.StateId equals s.Id
               join c in _dataContext.Countries on data.CountryId equals c.Id
               join p in _dataContext.Promotions on data.PromotionId equals p.Id
               join t in _dataContext.SubmissionStatus on data.SubmissionStatusId equals t.Id
               join r in _dataContext.Products on data.ProductId equals r.Id
                             join pp in _dataContext.ProductsInPromotions on data.ProductId equals pp.ProductId into pInfoGroup
               select new
               {
                   data.Id,
                   data.FirstName,
                   data.LastName,
                   data.Address,
                   data.City,
                   data.PostalCode,
                   state = s.Name,
                   stateId = s.Id,
                   country = c.Name,
                   countryId = c.Id,
                   data.EmailAddress,
                   promotion = p.Code,
                   data.PhoneNumber,
                   data.PurchaseDate,
                   data.RetailLocation,
                   data.ProductSerialNumber,
                   status = t.Code,
                   
                   product = r.ProductModel,
                   data.ConfirmationCode,
                   t.Code,
                   data.CreateDate,
                   
                   PromotionId = p.Id,
                   SubmissionStatusId = t.Id,
                   ProductId = r.Id,
                   data.Filename,
                   PInfo = pInfoGroup.DefaultIfEmpty()



               }).Where(x => x.Id == id);

Open in new window

LVL 8
CamilliaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Hi Camilla;

I suspect that PInfo is returning a collection of ProductsInPromotions for each iteration of the query. So returning a single column of rebate from ProductsInPromotions will mean a collection of rebate in PInfo. Is that what you want? Or is PInfo always returning one row from the collection?

// Example if PInfo is returning a collection this line of code will return one value being the first in the collection
PInfo = pInfoGroup.Select(p => p.Rebate).FirstOrDefault()

Open in new window

0
CamilliaAuthor Commented:
let me try. Thanks, as always.
0
CamilliaAuthor Commented:
No, I don't see "Rebate" in intellisense. I can move it to a stored proc but I really don't want to.
0
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Fernando SotoRetiredCommented:
If Rebate is a column in the table ProductsInPromotions then it should show up.

Is intellisense showing up on the display at all or is it just not in the list?
0
CamilliaAuthor Commented:
it is a column in the table. This is what I see in intelliense

intellisense.png
0
it_saigeDeveloperCommented:
Expand your selection.
PInfo = pInfoGroup.Select(p => p.ProductsInPromotion).FirstOrDefault()
// Or
PInfo = pInfoGroup.Select(p => p.ProductsInPromotion.Rebate).FirstOrDefault()

Open in new window


This is of course dependent upon Rebate being a column of the ProductsInPromotion object.

-saige-
0
Fernando SotoRetiredCommented:
OK, The R's are not being shown in the intelliense so I can not say if it is in it or not. If you do not see it, is it possible that this column was added to the database after you created the model? If this is the case you will need to update the model and re-compile the project. You can see if this is the case by opening up the model and find the table in the designer and see if the column Rebate shows up there.
0
CamilliaAuthor Commented:
Yes I added it later. Let me see
0
Fernando SotoRetiredCommented:
To update an EF model see this link.
Update Model Wizard (Entity Data Model Tools)
0
CamilliaAuthor Commented:
It's linq to sql. I have the column there. Those 2 lines didn't work either. I can use stored proc. I'll play around it a bit more
0
Fernando SotoRetiredCommented:
From your last post, " I have the column there", Does that mean that it shows in the .dbml designer? If that is the case make sure that it is spelled the same way in both places and remember capitalization maters so Rebate is not the same as rebate.

If that does not prove to be fruitful I would need to see the project to look around and see what is going on. If it is possible to zip up the project and post it to where I can download it I will have a look. I do understand that some organizations do not allow this, so only if you can.
0
CamilliaAuthor Commented:
Thanks Fernando . Let me check
0
CamilliaAuthor Commented:
The spelling is correct. I have the code here http://1drv.ms/1DH5iXm
0
Fernando SotoRetiredCommented:
Hi Camilla;

I downloaded your project and I un-commented the line I asked you to place in the query and in the select I added the property without issue as can be seen in the screen shot below. I did nothing else to your project. Please try again and even if it does not show up in intelliense give it a try anyway.Screen shot modifying query
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CamilliaAuthor Commented:
I'll  try and post back.
0
CamilliaAuthor Commented:
worked, thanks
0
Fernando SotoRetiredCommented:
Not a problem Camilla, glad to help.
Hope all is going well.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.