Dynamic Queries using LINQ

LINQ is great; I’m having all kinds of use to it lately. The other day I needed to do something different, I needed to write a LINQ query dynamically, constructing the LINQ query as a String and then executing it (which is pretty common thing when you’re using SQL).

I was already convinced that I would need to spend a couple of weeks writing such functionality. Turns out that Microsoft has already written a LINQ Dynamic Query Library. It doesn’t come with the framework but you can download it and include in your project. Take a look at ScottGu’s Blog to see his post about it.

Here is an example:

Suppose I have a LocalDBDataSet with a mapping to the Table Person (Name, Sex, Phone).

If I wanted to get all the people in the table and perform a LINQ query to filter only the Males (M) I could do it like this:

PersonTableAdapter ta = new PersonTableAdapter();
DataTable dt = ta.GetData();
IEnumerable<LocalDBDataSet.PersonRow> drCollection = 
    ((IEnumerable<LocalDBDataSet.PersonRow>)dt).AsQueryable().Where("SEX == @0", "M");

I know this isn’t the most exciting example ever and I would need dynamic LINQ to do it but I just wanted to show you what you could accomplish. You get the idea, right?

With this kind of resource you could accomplish a lot of cool stuff. In the next post I’ll show how you can write a Cache class that can be queried with LINQ to avoid unnecessary trips to the database.

You can download the LINQ Dynamic Query Library here or here

First impressions on ASP.NET Dynamic Data Preview

Today I saw a screen cast of ASP.NET Dynamic Data for the first time. As a Rails enthusiast I must say that I was very excited.

One of the ideas behind this technology is moving application logic into the models and removing them from the pages. Personally I think that idea is more than correct since a validation rule on a certain field will be repeated in every page where the field shows up.

Another great feature is the ability to quickly get something running. That is one thing that really facilitates talking to clients, after all there is no better way to talk a client than showing something running and from there you can define further requirements and move on with the development process.

I’m very excited to see the direction in which Microsoft is going with new ideas like the Dynamic Data Preview and the MVC framework. It shows that they a really connected to what is going on in the development world and keep taking the new ideas from other technologies and improving the .Net framework. So far the future of 3.5 looks bright.

To get to know the ASP.NET Dynamic Data Preview better look at the following screencast by David Ebbo: ASP.NET Dynamic Data Preview

Using Scope_Identity with TableAdapters

I find TableAdapters to be a very helpful resource so I use them a lot. However, every now and then I stumble upon some scenario where the TableAdapter needs some creativity to work. Imagine the following:

Scenario:

  1. Need to insert a Person and an Address in the Database;
  2. You populate all the data in just one page;
  3. The table PERSON has an primary key named ID which is an Identity, so SQLServer will generate the ID upon the record insertion;
  4. You need to Insert a record in the ADDRESS table that will need the ID of the person just inserted;
  5. You’re using TableAdapters;

For this you’ll need the SCOPE_IDENTITY function provided by SQLServer in the TableAdapter code.

The TableAdapters code is frequently regenerated so you need to protect this code from being overwritten on regeneration. The best solution for this is extending your TableAdapter and overriding the method you need to protect.

First step

Add a new insert query in the PERSONTableAdapter named InsertQueryReturnID with a code like this:

INSERT INTO [PERSON] ([NAME], [PHONE], [EMAIL]) VALUES (@p1, @p2, @p3);
SELECT CAST(SCOPE_IDENTITY() AS INT);

The key here is the SCOPE_IDENTITY() function which will return the last inserted id in your connection so you’re safe about inserts made by other users.

Second step

Add a new class named PERSONTableAdapterExtended to your project. This class will inhererit from PERSONTableAdapter. Next, copy the code for the InsertQueryReturnID method from the Designer file from your DataSet and paste the method to your extended class. Substitute the method’s virtual modifier by an override modifier (because you want to override the method from the base class).

Another important change is in the way you execute and return the id. Normally the insert would be executed with a command.ExecuteNonQuery() command which would return the numbers of rows affected. Our SQL will return the ID of the record inserted so will replace that code by (int)command.ExecuteScalar(). The final method should look like this:

public class PERSONTableAdapterExtended : PERSONTableAdapter
    {
        [global::System.Diagnostics.DebuggerNonUserCodeAttribute()]
        [global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
        [global::System.ComponentModel.DataObjectMethodAttribute(global::System.ComponentModel.DataObjectMethodType.Insert, false)]
        public override int InsertQueryReturnID(string p1, string p2, string p3)
        {
            global::System.Data.SqlServerCe.SqlCeCommand command = this.CommandCollection[1];
            if ((p1 == null))
            {
                command.Parameters[0].Value = global::System.DBNull.Value;
            }
            else
            {
                command.Parameters[0].Value = ((string)(p1));
            }
            if ((p2 == null))
            {
                command.Parameters[1].Value = global::System.DBNull.Value;
            }
            else
            {
                command.Parameters[1].Value = ((string)(p2));
            }
            if ((p3 == null))
            {
                command.Parameters[2].Value = global::System.DBNull.Value;
            }
            else
            {
                command.Parameters[2].Value = ((string)(p3));
            }
            global::System.Data.ConnectionState previousConnectionState = command.Connection.State;
            if (((command.Connection.State & global::System.Data.ConnectionState.Open)
                        != global::System.Data.ConnectionState.Open))
            {
                command.Connection.Open();
            }
            int returnValue;
            try
            {
                returnValue = (int)command.ExecuteScalar();
            }
            finally
            {
                if ((previousConnectionState == global::System.Data.ConnectionState.Closed))
                {
                    command.Connection.Close();
                }
            }
            return returnValue;
        }
    }

Step Three

Now you’ll use only the PERSONTableAdapterExtended class instead of the PERSONTableAdapter generated class. This will protect your code from changes when the DataSet is regenerated

Passed 70-536 today

Today I passed the 70-536 exam (Microsoft .NET Framework 2.0 – Application Development Foundation). Some parts of the exam are really interesting because you get to use them every day, like regular expressions, IO, threads, reflection etc. Others are a little more boring to study like Application Domains. Overall the contents of the exam are very helpful for developers who write more serious coding.

For those who are thinking about taking the exam, here is the recipe I used:

  1. Studied about 2 hours a day for 2 months
  2. Used the MCTS Self-Paced Training Kit (Exam 70-536): Microsoft .NET Framework 2.0 Application Development Foundation, which is quite good
  3. Worked with .Net for about 2 years
  4. It is very important to write code. Don’t think you can just read a book and go take the exam. Explore different scenarios, try new ideas. Only then you’ll be ready for the exam.

Now my next goal is to pass 70-528 and get the MCTS certification. This one should be easier to pass since I work with web apps every day. In about two months I hope I will have nailed it.

Autocomplete has a problem with numbers

When using Ajax ControlTookit Autocomplete Extender I noticed that when the value I wanted to dislplay was a number starting with 0 (zero), like 0012, this initiating zero is removed and the result would show as 12. I didn’t want this beahavior so after googling for a few minutes I found a post that had a solution.

The each string of the list that is returned to the page should have escaped double quotes before and after the string. In the following example I take a DataTable and loop through it, creating a new List in which I add the element 0 (zero) of each row sorrounding it with the escaped double quotes.

List<string> list = new List<string>(10); 

 for (int i = 0; i < dt.Rows.Count; i++)
 {
     list.Add("\""+ dt.Rows[i][0].ToString() + "\"");
 }

 string[] arrayString = list.ToArray();

I can only assume that when the result is rendered on the page ajax library try to convert the results to numbers. Surrounding each number with escaped double quotes forces them to be treated as strings.

Thanks to my friend Allison Bertoloto who brought this problem to my attention.