{"id":1803,"date":"2009-07-10T20:22:00","date_gmt":"2009-07-10T20:22:00","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/vbteam\/2009\/07\/10\/dynamic-searching-using-linq-dataset-joins\/"},"modified":"2024-07-05T12:48:44","modified_gmt":"2024-07-05T19:48:44","slug":"dynamic-searching-using-linq-dataset-joins","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/vbteam\/dynamic-searching-using-linq-dataset-joins\/","title":{"rendered":"Dynamic Searching using LINQ – Dataset + Joins"},"content":{"rendered":"

About two years ago I posted some code<\/a> that shows how to dynamically construct LINQ queries at runtime<\/a>.  On average there’s a couple questions per month about trying it in some edge case, and usually the support already exists within the API.  Recently though someone posted a question that has to do with Datasets and Joins that raised some interesting issues:<\/p>\n

I have a dataset with two datatables <\/em>(One Loaded From A CSV file) <\/em>and <\/em>(the other loaded from a query) <\/em>what I would like to be able to do is <\/em>set the criteria (i.e dtCSV.Field1=dtQuery.Field7 AND (dtCSV.Field5 != dtQuery.Field4 or dtCSV.Field5 != dtQuery.Field9)<\/em><\/p>\n

Returning dtQuery.Field1, dtCSV.Field5<\/em><\/p>\n

Previously the API supported conditions of the form “object.property=value”, but here we have something of the form “object1.property = object2.property”.  It’s still a completely valid expression, but it means the API will need to generate a different expression tree.<\/p>\n

Let’s walk through how you can add this support to the API:<\/p>\n

1. At the end of Module DynamicQuery add the following extension method:<\/p>\n

‘Extension method that can be called off any type that implements IEnumerable(Of T), <\/span><\/p>\n

    <\/span>‘which constructs a Condition with T as the element type and compares one property (or field) with another<\/span><\/span><\/p>\n

    <\/span><Extension(), EditorBrowsable(EditorBrowsableState.Always)> _<\/span><\/p>\n

    <\/span>Public<\/span> Function<\/span> CreateMutliColumnCondition(Of<\/span> T)(ByVal<\/span> dataSource As<\/span> IEnumerable(Of<\/span> T), _<\/span><\/p>\n

                                             <\/span>ByVal<\/span> propName As<\/span> String<\/span>, _<\/span><\/p>\n

                                  <\/span>           <\/span>ByVal<\/span> condType As<\/span> condition.Compare, _<\/span><\/p>\n

                                             <\/span>ByVal<\/span> propName2 As<\/span> String<\/span>) As<\/span> Condition(Of<\/span> T)<\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>Return<\/span> Condition.CreateMutliColumn(dataSource, propName, condType, propName2)<\/span><\/p>\n

    <\/span>End<\/span> Function<\/span><\/span><\/span><\/p>\n

2. Add this method to the Condition class:<\/p>\n

 <\/span>‘Constructs a Condition with T as the element type that compares one field\/property with another<\/span><\/span><\/p>\n

        <\/span>Public<\/span> Shared<\/span> Function<\/span> CreateMutliColumn(Of<\/span> T)(ByVal<\/span> dataSource As<\/span> IEnumerable(Of<\/span> T), _<\/span><\/p>\n

                                            <\/span>ByVal<\/span> propertyName As<\/span> String<\/span>, _<\/span><\/p>\n

            <\/span>                                <\/span>ByVal<\/span> condType As<\/span> Compare, _<\/span><\/p>\n

                                            <\/span>ByVal<\/span> propertyName2 As<\/span> String<\/span>) As<\/span> Condition(Of<\/span> T)<\/span><\/p>\n

 <\/span><\/p>\n

            <\/span>Return<\/span> New<\/span> Condition(Of<\/span> T)(propertyName, condType, propertyName2)<\/span><\/p>\n

        <\/span>End<\/span> Function<\/span><\/span><\/span><\/p>\n

3. Add the following code to the Condition(Of T) class:<\/p>\n

 <\/span><\/p>\n

       <\/span>‘Returns a MemberExpression for a property or field access.  <\/span>Also handles nesting scenarios<\/span><\/span><\/p>\n

        <\/span>Private<\/span> Function<\/span> GetPropertyExpression(ByVal<\/span> propName As<\/span> String<\/span>, ByVal<\/span> paramExpr As<\/span> ParameterExpression) As<\/span> MemberExpression<\/span><\/p>\n

            <\/span>‘Split the string to handle nested property access<\/span><\/span><\/p>\n

            <\/span>Dim<\/span> s = propName.Split(“.”c<\/span>)<\/span><\/p>\n

 <\/span><\/p>\n

            <\/span>‘Get the PropertyInfo instance for propName<\/span><\/span><\/p>\n

            <\/span>Dim<\/span> pInfo = GetType<\/span>(T).GetProperty(s(0))<\/span><\/p>\n

            <\/span>Dim<\/span> callExpr = Expression.MakeMemberAccess(paramExpr, pInfo)<\/span><\/p>\n

 <\/span><\/p>\n

            <\/span>‘For each member specified, construct the additional MemberAccessExpression<\/span><\/span><\/p>\n

            <\/span>‘For example, if the user says “myCustomer.Order.OrderID = 4” we need an<\/span><\/span><\/p>\n

            <\/span>‘additional MemberAccessExpression for “Order.OrderID = 4”<\/span><\/span><\/p>\n

           <\/span> <\/span>For<\/span> i = 1 To<\/span> UBound(s)<\/span><\/p>\n

                <\/span>pInfo = pInfo.PropertyType.GetProperty(s(i))<\/span><\/p>\n

                <\/span>callExpr = Expression.MakeMemberAccess(callExpr, pInfo)<\/span><\/p>\n

            <\/span>Next<\/span><\/span><\/p>\n

            <\/span>Return<\/span> callExpr<\/span><\/p>\n

        <\/span>End<\/span> Function<\/span><\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>Friend<\/span> Sub<\/span> New<\/span>(ByVal<\/span> propName As<\/span> String<\/span>, ByVal<\/span> condType As<\/span> Compare, ByVal<\/span> propName2 As<\/span> String<\/span>)<\/span><\/p>\n

            <\/span>Dim<\/span> paramExpr = GetParamInstance(GetType<\/span>(T))<\/span><\/p>\n

 <\/span><\/p>\n

            <\/span>Dim<\/span> b As<\/span> Expression = CombineExpression(GetPropertyExpression(propName, paramExpr), _<\/span><\/p>\n

                         <\/span>                           <\/span>condType, _<\/span><\/p>\n

                                                    <\/span>GetPropertyExpression(propName2, paramExpr))<\/span><\/p>\n

 <\/span><\/p>\n

            <\/span>LambdaExpr = Expression.Lambda(Of<\/span> Func(Of<\/span> T, Boolean<\/span>))(b, New<\/span> ParameterExpression() {paramExpr})<\/span><\/p>\n

 <\/span><\/p>\n

         <\/span>   <\/span>‘Compile the lambda expression into a delegate<\/span><\/span><\/p>\n

            <\/span>del = DirectCast<\/span>(LambdaExpr.Compile(), Func(Of<\/span> T, Boolean<\/span>))<\/span><\/p>\n

        <\/span>End<\/span> Sub<\/span><\/span><\/span><\/p>\n

4. (optionally) the old constructor in this method should be refactored to use the helper method we introduced in #3.<\/p>\n

        Friend<\/span> Sub<\/span> New<\/span>(ByVal<\/span> propName As<\/span> String<\/span>, ByVal<\/span> condType As<\/span> Compare, ByVal<\/span> value As<\/span> Object<\/span>, ByVal<\/span> valueType As<\/span> Type)<\/span><\/p>\n

   <\/span>         <\/span>Dim<\/span> paramExpr = GetParamInstance(GetType<\/span>(T))<\/span><\/p>\n

 <\/span><\/p>\n

            <\/span>Dim<\/span> callExpr = GetPropertyExpression(propName, paramExpr)<\/span><\/p>\n

 <\/span><\/p>\n

            <\/span>‘ConstantExpression representing the value on the left side of the operator<\/span><\/span><\/p>\n

            <\/span>Dim<\/span> valueExpr = Expression.Constant(value, valueType)<\/span><\/p>\n

 <\/span><\/p>\n

            <\/span>Dim<\/span> b As<\/span> Expression = CombineExpression(callExpr, condType, valueExpr)<\/span><\/p>\n

            <\/span>LambdaExpr = Expression.Lambda(Of<\/span> Func(Of<\/span> T, Boolean<\/span>))(b, New<\/span> ParameterExpression() {paramExpr})<\/span><\/p>\n

 <\/span><\/p>\n

           <\/span> <\/span>‘Compile the lambda expression into a delegate<\/span><\/span><\/p>\n

            <\/span>del = DirectCast<\/span>(LambdaExpr.Compile(), Func(Of<\/span> T, Boolean<\/span>))<\/span><\/p>\n

        <\/span>End<\/span> Sub<\/span><\/span><\/span><\/p>\n

 <\/p>\n

Now that the API supports it, we can do joins against Datasets:<\/p>\n

        ‘The code in this method is equivalent to the following query, but constructed dynamically<\/span><\/p>\n

        <\/span>‘Dim filteredQuery = From csvRow In dtCSV _<\/span><\/span><\/p>\n

        <\/span>‘                    <\/span>Join orderRow In dtOrders On csvRow.CustomerID Equals orderRow.CustomerID<\/span><\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>Dim<\/span> dtCSV As<\/span> New<\/span> myDataset.dtCSVDataTable<\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>dtCSV.Rows.Add(1, “Redmond”<\/span>)<\/span><\/p>\n

        <\/span>dtCSV.Rows.Add(2, “Toronto”<\/span>)<\/span><\/p>\n

        <\/span>dtCSV.Rows.Add(3, “Seattle”<\/span>)<\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>Dim<\/span> dtOrders As<\/span> New<\/span> myDataset.dtOrdersDataTable<\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>dtOrders.Rows.Add(10001, 1)<\/span><\/p>\n

        <\/span>dtOrders.Rows.Add(10002, 3)<\/span><\/p>\n

        <\/span>dtOrders.Rows.Add(10003, 4)<\/span><\/p>\n

        <\/span>dtOrders.Rows.Add(10004, 2)<\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>Dim<\/span> query = From<\/span> csvRow In<\/span> dtCSV _<\/span><\/p>\n

                    <\/span>From<\/span> orderRow In<\/span> dtOrders<\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>Dim<\/span> c = query.CreateMutliColumnCondition(“csvRow.CustomerID”<\/span>, Compare.Equal, “orderRow.CustomerID”<\/span>)<\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>Dim<\/span> filteredQuery = query.Where(c)<\/span><\/p>\n

 <\/span><\/p>\n

        <\/span>DataGridView1.DataSource = (From<\/span> row In<\/span> filteredQuery _<\/span><\/p>\n

                                    <\/span>Select<\/span> row.orderRow.OrderID, row.csvRow.CustomerID, row.csvRow.City).ToList()<\/span><\/span><\/p>\n

 <\/p>\n

The key line here is this one:<\/p>\n

        <\/span>Dim<\/span> c = query.CreateMutliColumnCondition(“csvRow.CustomerID”<\/span>, Compare.Equal, “orderRow.CustomerID”<\/span>)<\/span><\/p>\n

Instead of comparing a column to a value we can now compare one column to another (either in the same table or in a different table).<\/p>\n

<geeky overload resolution details><\/em><\/p>\n

Why did I name the method CreateMultiColumnCondition instead of just adding another overload called CreateCondition?  To avoid introducing a breaking change!  If you had code that called CreateCondition with the last parameter as a String, the compiler would infer the type parameter at that position to be String.  But now with a new overload that also has a parameter of type String in that position, the compiler would now choose the new overload because that method is “less generic” (i.e. a closer match).<\/em><\/p>\n

I don’t like the idea of having to think through advanced overload resolution details to figure out which method will actually get called, so it seemed easier to just rename the method \ud83d\ude42<\/em><\/p>\n

<\/geeky><\/em><\/p>\n

A better (and likely more performant) implementation would actually introduce an overload of Join similar to this one for Where:<\/p>\n

    <\/span>‘Filters an IEnumerable(Of T) according to the specified condition<\/span><\/span><\/p>\n

    <\/span><Extension()> _<\/span><\/p>\n

    <\/span>Public<\/span> Function<\/span> Where(Of<\/span> T)(ByVal<\/span> source As<\/span> IEnumerable(Of<\/span> T), _<\/span><\/p>\n

                                <\/span>ByVal<\/span> condition As<\/span> Condition(Of<\/span> T)) As<\/span> IEnumerable(Of<\/span> T)<\/span><\/p>\n

        <\/span>Return<\/span> source.Where(condition.del)<\/span><\/p>\n

    <\/span>End<\/span> Function<\/span><\/span><\/p>\n

 <\/font><\/span><\/p>\n

This would involve a bit more work in the API though as the Join method takes 3 delegates rather than 1.  (so Condition(Of T) would have to generate 3 smaller trees rather than one big one).  It’s already almost 6 on a Friday though and I’m lazy, so this is left as an exercise to the reader \ud83d\ude42<\/p>\n

 <\/p>\n

Full VS2008 .sln is attached.<\/p>\n

Jonathan<\/p>\n

 <\/p>\n

 <\/p>\n

DynamicQueries.zip<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

About two years ago I posted some code that shows how to dynamically construct LINQ queries at runtime.  On average there’s a couple questions per month about trying it in some edge case, and usually the support already exists within the API.  Recently though someone posted a question that has to do with Datasets and […]<\/p>\n","protected":false},"author":260,"featured_media":8818,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[21,195],"tags":[77,83,94,117,166],"class_list":["post-1803","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-im-a-vb","category-visual-basic","tag-iqueryable","tag-jonathan-aneja","tag-linqvb9","tag-orcas","tag-vb2008"],"acf":[],"blog_post_summary":"

About two years ago I posted some code that shows how to dynamically construct LINQ queries at runtime.  On average there’s a couple questions per month about trying it in some edge case, and usually the support already exists within the API.  Recently though someone posted a question that has to do with Datasets and […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/1803","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/users\/260"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/comments?post=1803"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/posts\/1803\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media\/8818"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/media?parent=1803"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/categories?post=1803"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/vbteam\/wp-json\/wp\/v2\/tags?post=1803"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}