Camillia
asked on
Left join in LINQ
I really don't want to move this to a stored proc. I want to do a left join in LINQ. This works but how can I add a left join to it?
This is the SQL statement. The left join is at the end
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
orderby data.CreateDate descending
select new
{....
This is the SQL statement. The left join is at the end
select * from Consumer c
inner join StateProvince s on c.StateId = s.Id
inner join Country co on c.CountryId = co.Id
inner join Promotions p on c.PromotionId = p.Id
inner join SubmissionStatus st on c.SubmissionStatusId = st.Id
inner join Products pc on c.ProductId = pc.Id
left join ProductsInPromotion pp on pp.ProductId = c.ProductId and pp.PromotionId = c.PromotionId
ASKER
sample is not gonna help me. I've seen samples. I need my query working.
Hi Camilla;
See if this is what you are looking for.
See if this is what you are looking for.
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.ProductsInPromotion on data.ProductId equals pp.ProductId into pInfoGroup
orderby data.CreateDate descending
select new
{
//....
// PInfo will be a collection and will be empty collection if no records are in it
PInfo = pInfoGroup.DefaultIfEmpty()
};
ASKER
I have this in "select new" section. How can I get this working? I want a column called "rebate" from it. If it's too much hassle, I just move this to a stored proc. How do I use that "PInfo" here to get the individual fields?
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
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
}).Where(x => x.Id == id);
Not understanding your question. Are you saying that from the ProductsInPromotion you want a to return a field called rebate?
ASKER
I'm saying you have this
But I have
How can I keep my code and use what you have? how does your line of code comes into play?
PInfo = pInfoGroup.DefaultIfEmpty()
But I have
select new
{
data.Id,
data.FirstName,
data.LastName,
data.Address,
data.City,
data.PostalCode,
state = s.Name,
stateId = s.Id,
...
How can I keep my code and use what you have? how does your line of code comes into play?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks, let me try
Not a problem, glad to help.
Open in new window
~Tim