asked on

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

I got help from Fernando:

https://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

Fernando Soto

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

Camillia

ASKER

let me try. Thanks, as always.

Camillia

ASKER

No, I don't see "Rebate" in intellisense. I can move it to a stored proc but I really don't want to.

Fernando Soto

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?

Camillia

ASKER

it is a column in the table. This is what I see in intelliense

it_saige

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-

Fernando Soto

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.

Camillia

ASKER

Yes I added it later. Let me see

Fernando Soto

To update an EF model see this link.
Update Model Wizard (Entity Data Model Tools)

Camillia

ASKER

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

Fernando Soto

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.

Camillia

ASKER

Thanks Fernando . Let me check

Camillia

ASKER

The spelling is correct. I have the code here http://1drv.ms/1DH5iXm

ASKER CERTIFIED SOLUTION

Fernando Soto

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial