SolvedPrivate

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

Posted on 2014-12-16
17
43 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 33

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

896 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

15 Experts available now in Live!

Get 1:1 Help Now