SolvedPrivate

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

Posted on 2014-12-16
17
50 Views
Last Modified: 2016-02-15
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

0
Comment
Question by:Camillia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
17 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40502662
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
 
LVL 7

Author Comment

by:Camillia
ID: 40502705
let me try. Thanks, as always.
0
 
LVL 7

Author Comment

by:Camillia
ID: 40502718
No, I don't see "Rebate" in intellisense. I can move it to a stored proc but I really don't want to.
0
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40502786
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
 
LVL 7

Author Comment

by:Camillia
ID: 40502802
it is a column in the table. This is what I see in intelliense

intellisense.png
0
 
LVL 34

Expert Comment

by:it_saige
ID: 40502834
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40502847
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
 
LVL 7

Author Comment

by:Camillia
ID: 40502862
Yes I added it later. Let me see
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40502897
To update an EF model see this link.
Update Model Wizard (Entity Data Model Tools)
0
 
LVL 7

Author Comment

by:Camillia
ID: 40502918
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40502956
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
 
LVL 7

Author Comment

by:Camillia
ID: 40502974
Thanks Fernando . Let me check
0
 
LVL 7

Author Comment

by:Camillia
ID: 40502978
The spelling is correct. I have the code here http://1drv.ms/1DH5iXm
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 40503498
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
 
LVL 7

Author Comment

by:Camillia
ID: 40503603
I'll  try and post back.
0
 
LVL 7

Author Closing Comment

by:Camillia
ID: 40509935
worked, thanks
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 40509955
Not a problem Camilla, glad to help.
Hope all is going well.
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question