I came across the following statement about LINQ to SQL on a developer forum the other day:

“I think the general consensus is this: it’s good if you know the query beforehand and what type of object you’re going to get back on execution, but if the query is dynamic and/or large then it’s not well suited.”

I thought I would kick off this blog by challenging this statement with some simple examples:

The contributor was partly referring to his belief that it was not possible to do anything similar to dynamic SQL using LINQ to SQL and in fact posted a challenge to do in LINQ to SQL the equivalent of the following dynamic SQL:

string dynamicSQL = ” SELECT ” +  <someCols>  + ”  FROM ” + <someTable> + ” WHERE ” + <someCondition> + ” ORDERBY ” + <orderBy>;

So we will look at how this can be done using LINQ to SQL.

Before looking at this it’s useful to observe that when you dynamically create the <someCondition> for the WHERE clause you would need to distinguish between types when constructing it dynamically. For example:

WHERE Quantity = 3 AND City=’London’   (Assuming Quantity is a number of some type)

You need to know that Quantity is a number and City is a string in order to correctly construct the SQL WHERE clause. What I am getting at is that when constructing dynamic SQL you have to be somewhat aware of what types you are dealing with.

The first thing to remember is that when we construct a LINQ to SQL query, that query is in fact stored as an expression tree. LINQ to SQL uses this expression tree to generate SQL which is sent to the database.

So the starting point for doing dynamic LINQ to SQL is to look at the System.Linq.Expressions namespace.

The System.Linq.Expressions namespace contains classes, interfaces and enumerations that enable language-level code expressions to be represented as objects in the form of expression trees. It enables us to directly manipulate expression trees at runtime. Manipulating expression trees at runtime allows us to create LINQ to SQL queries dynamically.

However, creating LINQ to SQL expression trees directly is not very convenient and is a none trivial task. What would be nice is to have the ability to convert a string representation of a LINQ to SQL query that can be parsed into an expression tree.

As it happens, there is a Dynamic Query Library written in C# that does precisely this and it is available at:

C# Dynamic Query Library (included in the \LinqSamples\DynamicQuery directory)

(The samples also include the NorthwindMapping project which contains the mapping classes for the Northwind database. The examples here are against this mapping.)

If you look in this library (System.Linq.Dynamic) you will see that it makes extensive use of the System.Linq.Expressions and System.Reflection namespace to implement its functionality.

Using this library we are able to construct a potentially fully dynamic LINQ to SQL query.

I will use as my example the LINQ query:

 

            var query =

                from c in db.Customers

                where c.City == “London” && c.Orders.Count >= 10

                select new { Name = c.CompanyName, Phone = c.Phone };

 

expressed here in the C# LINQ syntax. Or:

 

            var query =

                 db.Customers

                    .Where(c => c.City == “London” && c.Orders.Count >= 10)

                    .OrderBy(c=>c.CompanyName)

                    .Select(c => new { Name = c.CompanyName, Phone = c.Phone });

 

expressed here using extension methods. These two queries equate to the same thing.

The System.Linq.Dynamic namespace basically provides a number of overloads to the extension methods for the IQueryable<T> and IQueryable interfaces that accept string representations of lambda expressions. For example the signatures for the Where extension methods are:

 

        public static IQueryable<T> Where<T>(

                this IQueryable<T> source,

                string predicate, params object[] values) {}

and

        public static IQueryable Where<T>(

                this IQueryable source,

                string selector, params object[] values) {}

 

Of course we must make these extensions available by including the using statement:

using System.Linq.Dynamic;

Using these overloaded extension methods we can re-write the above query using string representations like so:

string connString =

@”Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;Integrated

                                Security=True”;

Northwind db = new Northwind(connString);

Assembly asm = Assembly.GetAssembly(typeof(Northwind));

var q = db.GetTable(asm.GetType(“NorthwindMapping.Customer”))

.Where(“City == @0 and Orders.Count >= @1″, “London”, 10)

.OrderBy(“CompanyName, City”)

.Select(“New(CompanyName as Name, Phone)”);

 

I have used the db.GetTable(asm.GetType(“NorthwindMapping.Customer”))

statement to get the mapping object dynamically too, however the extension methods have taken care of the rest.

 

 

Everything is expressed as strings with the exception that the parameters for the Where clause need to be the correct type, or at least of types that are implicitly convertible to the correct type. However as I mentioned before, you would need some type awareness when constructing an SQL WHERE clause too.

I could put this into a general purpose method:

        public static IQueryable FullDynamic(

            System.Data.Linq.DataContext dc,           

            string tableName,

            string orderBy,

            string select,

            string wherePredicate,

            params object[] whereValues

            )

        {

            IQueryable q = dc.GetTable(

                    Assembly.GetAssembly(dc.GetType()).GetType(tableName))

                .Where(wherePredicate, whereValues)

                .OrderBy(orderBy)

                .Select(select);

            return q;

        }

 

And call it thus:

            IQueryable q = FullDynamic(

                    db,

                    “NorthwindMapping.Customer”,

                    “CompanyName, City”,

                    “New(CompanyName as Name, Phone)”,

                    “City == @0 and Orders.Count >= @1″,

                    “London”, 10);

 

            foreach (System.Linq.Dynamic.DynamicClass item in q)

            {

                Console.WriteLine(item);

            }

 

I would need to do a little more work on this to enable changing the direction of the order by, but I think that would be about all to answer the challenge fully.

 As you can see the resulting items from the dynamic query inherit from the System.Linq.Dynamic.DynamicClass abstract type.

            foreach (System.Linq.Dynamic.DynamicClass item in query2)

            {

 

                PropertyInfo[] props = item.GetType().GetProperties(BindingFlags.Instance |

                                BindingFlags.Public);

                StringBuilder sb = new StringBuilder();

                sb.Append(“{”);

                for (int i = 0; i < props.Length; i++)

                {

                    if (i > 0) sb.Append(“, “);

                    sb.Append(props[i].Name);

                    sb.Append(“=”);

                    sb.Append(props[i].GetValue(item, null));

                }

                sb.Append(“}”);

                Console.WriteLine(sb.ToString());

            }

 

This demonstrates that you can use reflection to inspect the resulting object graph at runtime – nothing new here, just straight forward use of reflection. In fact this code is taken from the ToString() overload of the System.Linq.Dynamic.DynamicClass abstract type.

I hope this is helpful. In my next article I will look at the other issue raised, of large or complex queries in LINQ to SQL.

 

In LINQ to SQL, queries can be composed. We can use this ability to create dynamic queries. For example:

            Northwind db = new Northwind(connString);

 

            var query =

                from c in db.Customers

                where c.City == “London”

                select c;

 

            Console.WriteLine(db.GetCommand(query).CommandText);

 

            if (true)  // Some condition

            {

                query =

                    from c in query

                    where c.ContactTitle == “Sales Representative”

                    select c;

            }

 

            Console.WriteLine(db.GetCommand(query).CommandText);

 

            /*

            SELECT

                [t0].[CustomerID], [t0].[CompanyName],

                [t0].[ContactName], [t0].[ContactTitle],

                [t0].[Address], [t0].[City], [t0].[Region],

                [t0].[PostalCode], [t0].[Country],

                [t0].[Phone], [t0].[Fax]

            FROM [dbo].[Customers] AS [t0]

            WHERE ([t0].[ContactTitle] = @p0) AND ([t0].[City] = @p1)

            */

 

 

            if (true)

            {

 

                query =

                    (

                    from c in query

                    from o in c.Orders

                    where o.ShipCity == “Colchester”

                    select c

                    ).Distinct();

            }

 

            Console.WriteLine(db.GetCommand(query).CommandText);

 

            /*

            SELECT DISTINCT

                [t0].[CustomerID], [t0].[CompanyName],

                [t0].[ContactName], [t0].[ContactTitle],

                [t0].[Address], [t0].[City], [t0].[Region],

                [t0].[PostalCode], [t0].[Country],

                [t0].[Phone], [t0].[Fax]

            FROM [dbo].[Customers] AS [t0], [dbo].[Orders] AS [t1]

            WHERE ([t1].[ShipCity] = @p0)

                AND ([t0].[ContactTitle] = @p1)

                AND ([t0].[City] = @p2)

                AND ([t1].[CustomerID] = [t0].[CustomerID])         

            */

            foreach (var cust in query)

            {

                Console.WriteLine(cust);

            }

Cool eh!

This can be done because query is an expression tree and the SQL is only generated from the expression tree and the resulting SQL executed when we start to enumerate the query.

 

 

The DataContext keeps track of the state of business entities in LINQ To SQL, but many times the original DataContext is not available for updates and hence state information and changes made to the business entity is lost.

This situation is common in an n-tier architecture where the business entity is serialized and sent across tiers. The entity is disconnected from the original data context and no change tracking is possible.

The Attach Method on the DataContext in LINQ To SQL allows you to re-attach an existing business entity that came from the database as well as information about the original state of the entity to help with optimistic concurrency.

The Attach method is provided on the ITable instance that supposedly allows re-attach an instance to be change tracked. Unfortunately this doesn’t work quite as expected.

The following code example illustrates:

    public partial class AttachDemo

    {

        public static void DetachDemo()

        {

 

            string userName = “mytestuser”;

            // Creates a user to use in demo.

            CreateDemoUser(userName);

 

            User user;

 

            // SCENARIO 1

 

            using (LinqToSQLDataClassesDataContext db =

                new LinqToSQLDataClassesDataContext())

            {

                user = db.Users.Single(u => u.Username == userName);

                // Simulate detachment

                user = Detach<User>(user);

            }

 

            // Make changes to the detached user.

            user.Password = “s1password”;

 

            try

            {

                Scenario_1_FailsToUpdateUser(user);

            }

            catch (Exception exp)

            {

                Console.WriteLine(

                    “FailsToUpdateUser method exception: {0}”,

                    exp.Message);

            }

 

 

            // SCENARIO 2

 

            using (LinqToSQLDataClassesDataContext db2 =

                new LinqToSQLDataClassesDataContext())

            {

                user = db2.Users.Single(u => u.Username == userName);

                // Simulate detachment

                user = Detach<User>(user);

            }

            // Make changes to the detached user.

            user.Password = “s2password”;

            // Update the detached user.

            Scenario_2_UpdateUser(user);

 

 

            // SCENARIO 3

 

            User originalUser;

 

            using (LinqToSQLDataClassesDataContext db3 =

                new LinqToSQLDataClassesDataContext())

            {

                user = db3.Users.Single(u => u.Username == “mytestuser”);

                // originalUser = Detach<User>(user);

                originalUser = db3.Users.GetOriginalEntityState(user);

                // Simulate detachment

                user = Detach<User>(user);

                // This will simulate a situation where we hold original

                //  state in some server side state cache or on the client tier

                //  itself and thus is detached also.

                originalUser = Detach<User>(originalUser);

            }

            // Make changes to the detached user.

            user.Password = “s3password”;

            // Update the detached user.

            Scenario_3_UpdateUser(user, originalUser);

 

 

        }

 

        // Serializes and deserializes the user. This has the effect

        //  of detaching the user from the data context as would happen

        //  in an n-tier application for example where the entities would

        //  be serialized over the wire.

        public static T Detach<T>(T entity)

        {

            DataContractSerializer dcs =

                new DataContractSerializer(typeof(T));

            Stream mstream = new MemoryStream();

            dcs.WriteObject(mstream, entity);

            mstream.Position = 0;

            return (T)dcs.ReadObject(mstream);

        }

 

        public static void Scenario_1_FailsToUpdateUser(User user)

        {

            using (LinqToSQLDataClassesDataContext dc =

                new LinqToSQLDataClassesDataContext())

            {

                dc.Log = Console.Out;

                User orgUser;

                orgUser = dc.Users.Single(u => u.Username == user.Username);

                dc.Users.Attach(user, orgUser);

                Console.WriteLine(dc.GetChangeSet());

                dc.SubmitChanges();

            }

        }

 

        public static void Scenario_2_UpdateUser(User user)

        {

            using (LinqToSQLDataClassesDataContext dc =

                new LinqToSQLDataClassesDataContext())

            {

                dc.Log = Console.Out;

                User orgUser;

                using (LinqToSQLDataClassesDataContext dca =

                    new LinqToSQLDataClassesDataContext())

                {

                    // dca.ObjectTrackingEnabled = false; // May save some overhead

                    orgUser = dca.Users.Single(u => u.Username == user.Username);

                }

 

                dc.Users.Attach(user, orgUser);

                Console.WriteLine(dc.GetChangeSet());

                dc.SubmitChanges();

            }

        }

 

        public static void Scenario_3_UpdateUser(User user, User originalUser)

        {

            using (LinqToSQLDataClassesDataContext dc =

                new LinqToSQLDataClassesDataContext())

            {

                dc.Log = Console.Out;

                dc.Users.Attach(user, originalUser);

                Console.WriteLine(dc.GetChangeSet());

                dc.SubmitChanges();

            }

        }

 

        public static User GetUser(string username)

        {

            using (LinqToSQLDataClassesDataContext dc =

                new LinqToSQLDataClassesDataContext())

            {

                return dc.Users.Single(u => u.Username == username);

            }

        }

    }

The public static T Detach<T>(T entity) method ensures that the entity passed in is detached from its data context by serializing it and then de-serializing it in memory using the DataContractSerializer class from the System.Runtime.Serialization namespace. This is what would generally happen in an n-tier architecture when entities are passed from tier to tier over the network. The important thing here is that they will no longer be attached to the originating data context and, of course, that originating data context can no longer track changes.

The Scenario_1_FailsToUpdateUser method

Here we create a new data context and query it for the user that we want to update, in order to get the current state of the user in the database. This is then passed into the second parameter (“original”) of the Attach method. The first parameter is the updated user.

What we want here, of course, is for the data context to compare the updated entity (first parameter) with the original (second parameter) and generate SQL accordingly.

However, The Scenario_1_FailsToUpdateUser method throws an exception.

The pertinent console output here is:

FailsToUpdateUser method exception: Cannot add an entity with a key that is already in use.

 

As you can see we get the exception “Cannot add an entity with a key that is already in use.” thrown by the dc.Users.Attach(user, orgUser) call.

 

I suspect that this is a bug, since one would expect the Attach(TEntity entity, TEntity original) method to work in such a scenario. The error seems to be caused by the very fact that I have executed the query:

orgUser = dc.Users.Single(u => u.Username == user.Username)

in order to get the current state to pass into the second parameter of the attach method – all within the same data context.

 

If we simply call the dc.Users.Attach(user) overload, the same error will occur.

 

Calling dc.Users.Attach(user, true) (asModified=true) produces the exception:

 

“An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.”

 

This we would expect.

 

Calling dc.Users.Attach(user, false) (asModified=false) again produces the exception:

 

“Cannot add an entity with a key that is already in use.”

 

Calling dc.Users.Attach(user, false) (asModified=false) without executing the preceding query produces no exception, but does not update the database!

 

The Scenario_2_UpdateUser method

The difference between the Scenario_1_FailsToUpdateUser method and the Scenario_2_UpdateUser method, which works, is simply that the query for the current state is executed on a separate data context. The console output for this method is:

 

{Inserts: 0, Deletes: 0, Updates: 1}

UPDATE [dbo].[Users]

SET [Password] = @p2

WHERE ([Username] = @p0) AND ([Password] = @p1) AND ([Active] = 1)

 

As we can see it has successfully worked out that we need an update and produces the minimal SQL to perform the task.

The Scenario_3_UpdateUser method

This method works.

This method differs from Scenario_2_UpdateUser method in that it requires an original user entity to be passed in, in addition to the updated entity.

The original user entity passed in is generated by the original data context:

                originalUser = db3.Users.GetOriginalEntityState(user);

                // Simulate detachment

                user = Detach<User>(user);

 

In this case there is no significance to using the GetOriginalEntityState method. I could equally have written:

originalUser = Detach<User>(user);

which would return a copy of the user. There is no significance to the fact that the originalUser was generated by the original context.

This scenario would be typical of an architecture where the original state is stored on the client and returned along with changes to the data tier.

So the reason that Scenario_3_UpdateUser method works is the same as the reason the Scenario_3_UpdateUser method works. It is by virtue of the fact that the original state is not obtained by querying on the same data context that you want to attach it to for updating.

 

This does seem odd. Any enlightenment on why this is would be welcome! Microsoft…..?