how can i write sql query with join and where clause in linq or lambda

Question

I wrote below query in MsSQL now I want to write this query using C# linq

SELECT JD.*
FROM Job_Details JD
INNER JOIN MstCustomer Cust ON JD.Cust_ID = Cust.Cust_ID
WHERE Cust.SAP = 'Yes'

Answer 1

A fairly simple join would do.

from jd in Job_Details 
join cust in MstCustomer
on jd.Cust_ID equals cust.Cust_ID
where cust.SAP == 'Yes'
select jd

Answer 2

You asked for it using a lambda expression

You only want the Customers with Cust.SAP equal to "Yes", but you don't want the SAP in the end result. Hence it is more efficient to join only with the customers you actually want in your final result. Therefore do the Where before the Join:

IQueryable<JobDetail> jobDetails = ...
IQueryable<Customer> mstCustomers = ...

// Step 1: filter only the Yes customers:
var yesCustomers = mstCustomers.Where(customer => customer.SAP == "Yes");

// Step 2: Perform the join and select the properties you want:
var result = jobDetails.Join(yesCustomers, // Join jobDetails and yesCustomers
   jobDetail => jobDetail.Cust_Id,         // from every jobDetail take the Cust_Id
   customer = customer.Cust_Id,            // from every customer take the Cust_Id
   (jobDetail, customer) => new            // when they match use the matching items
   {                                       // to create a new object
       // select only the properties
       // from jobDetail and customer
       // you really plan to use
   })

TODO: if desired, make it one big LINQ statement. Note that this doesn't influence the performance very much, as these statements do not perform the query. They only change the Expression of the query. Only items that do not return IQueryable perform the query: ToList / FirstOrDefault / Any / Max / ...