SolvedPrivate

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

Posted on 2014-12-16
17
41 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
  • 9
  • 7
17 Comments
 
LVL 62

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
 
LVL 62

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 32

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 62

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 62

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 62

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 62

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 62

Expert Comment

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

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Data Saving 5 30
Long URL in SMS 6 24
Execute Stored Procedure for a set of values 5 26
bulid json format 3 19
Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now