The Code Slinger

October 28, 2008

LINQKit: PredicateBuilder<T> Goodness

Filed under: Uncategorized — Pete @ 2:52 pm
Tags: , ,

Remember the good ol’ days when you wanted to create a query that had dependencies that were unknown at runtime? You’d create a nice StringBuilder instance, and start building your own SQL to handle the complex logic of ever possible permutation that you allowed via your UI.  Or you create the hairiest stored procedure known to man with umpteen variables and then tried not only to not screw it up internally, but to make every scenario as fast as possible.  Yeah, those days were fun weren’t they?

With LINQ, such tasks are still moderately tough, but now that I’ve found the very nice PredicateBuilder<T> class that comes in the free LINQKit, the ease with which one can create dynamic SQL calls is significantly easier. 

As an example, supposed you have a table which contains a description column.  And you want to implement a sort of “smart search” on that column such that if your user types in:

Include: Dog Cat
Exclude: Hamster

you code translates that into a query such as:

SELECT * FROM Pets
WHERE Description LIKE ‘%Dog%’
AND Description LIKE ‘%Cat%’
AND Description NOT LIKE ‘%Hamster%’

Behold, PredicateBuilder<T>.

            //First get a list of keywords that match the description entered.

            string[] parts = txtInclude.Text.Split(new[] {‘ ‘});

            string[] noparts = null;

            if(txtButNot.Text.Trim().Length > 0)

                noparts = txtExclude.Text.Trim().Split(new[] {‘ ‘});

 

            var pred = PredicateBuilder.True<Pet>();

            parts.ForEach(p => pred = pred.And(pl => pl.description.Contains(p)));

            if(noparts != null)

                noparts.ForEach(p => pred = pred.And(pl => !pl.description.Contains(p)));

 

            var pets = from s in db.Pets.Where(pred)

                        select s;

Notice that it doesn’t matter what order the user types the keywords in, nor does it matter how many there are.    Obviously this is a trivial example, and I don’t really have a table that stores pet names 😉  However, I do have some pretty complex requirements to create a way to backtrack and find potential buyers in our system based on previous items they’ve shown interest in.  Sounds like a nice use for this gem of a find.

Til next time….keep on slangin!

Advertisements

October 22, 2008

LINQDataSource and Optional Parameters

Filed under: Uncategorized — Pete @ 1:20 pm
Tags: , ,

The combination of LINQDataSource and GridView makes a nice quick way to provide basic search capabilities for your users.  Providing a user the ability to search on multiple criteria for a single entity is quick and painless.  However, I ran into an issue where one of the columns I was searching by allowed NULL in the table. 

My fields looked something like:

                <tr>

                    <td>Company:&nbsp; </td>

                    <td><asp:TextBox ID=”txtCompNameSearch” runat=”server”></asp:TextBox></td>

                </tr>

                <tr>

                    <td>Phone: &nbsp;</td>

                    <td><asp:TextBox ID=”txtPhoneSearch” runat=”server”></asp:TextBox></td>

                </tr>

My LINQDataSource:

 

    <asp:LinqDataSource ID=”ldsCompanies” runat=”server”

        ContextTypeName=”DataContext”

        TableName=”REG_Companies” EnableUpdate=”True”

        Where=”CompanyName.StartsWith(@CompanyName) &amp;&amp; Phone.StartsWith(@PhoneNumber)”>

        <WhereParameters>

            <asp:ControlParameter ConvertEmptyStringToNull=”false” ControlID=”txtCompNameSearch” Name=”CompanyName”

                PropertyName=”Text” Type=”String” />

            <asp:ControlParameter ConvertEmptyStringToNull=”false” ControlID=”txtPhoneSearch” Name=”PhoneNumber”

                PropertyName=”Text” Type=”String” />

        </WhereParameters>

    </asp:LinqDataSource>

If both CompanyName and Phone fields didn’t allow nulls, this would work just fine.  However, like my scenario, the Phone field (for whatever reason) allowed NULL values in the database, this would still work but if you left txtPhoneSearch empty, it would only return records in which the Phone field was NOT NULL.

So, the next logical thing to do would be to change the ConvertEmptyStringToNull value to true for that parameter.  Ah, but if you do that you get the following error when you try to load the page the code is on:

No applicable method ‘StartsWith’ exists in type ‘String’

So, this is what I came up with to fix it so you can search optionally on columns that allow nulls using the standard ControlParameter in the LINQDataSource.  Leave the ConvertEmptyStringToNull=”false” as you would with non-nullable columns.  However, modify your Where clause to the following:

        Where=”CompanyName.StartsWith(@CompanyName) &amp;&amp; (@PhoneNumber == String.Empty || (@PhoneNumber != String.Empty &amp;&amp; Phone.StartsWith(@PhoneNumber)))”>

You’re effectively just giving the query an option to exit early if the @PhoneNumber parameter is empty, but if it is not, then you’re adding the comparison to the dynamic SQL logic.

Til next time….keep on slangin!

September 30, 2008

Randomization & LINQ

Filed under: Uncategorized — Pete @ 3:21 pm
Tags: , ,

Ran into a problem today in which I had a List<T> of items, and I needed to return a number X of them back, but I wanted a random sampling of the original list.  There are plenty of traditional ways to do this, however I figured I’d learn something new and figure out how to do this using LINQ.

Here’s my solution, which I turned into an extension method for future use……

        public static List<T> GetRandom<T>(this List<T> objs, int count)

        {

            var ret = new List<T>();

 

            if(objs.Count() <= count)

                return objs.ToList();

 

            var used = new List<int>();

            var seq = (Enumerable.Range(1, count)

                        .Select(a =>

                            {

                                var num = (from b in Enumerable.Range(0, objs.Count()-1)

                                           orderby Guid.NewGuid()

                                           where !(from u in used

                                                  select u).Contains(b)

                                           select b).First();

                                used.Add(num);

                                return num;

                            })).ToList();

 

            foreach (int s in seq) { ret.Add(objs[s]); }

            return ret;

        }

 

Basically the thinking is that since I’m dealing with randomizing what’s in the list, I don’t really care about data specific to the type “T”.  I’m just randomizing the index count from the original list based on how many I want, then using those indexes I get back from the randomization part to pull the actual objects.

The only thing I don’t like about it is the fact that I have to create a separate variable ( “used” ) to keep track of which items in the original list that I’ve already used. Seems there should be a better way to do this within the LINQ query, but at this point I am drawing a blank on that one.

Also…did you notice I am doing randomization without even using the System.Random class? Yeeaaa for me! 😉

As always….Keep on Slangin!

November 20, 2007

LINQ to Objects: Nested Queries

Filed under: .NET,LINQ,VB.NET — Pete @ 6:21 pm
Tags: , ,

LINQ allows us to query our object tree(s) in a much easier and more readable manner than using traditional looping/iteration mechanisms of the past.

In this example, I have a Role object which contains a Dictionary(Of Type, List(Of IPermission)) which stores a series of object types as it’s key and a list of permission objects (which implement the IPermission interface). This keeps track of the relationship(s) between roles and permissions.

In the past, I would have written a function which takes a particular characteristic of one permission in particular and returns that permission type as the following:

 

253 Public Function InheritedStatusesByType(ByVal a_TypeEnum As WorkObjectTypeEnum) As IEnumerable(Of Status) 254 Dim hshReturn As New Dictionary(Of Integer, Status)

255 256 For Each rl As Role In CurrentRole.GetPermissionDL(Of Role)() 'ChildrenRolesDirect

257 For Each st As Status In rl.GetPermissionFlat(Of Status)() 'Statuses 258 If st.WorkObjectTypeEnum = a_TypeEnum Then

259 If Not hshReturn.ContainsKey(st.ID) Then 260 hshReturn.Add(st.ID, st)

261 End If 262 End If

263 Next 264 Next

265 Return New List(Of Status)(hshReturn.Values) 266 End Function

So this loops through the children roles (roles can be nested in this security system) that are direct children, then for each one it gets the list of all statuses which are either directly set or inherited from further sub-roles.

It inspects each status to be sure it’s of the type we want (via the enum), then it checks the return dictionary to make sure we don’t already have the status, add it if necessary, and once the loops exit successfully, the return values of the dictionary are cast into a return list of unique statuses the current Role object has access to.

This isn’t too bad, but it certainly isn’t all that intuitive to the regular programmer. One has to take a few minutes to “wrap ones head” around what is going on. More time spent on that is less time adding features or new functionality.

So, I’ve rewritten this type of code using LINQ. It goes something like…..

 

244 Public Function InheritedStatusesByType(ByVal a_TypeEnum As WorkObjectTypeEnum) As IEnumerable(Of Status) 245 'Try with LINQ

246 Dim ret = From rl In _ 247 m_CurrentRole.GetPermissionDL(Of Role)(), _

248 st In rl.GetPermissionFlat(Of Status)() _ 249 Where st.WorkObjectTypeEnum = a_TypeEnum _

250 Select st 251

252 Return ret 253 End Function

So far so good. This does basically the same thing using LINQ style query syntax. However the only issue with this is that it can potentially return duplicate statuses. This is because like in normal SQL, it is effectively doing a join on the detail records, and if more than one sub-role has access to the same status, it will be returned multiple times.

So, we need to figure out a way to do the equivalent of “DISTINCT” in SQL. Looking in the documentation one finds just that.

 

244 Public Function InheritedStatusesByType(ByVal a_TypeEnum As WorkObjectTypeEnum) As IEnumerable(Of Status) 245 'Try with LINQ

246 Dim ret = (From rl In _ 247 m_CurrentRole.GetPermissionDL(Of Role)(), _

248 st In rl.GetPermissionFlat(Of Status)() _ 249 Where st.WorkObjectTypeEnum = a_TypeEnum _

250 Select st).Distinct() 251

252 Return ret 253 End Function

The only problem is that when I run this with my example code, I still get duplicates. Hmmmm. Digging further into the Distinct syntax, I find that it’s default implementation uses the method GetHashCode() to determine equality. Obviously this won’t work, as our objects are distinct instances even if their data is the same. We need a way of overriding the Distinct logic without losing our readability.

This is where we find out about Extension Methods.

Here’s the code for the extension method we need to write to accomplish our own Distinct implementation:

 

9 <Extension()> _ 10 Public Function Distinct(Of T As PersistLoad)(ByVal source As IEnumerable(Of T)) As IEnumerable(Of T)

11 Return System.Linq.Enumerable.Distinct(source, New IDComparer(Of T)()) 12 End Function

Our source object is simply the same type that we want to write the extension off of (since that is the “type” we’re doing the Distinct operation on). However the second parameter is a custom class which implements the IEqualityComparer(Of T) interface, effectively defining how two objects (in this case inheriting from the base type PersistLoad) are defined as being “equal”.

 

2 Public Class IDComparer(Of T As PersistLoad) 3 Implements IEqualityComparer(Of T)

4 5 Public Function Equals1(ByVal x As T, ByVal y As T) As Boolean Implements System.Collections.Generic.IEqualityComparer(Of T).Equals

6 Return x.ID = y.ID 7 End Function

8 9 Public Function GetHashCode1(ByVal obj As T) As Integer Implements System.Collections.Generic.IEqualityComparer(Of T).GetHashCode

10 Return obj.ID.GetHashCode() 11 End Function

12 End Class

In this case, all PersistLoad objects have a property called “ID” which defines it’s key or unique value. You could use any attribute to define equality in reality though.

So, implementing our extension method of the Distinct(Of T as PersistLoad) method and our custom comparer class which it uses, we can retry our LINQ query in our Role object. NOTE that we could literally write any number of Distinct extension methods, which because of the nature of polymorphism, will get called based on the calling type at runtime. In our case, the LINQ query is returning an IEnumerable(Of T as PersistLoad), therefore when calling Distinct on it, our extension method written for that context will be called rather than the default.

 

244 Public Function InheritedStatusesByType(ByVal a_TypeEnum As WorkObjectTypeEnum) As IEnumerable(Of Status) 245 'Try with LINQ

246 Dim ret = (From rl In _ 247 m_CurrentRole.GetPermissionDL(Of Role)(), _

248 st In rl.GetPermissionFlat(Of Status)() _ 249 Where st.WorkObjectTypeEnum = a_TypeEnum _

250 Select st).Distinct() 251

252 Return ret 253 End Function

So our final code (after the extension and comparer implementation) looks exactly the same as before. Now when we run this, no matter how many times a status shows up in the object tree of roles, it will only be returned a single time to the variable ret, which houses a custom iterator ultimately representing an IEnumerable(Of Status) that actually ends up being a List(Of Status) because of the underlying data structures.

Technorati Tags: , ,

Extension Methods in Action

Filed under: .NET,LINQ,VB.NET — Pete @ 3:54 am
Tags: , ,

Extension methods are a new language feature in C# and VB.NET which in essence is one of the underpinnings of the new LINQ (Language INtegrated Query) syntax new to .NET.

At first look, extension methods seem to “add” functionality to existing types. But in reality they simply allow you the flexibility of making static method calls more intuitive within the framework of your code by allowing you to attach those methods to existing types (which you may or may not have the source-code to).

As an example, I’ve taken a common task that I find myself doing in my code quite a bit. Having a generic List(Of T) of business objects, I want to find the one object in that list that has a particular ID.

27 Public Shared Function GetByID(Of T As PersistLoad)(ByVal a_List As List(Of T), ByVal a_ID As Integer) As T

28 Dim current As T = Nothing

29 For Each obj As T In a_List

30 If obj.ID = a_ID Then

31 current = obj

32 Exit For

33 End If

34 Next

35

36 Return current

37 End Function

Using this generic implementation, I can pass in any list of objects, the ID I want to search for, and either return an object of type T or nothing/null. This works fine, however if I want to do the same for another data type, e.g. like a Dictionary, I have to write a new method that knows how to iterate over that type of object.

The calling code for this implementation would be something like:

112 m_CurrentBarcode = ExtensionsRegular.GetByID(m_Barcodes, a_BarcodeID)

Notice that I have to pass in as an input parameter the actual list of objects (in this case, barcodes)? Additionally, the method is simply called from a static/shared method repository class called ExtensionsRegular.

Now, let’s look at how an extension method can help us here.

7 <Extension()> _

8 Public Function FindByID(Of T As PersistLoad)(ByVal list As IEnumerable(Of T), ByVal a_ID As Integer) As T

9 Dim selected = _

10 From b In list _

11 Where b.ID = a_ID _

12 Select b

13

14 If selected.Count > 0 Then

15 Return selected.First()

16 Else

17 Return Nothing

18 End If

19 End Function

Notice with the extension method implementation, we can use the IEnumerable generic interface as the input. We’ll come back to why that’s important in a minute. Inside the implementation of the extension method, we are using a LINQ query that selects from the list (which must implement IEnumerable or it’s generic equivalent IEnumerable) where the ID equals the a_ID parameter we passed in. Notice 1) how similar this is to SQL queries and 2) how natural it looks to the reader in terms of what is actually being done in the code.

Now our calling code looks like:

114 m_CurrentBarcode = m_Barcodes.FindByID(a_BarcodeID)

Because it’s an extension method, the compiler actually allows the method to be called as if it were an instance member method. Notice that the only input parameter is the ID we’re searching for.

Additionally, since we used the IEnumerable(Of T) interface as our input declaration, we can now use this exact same extension method if we were trying to FindByID on basically ANY type (in the .NET framework or otherwise) which implements IEnumerable(Of T), since any IEnumerable type must implement the MoveNext() method.

Blog at WordPress.com.