Dynamic SQL To Dynamic LINQ in VB.NET with MS SQL Server 2008
- by user337501
I dread asking this question, because with what I've read so far I understand im gonna have to cram a lotta new things into my head. In spite of all the similiar questions(and the wide variety of answers) I thought I'd ask as nothing I've read tailors to what I need specifically enough.
I need to represent the following query using LINQ:
DECLARE @PurchasedInventoryItemID Int = 2
DECLARE @PurchasedInventorySectionID Int = 0
DECLARE @PurchasedInventoryItem_PurchasingCategoryID Int = 3
DECLARE @PurchasedInventorySection_PurchasingCategoryID Int = 0
DECLARE @IsActive Bit = 1
DECLARE @PropertyID Int = 2
DECLARE @PropertyValue nvarchar(1000) = 'Granny Smith'
--Property1, Property2, Property3 ...
SELECT O.PurchasedInventoryObjectID,
O.PurchasedInventoryObjectName,
O.PurchasedInventoryConjunctionID,
O.Summary,
O.Count,
O.PropertyCount,
O.IsActive
FROM tblPurchasedInventoryObject As O
INNER JOIN tblPurchasedInventoryConjunction As C ON C.PurchasedInventoryConjunctionID = O.PurchasedInventoryConjunctionID
INNER JOIN tblPurchasedInventoryItem As I ON I.PurchasedInventoryItemID = C.PurchasedInventoryItemID
INNER JOIN tblPurchasedInventorySection As S ON S.PurchasedInventorySectionID = C.PurchasedInventorySectionID
INNER JOIN tblPurchasedInventoryPropertyMap as M ON M.PurchasedInventoryObjectID = O.PurchasedInventoryObjectID
INNER JOIN tblPropertyValue As V ON V.PropertyValueID = M.PropertyValueID
WHERE
I.PurchasedInventoryItemID = @PurchasedInventoryItemID AND
S.PurchasedInventorySectionID = @PurchasedInventorySectionID AND
I.PurchasingCategoryID = @PurchasedInventoryItem_PurchasingCategoryID AND
S.PurchasingCategoryID = @PurchasedInventorySection_PurchasingCategoryID AND
O.IsActive = @IsActive AND
V.PropertyID = @PropertyID AND
V.Value = @PropertyValue
Now, I know that a query in .NET doesnt look like this, this is my test in the SQL Design Studio. Naturally VB.NET variables will be used in place of the SQL local variables.
My problem is this: All of the conditions after "WHERE" are optional. In that a query might be made that uses one, some, all, or none of the conditions. V.PropertyID and V.Value can also appear any number of times.
In VB.NET I can make this query easy enough by simply concatenating strings, and using a loop to append the "V.PropertyID/V.Value" conditions.
I can also make a Stored Procedure in MS SQL, which is easy enough.
However, I want to accomplish this using LINQ.
If anyone could direct me, I would be most appreciative.
