CODEWITHSUNDEEP
sqllinqlambda
samantha07
samantha07

Reputation: 507

How to convert this SQL to LINQ or Lambda expression?

here's my sql query below:
Can you guys help me to convert this to a much cleaner one??

SELECT [PurchaseRequestID], [ProjectID],[FullName]
FROM PurchaseRequest
  WHERE [PurchaseRequestID] IN
    (SELECT [PurchaseRequestID] FROM PurchaseRequestDetail )
    AND [PurchaseRequestID] NOT IN
      (SELECT [PurchaseRequestID] FROM [PurchaseOrder] )

Though i have already converted this successfuly, i think this is not readable and needs to be rewritten:

var query = from a in db.PurchaseRequests
where
   (from b in db.PurchaseRequestDetails
    select new
    {
        b.PurchaseRequestID
    }).Contains(new { a.PurchaseRequestID }) &&
!(from c in db.PurchaseOrders
    select new
    {
        c.PurchaseRequestID
    }).Contains(new { a.PurchaseRequestID })
select a;

thanks

Upvotes: 0

Views: 258

Answers (3)

Amy B
Amy B

Reputation: 110091

If you have navigation properties set up, you can write the query like this:

IQueryable<PurchaseRequest> query = 
  from purchaseRequest in myDataContext.PurchaseRequests
  where purchaseRequest.PurchaseRequestDetail.Any()
  where !purchaseRequest.PurchaseOrder.Any()
  select purchaseRequest;

Or this lambda/method style if you prefer...

IQueryable<PurchaseRequest> query2 = myDataContext.PurchaseRequests
  .Where(purchaseRequest => purchaseRequest.PurchaseRequestDetail.Any())
  .Where(purchaseRequest => !purchaseRequest.PurchaseOrder.Any());

Upvotes: 0

Scott M.
Scott M.

Reputation: 7347

you really don't need all those anonymous objects. Use the let keyword to introduce temporary variables instead of doing operations on the subqueries directly.

from a in db.PurchaseRequests
let b = from b in db.PurchaseRequestDetails select b.PurchaseRequestID
let c = from c in db.PurchaseOrders select c.PurchaseRequestID
where b.Contains(a.PurchaseRequestID) && !c.contains(a.PurchaseRequestID)
select a;

Upvotes: 2

Magnus
Magnus

Reputation: 46919

var query = from a in db.PurchaseRequests
where
   db.PurchaseRequestDetails.Any(x => x.PurchaseRequestID == a.PurchaseRequestID) &&
   !db.PurchaseOrders.Any(x => x.PurchaseRequestID == a.PurchaseRequestID)
select a;

Upvotes: 1

Related Questions