Click here to Skip to main content
15,947,476 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
How to join two joined tables with inner query in linque? same like below I tried in sql server

select refCustGrouped.Extension,refCustGrouped.CreatedOn,users.MobileNo,users.Name,cust.Status,users.IsDelete							
					from  [there_crm].[therecrm_admin].[User] users 
							join [there_crm].[therecrm_admin].[Customer] cust on users.uID = cust.uID
							join (
											select top 1 with TIES refCust.cuRefID,refCust.MobileNo,
											from [there_crm].[therecrm_admin].[RefCustomer] refCust
											ORDER BY
											ROW_NUMBER() OVER(PARTITION BY refCust.MobileNo ORDER BY refCust.cuRefID DESC)
							) refCustGrouped on refCustGrouped.MobileNo = users.MobileNo
					where users.IsDelete Is NULL or users.IsDelete = 0 and users.rID = 3 and cust.Status = 'INV' or cust.Status = 'VIS'

What I have tried:

var res = (from cust in CRMEntites.Customers
                       join user in CRMEntites.Users on cust.uID equals user.uID
                       join refCust in (
                       where cust.IsActive == true && cust.IsDelete == false && user.rID == 3
                       select new RefCust
Updated 11-Jan-19 1:43am
CHill60 9-Jan-19 12:22pm    
What is wrong with your code? Is an exception thrown, does it not return what you are expecting?
dawood abbas 10-Jan-19 2:35am    
I cant complete my linq query as per above sql query.

Note: i have no access to your data or your screen, so i'm not able to provide direct answer (solution).

The most recommended Linq method to implement SQL IN clause is Contains[^]. See:
What is the linq equivalent to the SQL IN operator - Stack Overflow[^]
Tip 8 – How to write ‘WHERE IN’ style queries using LINQ to Entities – Meta-Me[^]

I'm pretty sure that you can achieve the same result using Linq Where[^] + All[^] method (need some time to provide an example).
Share this answer
dawood abbas 11-Jan-19 7:54am    
I got they query but throwing an error null exception...whereas in MVC controller it's coming, in DAL cant...

var res = from users in CRMEntites.Users
join cust in CRMEntites.Customers on users.uID equals cust.uID
join refCust in (
from refC in CRMEntites.RefCustomers
group refC by refC.MobileNo into grp
select grp.OrderByDescending(g => g.cuRefID).FirstOrDefault()
) on users.MobileNo equals refCust.MobileNo
where users.IsDelete != true && users.rID == 3 && cust.Status == "INV" && cust.Status == "VIS"
select new DashboardGrid
Name = users.Name,
CreatedOn = refCust.CreatedOn,
MobileNo = refCust.MobileNo,
Extension = refCust.Extension
dawood abbas 11-Jan-19 7:56am    
below just grouping which is in Ctroller.cs working and getting data..fine
var res = _objRefCustomerBS.GetAllRefCustomer().GroupBy(x => x.MobileNo).Select(x => x.ToList().OrderByDescending(y => y.cuRefID).First()).ToList();
but this below code is not working in DAL class file.
var res = CRMEntites.RefCustomers.GroupBy(x => x.MobileNo).Select(x => x.ToList().OrderByDescending(y => y.cuRefID).First()).ToList();
var q =  from d in Dealer
         join dc in DealerConact on d.DealerID equals dc.DealerID
         select dc;
Share this answer
Maciej Los 11-Jan-19 5:39am    
This is not an answer to the question!
dawood abbas 11-Jan-19 7:58am    
@shawn patil I can join but cannot get data by grouping.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900