The Code Slinger

March 30, 2009

Funniest thing I’ve read in a while….

Filed under: Uncategorized — Pete @ 4:47 pm

This isn’t tech related…but I am crying here after reading this.  No idea if it’s true or not, but it gave me a great laugh on a day when I needed it.

First Date Gone Horribly Wrong

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!

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!

April 16, 2008

ConditionalWhere & LINQ

Filed under: Uncategorized — Pete @ 7:45 am

In writing SQL we’ve all no doubt come up against scenarios in which we wanted to return data conditionally based upon some arbitrary input.  There are entire case studies written about how to get the most efficiency out of the various and sundry methods employed to complete such a task. 

In a great many cases, the dynamic SQL we all hate writing so much in sprocs really is the fastest and most efficient means of getting the job done.  However, with the new 3.5 Framework extensions and LINQ, tasks like these become child’s play.  Here’s how:

First let’s create a generic extension method that we can use for any conditional statement.

        public static IEnumerable<T> ConditionalWhere<T, K>(this IEnumerable<T> query,

                                                            System.Nullable<K> id,

                                                            Func<T, bool> pred) where K : struct

        {

            if (id.HasValue)

                return query.Where(pred);

            return query;

        }

 

Now, when we want to conditionally include (or exclude) data from a LINQ call, we simply use:

DateTime? start = null;

if (txtStartRange.Text.Length > 0)

    start = DateTime.Parse(txtStartRange.Text);

DateTime? end = null;

if (txtEndRange.Text.Length > 0)

    end = DateTime.Parse(txtEndRange.Text);


var
nts = (from obj in db.Interests

          select new

            {

                obj.ID,

                obj.Display,

                obj.EnteredBy,

                obj.Note,

                obj.Timestamp,

                obj.Contact

            })

            .ConditionalWhere(start, nt => nt.Timestamp >= start.Value)

            .ConditionalWhere(end, nt => nt.Timestamp <= end.Value);

Blog at WordPress.com.