SQL SubQuery using LINQ

SQL SubQuery using LINQ

• nadir

  Can "Any" or "Contain" operator take subquery as parameter If yes then please provide with an example.

   

  
  

• Alex2200

  This should do it:

  var query =
  from c in db.Customers
  where !db.Products.Any(p => p.ProductID == c.ProductID)
  select c;

  Of course, Customers don't usually have ProductIDs, but it works with the Northwind schema if you substitute OrderDetails.

  Anders

  
  

• Big5824

  The query you state in your post sounds like it should be able to be written using a simple join rather than an In clause. In that case, the TSQL would be:

  Select CustomerId from Customers INNER JOIN Products on Customers.ProductID=Products.ProductID.

  Given that, you could do the following LINQ query:

  var query = from c in Customers join
  p in Products on c.ProductID equals p.ProductID
  select c.CustomerID

  That being said, I suspect you may have been simplfying the query in question and really do need the IN functionality. In that case, you may want to check out the following thread: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=1220424&SiteID=1

  Jim Wooley
  http://devauthority.com/blogs/jwooley

  
  
  

• qrli

  There is no 'In' subquery in LINQ (so far).

  Use the 'Any' operator to accomplish the same thing.

  For example:
  
  all customers that are located in the same city as an employee

  from c in db.Customers
  where db.Employees.Any(e => e.City == c.City)
  select c;

  
  
  

• Andres Aguiar

  Hi,

  To use .Any within query do I need to bring database scheama as object model using sql metal . Currently I am operating on <Iqueryable> interface of memory Datasets tables by appying ToQueryable() and the operator .Any doesn't seem to work.

  This is how I am converting my dataset table to IQuerable().

  Dim Humanic = dsHumanic.Tables(0).ToQueryable()

  Please advice.

  Thanks,

  Rajesh

  
  

• moondaddy

  The left-hand-side of the .Any() operator is the subquery.

  query.Any(x => predicate)

  is equivalent to the SQL

  EXISTS(
  SELECT *
  FROM query
  WHERE predicate
  )

  
  
  

• Shan McArthur

  Any update on this
  

• dnzone

  Please convert following query using LINQ syntax:

  "Select CusotmerID from Customers where ProductID not in (Select ProductID from Products)"

  Thanks,

  Rajesh

  
  

• eeidfn

  While it is possible to write many "IN" queries as JOINs, there are sometimes side effects. This query attempts to get a list of categories for which the category contains "y" and the category has any product that is sold by a supplier in a city that starts with "M".

  var query = from category in MySandbox.Categories
  from product in MySandbox.Products
  from supplier in MySandbox.Suppliers
  where //join logic
  category.CategoryID == product.CategoryID
  && product.SupplierID == supplier.SupplierID
  where //filter logic
  supplier.City.StartsWith("M")
  && category.CategoryName.Contains("y")
  select category;

  
  Unfortunatly, it returns three results, instead of the expected one result. This is a side effect of the joining. These results could be DISTINCT'd, but that's a crutch. There could be intended dupes and unintended dupes. DISTINCT removes both.

  
  Here's two ways to query using subqueries (not JOIN) to get these results.

  var subquery = from product in MySandbox.Products
  where product.Supplier.City.StartsWith("M")
  select product.CategoryID;

  var query = from category in MySandbox.Categories
  where
  category.CategoryName.Contains("y")
  && subquery.Contains(category.CategoryID)
  select category;

  //-----------

  var query = from category in MySandbox.Categories
  where
  category.CategoryName.Contains("y")
  &&
  (
  from product in MySandbox.Products
  where product.Supplier.City.StartsWith("M")
  select product.CategoryID
  ).Contains(category.CategoryID)
  select category;
  

  
  
• SQL SubQuery using LINQ