SQL Server Xquery with LINQ To SQL

UPDATE – Beefed up the regular expression for better handling of nested parentheses.

LINQ To SQL doesn’t provide a way to include Xquery expressions in queries.  The simplest solution is to create SQL user-defined functions that perform the Xquery for you and return scalar results.

This works pretty well for small databases, but you lose the benefit of SQL Server’s XML indexes.  To get around that, we can modify the SQL command text to expand the function calls into raw Xquery expressions.

There are a couple of ways to do this, depending on the complexity / flexibility required for your query.  For simple queries you can use the DataContext.GetCommand method, modify the command, and get the results through DataContext.Translate or DataContext.ExecuteQuery.  For complex queries (particularly ones that need to shape the results) you’ll need to jump through a couple of Reflection hoops.

Since we don’t actually need to call the SQL user-defined functions, we can just create empty placeholders.  Here’s a sample table and functions:

-- =============================================
-- Author:      Chris Cavanagh
-- Create date: 3/15/2011
-- Description: Demo table
-- =============================================
CREATE TABLE [dbo].[MyXmlFieldTable](
    [UID] [uniqueidentifier] NOT NULL,
    [TimeCreated] [datetime] NOT NULL,
    [XmlValues] [xml] NULL,
 CONSTRAINT [PK_MyXmlFieldTable] PRIMARY KEY
(
    [UID] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[MyXmlFieldTable] ADD  CONSTRAINT [DF_MyXmlFieldTable_TimeCreated]  DEFAULT (getdate()) FOR [TimeCreated]
GO

-- =============================================
-- Author:      Chris Cavanagh
-- Create date: 3/15/2011
-- Description: Get XQuery value (string)
-- =============================================
CREATE FUNCTION XQueryString
(
    @xml xml,
    @xpath varchar(200)
)
RETURNS nvarchar(4000)
AS
BEGIN
    DECLARE @Result nvarchar(4000)
    RETURN @Result
END
GO

-- =============================================
-- Author:      Chris Cavanagh
-- Create date: 3/15/2011
-- Description: Get XQuery value (datetime)
-- =============================================
CREATE FUNCTION XQueryDateTime
(
    @xml xml,
    @xpath varchar(200)
)
RETURNS datetime
AS
BEGIN
    DECLARE @Result datetime
    RETURN @Result
END
GO

-- =============================================
-- Author:      Chris Cavanagh
-- Create date: 3/15/2011
-- Description: Get XQuery value (float)
-- =============================================
CREATE FUNCTION XQueryFloat
(
    @xml xml,
    @xpath varchar(200)
)
RETURNS float
AS
BEGIN
    DECLARE @Result float
    RETURN @Result
END
GO

-- =============================================
-- Author:      Chris Cavanagh
-- Create date: 3/15/2011
-- Description: Get XQuery value (bit)
-- =============================================
CREATE FUNCTION XQueryBit
(
    @xml xml,
    @xpath varchar(200)
)
RETURNS bit
AS
BEGIN
    DECLARE @Result bit
    RETURN @Result
END
GO

-- =============================================
-- Author:      Chris Cavanagh
-- Create date: 3/15/2011
-- Description: Get XQuery value (compare)
-- =============================================
CREATE FUNCTION XQueryCompare
(
    @xml xml,
    @xpath varchar(200),
    @oper varchar(10),
    @value nvarchar(4000)
)
RETURNS bit
AS
BEGIN
    DECLARE @Result bit
    RETURN @Result
END
GO

Once you add these functions to your DBML file (through the designer, or manually if you prefer) you’ll be able to write LINQ queries that call them.  Here’s a simple example:

var results = from r in MyXmlFieldTable
    let date = context.XQueryDateTime( r.XmlValues, "MyContainer/MyDateTime" )
    where date < DateTime.Now.AddDays( -5 )
    select new
    {
        Date = date,
        FirstName = context.XQueryString( r.XmlValues, "MyContainer/FirstName" ),
        LastName = context.XQueryString( r.XmlValues, "MyContainer/LastName" )
    };

With this, we can use some Regex magic to expand the function names into XQuery expressions:

public class XQueryHelper
{
    private static Regex regex = new Regex( @"\[dbo\]\.\[XQuery(?<type>.*?)\]\(\s?(?<column>\([^\(\)]*(((?'Open'\()[^\(\)]*)+((?'Close-Open'\))[^\(\)]*)+)*(?(Open)(?!))\)|([\s\S]*?)),\s?(?<xpath>.*?)(,\s?(?<oper>.*?),\s?(?<value>.*?))?\s?\)", RegexOptions.Compiled );

    public static string OptimizeSql( string commandText, IDictionary<string, object> parameters )
    {
        return regex.Replace(
            commandText,
            delegate( Match m )
            {
                var column = m.Groups[ "column" ].Value;
                var xpath = parameters[ m.Groups[ "xpath" ].Value ];

                var sqlType = "nvarchar( 4000 )";
                var compare = "";
                var type = m.Groups[ "type" ].Value;

                switch ( type )
                {
                    case "Float": sqlType = "float"; break;
                    case "DateTime": sqlType = "datetime"; break;
                    case "Bit": sqlType = "int"; compare = " = 1"; break;
                    case "Compare":
                    {
                        var oper = parameters[ m.Groups[ "oper" ].Value ];
                        var valueParam = m.Groups[ "value" ].Value;

                        var value = valueParam.StartsWith( "@" )
                            ? string.Format( "sql:variable(\"{0}\")", valueParam )
                            : string.Format( "\"{0}\"", valueParam );

                        return string.Format( "{0}.exist('{1}[.{2}{3}]')", column, xpath, oper, value );
                    }
                }

                return string.Format( "{0}.value('({1})[1]', '{2}'){3}", column, xpath, sqlType, compare );
            } );
    }
}

Here’s where you need to decide between the “safe” and risky ways to manipulate the command text.  Here’s the safe way:

    var command = context.GetCommand( results );
    var parameters = command.Parameters.Cast<DbParameter>().ToDictionary( p => p.ParameterName, p => p.Value );

    command.CommandText = XQueryHelper.OptimizeSql( command.CommandText, parameters );

    var newResults = context.ExecuteQuery( command.CommandText, parameters.Values );

The riskier (but more flexible and robust) way is discussed here.  Using this, all you need to do is modify your DataContext before executing the query.

    context = DataContextInterceptor.Intercept( context, XQueryHelper.OptimizeSql );

Hope you find this useful!

About these ads