Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
SolvedPrivate

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

Posted on 2014-12-16
17
Medium Priority
?
74 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 64

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 64

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 64

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 64

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 64

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 64

Accepted Solution

by:
Fernando Soto earned 2000 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 64

Expert Comment

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

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

618 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