Executing arbitrary queries in LINQ to SQL

A relative nuisance when working with LINQ to SQL is the need to a priori define a data type in order to represent the results of an arbitrary query executed through the data context. I don’t see defining a type as something specially complicated, and it could improve the readability of the code, but it is true that it could be avoided …

For instance (using an example taken from my book “C# 3.0 and LINQ”), if we would like to know how many soccer clubs from each city do belong to the Spanish league First Division, we should do something like this:

        class Tmp  // the type for the results

        {

            public string City { get; set; }

            public int ClubCount { get; set; }

        }

 

        static void ExecQuery1 ()

        {

            using (FutbolDataContext ctx = new FutbolDataContext())

            {

                var results = ctx.ExecuteQuery<Tmp>(

                   @"SELECT City, COUNT(*) AS ClubCount FROM Club

                                     GROUP BY City

                                     ORDER BY 2 DESC");

 

                Console.WriteLine("Clubs by city");

                foreach (Tmp c in results)

                    Console.WriteLine(c.Ciudad + " - " + c.CantClubes);

            }

        }

 

The ExecuteQuery() method has another, non-generic variant, but that one also requires the type for the result as its first parameter (of type System.Type).

Here we present a proposal that will allow you to avoid the definition of the type. With that goal in mind, we will extend the System.Data.Linq.DataContext class by means of an extension method that implements a non-generic version of ExecuteQuery(), that will produce (on demand) a sequence of arrays with the values of each column for each row of the query results:

namespace PlainConcepts.Linq

{

    public static class Extensions

    {

        public static IEnumerable<object[]> ExecuteQuery(

            this DataContext ctx, string query)

        {

            using (DbCommand cmd = ctx.Connection.CreateCommand())

            {

                cmd.CommandText = query;

                ctx.Connection.Open();

                using (DbDataReader rdr =

                    cmd.ExecuteReader(CommandBehavior.CloseConnection))

                {

                    while (rdr.Read())

                    {

                        object[] res = new object[rdr.FieldCount];

                        rdr.GetValues(res);

                        yield return res;

                    }

                }

            }

        }

    }

}

 

After importing the namespace PlainConcepts.Linq, we will be able to read the result set produced by the query in a fashion similar to that we use when reading from a DataReader:

 

        static void ExecQuery2()

        {

            using (FutbolDataContext ctx = new FutbolDataContext())

            {

                var results = ctx.ExecuteQuery(

                   @"SELECT City, COUNT(*) AS ClubCount FROM Club

                     GROUP BY City

                     ORDER BY 2 DESC");

 

                Console.WriteLine("Clubs by city ");

                foreach (var c in results)

                    Console.WriteLine(c[0] + " - " + c[1]);

            }

        }

 

Of course, it is the programmer’s responsibility to apply the necessary casts depending on the types of columns of the result set.

In a future post I will expose some ideas I’ve been nurturing lately in relation to this subject.

 

Published 30/1/2008 19:18 por Octavio Hernández
Archivado en:
Comparte este post: