Creating IN Queries With Linq To Sql
I am working on a ecommerce website these days using MVC; although the point I am going to discuss here is not related to MVC it is about LINQ; I am liking LINQ these days as it saves a lot of time and effort in creating large and complicated queries and that too specially when you are not a SQL expert or SQL Freak. I am one of those programmers who can write few simple queries in SQL and avoid to code in SQL. So I am Liking LINQ more.
Coming to the point Today while writing a function I need to extract few records from the product table where the product can be from the given range of product id's and I was using LINQ so here comes the concern how can I use IN operator feature of SQL which was so easy to use in SQL. And then I got the answer after going through few links only. Believe me it's not a great trick or something magical we need to do LINQ has handled it.
We can have two types of case and both the cases are handled in different ways but on same concept. Let me tell you what these two cases can be.
1. Values for comparison can be already with you in form or any array or list
2. Value for comparison can be evaluated by the query itself.
Here I am taking an example that I have a collection of productid and I need to fetch the names of those products from the product table. If I have to write a sql query for this then it would look like :
Select productname from products where productid in(1,5,7,9,11)
It's a simple SQL Qery which will return the result as the names of the products whose id will be either 1, 5, 7, 9 or 11.
Now if I need to do this using our LINQ we can consider the above two cases taking case 1 first where I have already the list of items stored in an array;
int[] productIDs = new int[] { 1, 2, 3, 4 };
var ProductsNames = from p in _db.Products
where productIDs.Contains(p.ProductID)
select p.Name;
Generated SQL for this will be
SELECT [t0].[Name
FROM [DatabaseName].[Product] AS [t0]
WHERE [t0].[ProductID] IN (@p0, @p1, @p2, @p3)
will discuss this code a bit later in this article. As before discussing this code I would like to share the code for other case also. And that goes like this
Considering that the productids will be selected from the other table i.e. tblcart which contains the products which are currently in the cart.
So first we need to select the productids form that cart table code goes like this:
var ProdIdsQuery = from Items in _db.tblCart
where Items.UserID == "meetuchoudhary"
select Items.ProductID;
now in ProdIdsQuery we have all the productids which user meetuchoudhary has added now selecting the names of these products we need to write another LINQ
var ProductsNames = from p in _db.Products
where ProdIdsQuery.Contains(p.ProductID)
select p.Name;
here I have just changed the variable name which was being tested. J not a trick. And the sql generated behind this will be
SELECT [t0].[Name]
FROM [DatabaseName].[Product] AS [t0]
WHERE EXISTS(
SELECT NULL AS [EMPTY]
FROM [Sales].[tblcart] AS [t1]
WHERE ([t1].[ProductID] = [t0].[ProductID]) AND ([t1].[ UserID] = @p0)
)
Here you can notice that we get where exists this is just a synonym for IN in SQL.
Well now this may seems bit weird or a wacky syntax to you but this is how we can achieve the IN of SQL in LINQ. This is the top down approach which LINQ follows this weirdness of these may be because Linq To Sql only constructs the query when the Enumerator is tripped. So that's why we need those ProdIdsQuery or array of productIDs
Hope it helps you. Thanks for spending your time on this page and reading it.
0 comments:
Post a Comment