0

I have this query which runs a join on Books, TradingDesks and ProductInfos.

var queryjoin = from b in books.values join d in tradingdesks on b.tradingdeskid equals d.id **join p in productinfoss** on b.id equals p.riskbookid select new { p, book = b.name, tradingdeskname = d.name };

In the last join, I would like to do an right outer join with the Products. I am trying not to use dynamic sql query and trying to get this work with linq itself. I dont want to use where since this greatly affects performance. I tried linq to objects earlier but couldnt get right performance and now trying linq to sql but still cant get this through. LINQ Query Help . Any help is greatly appreciated.

Improve this question

2 Answers 2

Reset to default
1

Try this (check for typo of productinfos(s)):

EDIT: Sorry, I gave you the left outer join. Right outer join is not available in Linq, so you need to reformat the query slightly.

var queryjoin = from p in productinfos join b in books.values on p.riskbookid equals b.id into outer from o in outer.DefaultIfEmpty() join d in tradingdesks on o.tradingdeskid equals d.id select new { p, book = (o==null) ? "(no book name)" : o.name, tradingdeskname = d.name };
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Isnt this the left outer join equivalent ?
Yes, sorry you're right! It needs to be reordered into a left outer join (or you can use groups). Hang on...
1

please try the following

var queryjoin = from p in productinfoss join b in books.values on p.riskbookid equals b.id into temp from t in temp.DefaultIFEmpty() join d in tradingdesks on t.tradingdeskid equals d.id new { p, book = t==null?"no name":t.name, tradingdeskname = d.name };

it should work in this scenario. i have not tested it though

Improve this answer

6 Comments

no. this doesnt produce the expected result. In my intial query, I would get all the products
that's what you will get by taking right outer join on products. what else you want this query to return you
---Yours----- SELECT DISTINCT [t0].[Family] FROM [EVF_TradeService].[dbo].[Product] AS [t0] LEFT OUTER JOIN [EVF_HierarchyService].[dbo].[Book] AS [t1] ON [t0].[RiskBookId] = [t1].[Id] INNER JOIN [EVF_HierarchyService].[dbo].[TradingDesk] AS [t2] ON [t1].[TradingDeskId] = [t2].[Id] --------------------------------------------------------------------
---What I need---- SELECT distinct t2.Family FROM [EVF_HierarchyService].[dbo].[Book] AS [t0] INNER JOIN [EVF_HierarchyService].[dbo].[TradingDesk] AS [t1] ON [t0].[TradingDeskId] = [t1].[Id] RIGHT OUTER JOIN [EVF_TradeService].[dbo].[Product] AS [t2] ON [t0].[Id] = [t2].[RiskBookId]
These are the sqls genrated out of the linq query of yours. These both doesnt return the same result set.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.