Dynamic SQL To Dynamic LINQ in VB.NET with MS SQL Server 2008

Question

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.

Answer 1

I worked around this by string-concatenating my query and using the DataContext.ExecuteQuery function:

Dim res As IEnumerable(Of tblPurchasedInventoryObject) = pidc.ExecuteQuery(Of tblPurchasedInventoryObject)(query).ToList

Works like a charm, I can even change the data and post it back to the database. It still feels "backwards" though, and im open to better methods of doing this.

Answer 2

“ 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 ”

You can make the same thing by using in memory query, by building the query statement and then filter “only when it’s required” by using expression trees Here’s basics of expression trees http://blogs.msdn.com/b/charlie/archive/2008/01/31/expression-tree-basics.aspx

and this is good article on the subject: http://blogs.msdn.com/b/csharpfaq/archive/2009/09/14/generating-dynamic-methods-with-expression-trees-in-visual-studio-2010.aspx you can also refer to page 238 section 9.3 in Jon skeet’s book C# in depth http://www.manning.com/skeet/, and also Linq in Action book http://www.manning.com/marguerie/ all have a good explanation of Expression trees.