Paulo Morgado : SoftDev

LINQ To SQL Tips & Tricks: String Operations

Paulo Morgado — Tue, 13 Oct 2009 00:51:43 GMT

LINQ brought developers a very user friendly and domain independent style of writing queries.

The fact that the way queries are written is domain independent doesn’t mean that any query will compile the same way or even run the same way. You’ll always need to know how the provider will behave.

LINQ To Objects, for example, will compile queries as a Func<> delegate and the query methods will return IEnumerable(T) implementations.

On the other hand, LINQ To SQL will compile queries as an Expression<Func<>> (which is, in fact, an expression tree) instance and the query methods will return IQueryable(T) implementations.

Because LINQ To SQL queries are compiled to an expression tree, that allows the provider to treat the query elements as it sees fit.

In this case, this means that all operations that can be done on the database will be done on the database and the developer must be aware of this when she/he is writing the queries.

Lets take an example using the AdventureWorks database (if you don’t have it, you can download it from here).

I want to build a list of salutation for every employee that has the SalariedFlag set, in the form of:

[Mr.|Mrs.|Miss] <first name> <middle name> <last name>

But there’s also one detail about the data in the database: FirstName, MiddleName and LastName may have trailing spaces and I don’t want them.

This is a simple query like this:

var q1 = from e in context.Employees
         where e.SalariedFlag
         select
            ((e.Gender == 'F') ? ((e.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " +
            e.Person.FirstName.Trim() +
            (e.Person.MiddleName == null || e.Person.MiddleName.Trim().Length == 0 ? " " : " " + e.Person.MiddleName.Trim() + " ") +
            e.Person.LastName.Trim();

and it will be executed as:

SELECT ((((
    (CASE
        WHEN UNICODE([t0].[Gender]) = @p0 THEN
            (CASE
                WHEN UNICODE([t0].[MaritalStatus]) = @p1 THEN @p2
                ELSE @p3
             END)
        ELSE CONVERT(NVarChar(4),@p4)
     END)) + @p5) + LTRIM(RTRIM([t1].[FirstName]))) + (
    (CASE
        WHEN ([t1].[MiddleName] IS NULL) OR (LEN(LTRIM(RTRIM([t1].[MiddleName]))) = @p6) THEN CONVERT(NVarChar(MAX),@p7)
        ELSE (@p8 + LTRIM(RTRIM([t1].[MiddleName]))) + @p9
     END))) + LTRIM(RTRIM([t1].[LastName])) AS [value]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [70]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [83]
-- @p2: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Miss]
-- @p3: Input NVarChar (Size = 4; Prec = 0; Scale = 0) [Mrs.]
-- @p4: Input NVarChar (Size = 3; Prec = 0; Scale = 0) [Mr.]
-- @p5: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p6: Input Int (Size = 0; Prec = 0; Scale = 0) [0]
-- @p7: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p8: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- @p9: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [ ]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

If you notice the query, there are a lot of text operations going on for each row.

Depending on the number of rows or database load this can prove to be very bad. The result might even be just a timeout.

So, how do we force the string operations to occur on the client instead of the database?

Only IQueryable<T> will be translated to T-SQL. So, all we need to do is change the type of the enumerator being iterated.

One way to do this is using the the AsEnumerable method of the Enumerable class.

The query would now be written as:

var q2 = from e in context.Employees.Where(e => e.SalariedFlag).AsEnumerable()
         select
            ((e.Gender == 'F') ? ((e.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " + e.Person.FirstName.Trim() +
            (e.Person.MiddleName == null || e.Person.MiddleName.Trim().Length == 0 ? " " : " " + e.Person.MiddleName.Trim() + " ") +
            e.Person.LastName.Trim();

and it will be executed as:

SELECT
    [t0].[BusinessEntityID],
    [t0].[LoginID],
    [t0].[NationalIDNumber],
    [t0].[JobTitle],
    [t0].[MaritalStatus],
    [t0].[BirthDate],
    [t0].[Gender],
    [t0].[HireDate],
    [t0].[SalariedFlag],
    [t0].[VacationHours],
    [t0].[SickLeaveHours],
    [t0].[CurrentFlag],
    [t0].[rowguid],
    [t0].[ModifiedDate],
    [t1].[BusinessEntityID] AS [BusinessEntityID2],
    [t1].[PersonType],
    [t1].[NameStyle],
    [t1].[Title],
    [t1].[FirstName],
    [t1].[MiddleName],
    [t1].[LastName],
    [t1].[Suffix],
    [t1].[EmailPromotion],
    [t1].[AdditionalContactInfo],
    [t1].[Demographics],
    [t1].[rowguid] AS [rowguid2],
    [t1].[ModifiedDate] AS [ModifiedDate2]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

As you can notice, text operations are no longer done on the database, but all the columns of both tables are being returned. And this is still a bad thing because we are using network bandwidth with data that won’t be used.

The way to choose the columns that will be retrieved from the database is by selecting only the ones wanted in the select statement. But because we still want string operations the be done on the client, we’ll need to project the desired columns into an intermediary object. Since we won’t need this object outside the query, we’ll use an anonymous type.

The query would now be written as:

var q3 = from n in
             (
                 from e in context.Employees
                 where e.SalariedFlag
                 select new
                 {
                     Gender = e.Gender,
                     MaritalStatus = e.MaritalStatus,
                     FirstName = e.Person.FirstName,
                     MiddleName = e.Person.MiddleName,
                     LastName = e.Person.LastName
                 }
             ).AsEnumerable()
         select ((n.Gender == 'F') ? ((n.MaritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " " + n.FirstName.Trim()
         + (n.MiddleName == null || n.MiddleName.Trim().Length == 0 ? " " : " " + n.MiddleName.Trim() + " ")
         + n.LastName.Trim();

and it will be executed as:

SELECT
    [t0].[Gender],
    [t0].[MaritalStatus],
    [t1].[FirstName],
    [t1].[MiddleName],
    [t1].[LastName]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

Notice the call to Enumerable.As Enumerable to translate the LINQ To SQL query into a LINQ To Objects query.

And, to end this long blog post, if you don’t use any string operations on the query, they, obviously, won’t be translated to T-SQL:

var q4 = from e in context.Employees
         where e.SalariedFlag
         select BuildSalutation(e.Gender, e.MaritalStatus, e.Person.FirstName, e.Person.MiddleName, e.Person.LastName);

where BuildSalutation is implemented as:

private static object BuildSalutation(char gender, char maritalStatus, string firstName, string middleName, string lastName)
{
    return ((gender == 'F') ? ((maritalStatus == 'S') ? "Miss" : "Mrs.") : "Mr.") + " "
        + firstName.Trim()
        + (middleName == null || middleName.Trim().Length == 0 ? " " : " " + middleName.Trim() + " ")
        + lastName.Trim();
}

and it will be executed as:

SELECT
    [t0].[Gender] AS [gender],
    [t0].[MaritalStatus] AS [maritalStatus],
    [t1].[FirstName] AS [firstName],
    [t1].[MiddleName] AS [middleName],
    [t1].[LastName] AS [lastName]
FROM [HumanResources].[Employee] AS [t0]
INNER JOIN [Person].[Person] AS [t1] ON [t1].[BusinessEntityID] = [t0].[BusinessEntityID]
WHERE [t0].[SalariedFlag] = 1
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 3.5.30729.4926

Have you noticed that this T-SQL query is pretty much the same in the previous example?

If you are still reading this, I hope you now aware of how you write your LINQ To SQL queries affect the generated T-SQL.

ReMIX 09 Is Coming To Lisbon, Portugal

Paulo Morgado — Wed, 09 Sep 2009 18:50:28 GMT

For the first time, ReMIX is coming to Portugal.

The event will have a keynote and 3 tracks (Web Developer, UX and Architect) with 4 sessions each by the best speakers in each field.

Don’t miss it! Register now!

Windows Live Messenger Unable To Connect With Error Code 80040200

Paulo Morgado — Tue, 01 Sep 2009 23:55:17 GMT

Today I came across this issue when trying to sign in to Windows Live Messenger.

Everyone I talked to was able to sign in, though, so I tried another account and was also able to sign in.

Binging around, I found several blog posts pointing to a post in the Messenger Support blog with the solution to this problem. In my case (Windows 7 x64) the solution would be to remove the %LOCALAPPDATA%\Microsoft\Windows Live Contacts (usually C:\Users\<Windows Logon name>\AppData\Local\Microsoft\Windows Live Contacts) folder.

Instead of removing the folder, I thought of renaming it to avoid removing my contacts for all account that were able to sign in. When I tried it, I got an error stating the the folder was already being used by another application.

Since I had Windows Live Mail open and assumed it uses the same contact store, I closed it and was able to sign in to messenger with no problems.

As a developer and architect, I find it disturbing that such errors are presented to the user. The message hinted that there was a problem signing in to the server and, as it turned out, it was a problem with only one account for only one Windows user in only one machine.

So, developers and architects out there (me included), always give the user an error message meaningful to what problem he/she is running into. Adding technical data to help support is nice but should be expressly accessed by the user (Windows Live Messenger got that part right).

Playing With SQL Server CLR Integration – Part IV (Deploying To SQL Server 2005)

Paulo Morgado — Sat, 13 Jun 2009 08:39:10 GMT

With all developed and tested on my laptop using SQL Server 2008, it’s time to deploy to the company’s test machine running SQL Server 2005.

The first thing I ran into when executing:

CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
GO

was:

Msg 10327, Level 14, State 1, Line 1
Assembly 'MyAssembly' references assembly 'system.xml.linq, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089.',
which is not present in the current database.
SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from,
but that operation has failed (reason: 2(The system cannot find the file specified.)).
Please load the referenced assembly into the current database and retry your request.

Looks like SQL Server 2005 doesn’t know about .NET Framework 3.5. I’d just load the assemblies being used: System.Core and System.Linq.Xml:

CREATE ASSEMBLY [System.Core]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = SAFE
GO

Not so easy:

Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'System.Core' failed because assembly 'System.Core' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.Diagnostics.Eventing.EventProvider::EtwRegister][mdToken=0x600003b][offset 0x0000003D][found Native Int][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::EncodeObject][mdToken=0x6000046][offset 0x00000000] Unmanaged pointers are not a verifiable type.
[ : System.Diagnostics.Eventing.EventProvider::WriteMessageEvent][mdToken=0x6000047][offset 0x0000003C][found ref 'System.String'] Expected numeric type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x0000012E] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x6000049][offset 0x00000030] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004a][offset 0x0000005F][found ref 'System.String'] Expected numeric type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteEvent][mdToken=0x600004b][offset 0x00000010][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x0000007D] Instruction cannot be verified.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004c][offset 0x00000309][found Native Int][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Diagnostics.Eventing.EventProvider::WriteTransferEvent][mdToken=0x600004d][offset 0x0000001B][found unmanaged pointer][expected unmanaged pointer] Unexpected type on the stack.
[ : System.Security.Cryptography.CapiNative::ImportSymmetricKey][mdToken=0x60007c2][offset 0x00000071][found address of Byte] Expected numeric type on the stac...

Ok. I’d just load it with PERMISSION_SET = UNSAFE:

CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = UNSAFE
GO

Not yet:

Warning: The Microsoft .Net frameworks assembly 'system.core, version=3.5.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.
Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'System.Core' failed because assembly 'System.Core' is not authorized for PERMISSION_SET = UNSAFE.
The assembly is authorized when either of the following is true: the database owner (DBO) has UNSAFE ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission.
If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server.
If not, use sp_changedbowner to fix the problem.

Solved:

ALTER DATABASE MyDatabase SET TRUSTWORTHY ON
GO

Only than I was able to load the .NET 3.5 assemblies:

CREATE ASSEMBLY [System.Core]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = UNSAFE
GO

CREATE ASSEMBLY [System.Xml.Linq]
AUTHORIZATION [dbo]
FROM 'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Xml.Linq.dll'
WITH PERMISSION_SET = UNSAFE
GO

With all dependencies in place, I tried to load the assembly with PERMISSION_SET = SAFE with no luck:

Msg 6212, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because method 'ShortPropsToXml' on type 'ShortProps'  in safe assembly 'Esi.SA.Encyclopedia' is storing to a static field.
Storing to a static field is not allowed in safe assemblies.

It had to be with PERMISSION_SET = UNSAFE.

After successfully loading the assemblies, I was finally able to create the Transact-SQL definitions for the functions (see Part I and Part II).

Now the DBAs won’t definitely let me use this, but it was fun to build it.

Playing With SQL Server CLR Integration – Part III

Paulo Morgado — Fri, 12 Jun 2009 00:11:00 GMT

You might have noticed that I used LINQ in my last Playing With SQL Server CLR Integration posts (Part I, Part II).

I couldn’t make it work with the standard Visual Studio 2008 SQL CLR project template. Changing the Target Framework to .NET Framework 3.5 wasn’t enough. I had to edit the .csproj file by hand:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="3.5" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <!— ... -->
  <ItemGroup>
    <!— ... -->
    <Reference Include="System.Core">
      <RequiredTargetFramework>3.5</RequiredTargetFramework>
    </Reference>
    <Reference Include="System.Xml.Linq">
      <RequiredTargetFramework>3.5</RequiredTargetFramework>
    </Reference>
  </ItemGroup>
  <!— ... -->
</Project>

Playing With SQL Server CLR Integration – Part II

Paulo Morgado — Thu, 11 Jun 2009 23:52:37 GMT

On my last post, I showed how to convert a property bag stored in text to a CLR Table-Valued Function.

I started thinking that I could retrieve the property values, but I couldn’t change them or add new properties.

Passing a table as a parameter is still not possible in SQL Server 2005. And that would force me to load the table into a variable, change it and load it back into the property bag.

Or I could create functions to Create, Update and Delete properties from the property bag.

Or I could just use XML. All it takes is creating a CLR Scalar-Valued Function to convert the property bag to an XML representation and another to convert the XML back to the property bag format.

It still makes me load the property bag into a variable if I want to change it, but it’s usable in SQL Server 2005, which was the targeted platform.

Converting the property bag to an XML document is easy using the previously created enumerator:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToXml",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlXml ShortPropsToXml(string shortPropsText)
{
    var xml = new XElement("ShortProps",
        from shortProp in ShortPropsEnumerable(shortPropsText)
        select new XElement("p",
            new XAttribute("n", shortProp.Key),
            new XCData(shortProp.Value)));

    using (var buffer = new MemoryStream())
    {
        using (var xmlWriter = XmlWriter.Create(buffer, new XmlWriterSettings { CheckCharacters = false }))
        {
            xml.WriteTo(xmlWriter);
        }

        buffer.Position = 0;

        using (XmlReader xmlReader = XmlReader.Create(buffer, new XmlReaderSettings { CheckCharacters = false }))
        {
            return new SqlXml(xmlReader);
        }
    }
}

Converting the an XML document back into the property bag format is also easy:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "XmlToShortProps",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlChars XmlToShortProps(SqlXml shortPropsXml)
{
    var xml = XDocument.Parse(shortPropsXml.Value);

    var textBuilder = new StringBuilder();
    foreach (var item in xml.Document.Element("ShortProps").Elements("p"))
    {
        textBuilder.AppendFormat("[[[{1}]]]{0}{2}{0}", Environment.NewLine, item.Attribute("n").Value, item.Value);
    }

    return new SqlChars(textBuilder.ToString().ToCharArray());
}

Now it’s just uploading the new version of the assembly to the database:

ALTER ASSEMBLY [MyAssembly]
FROM '...\MyAssembly.dll'
GO

And defining the Scalar-Valued Functions in Transact-SQL:

CREATE FUNCTION [dbo].[ShortPropsToXml](@shortPropsText [nvarchar](4000))
RETURNS [xml] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[ShortPropsToXml]
GO

CREATE FUNCTION [dbo].[XmlToShortProps](@shortPropsXml [xml])
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[XmlToShortProps]
GO

And it’s all set to go.

Now, given this property bag definition:

declare @text nvarchar(max)='[[[name1]]]
value1
[[[name2]]]
value2
[[[name3]]]
value3
'

I can change it into XML:

DECLARE @xml [xml] = dbo.ShortPropsToXml(@text)

Update:

set @xml.modify('replace value of (/ShortProps/p[@n="name2"]/text())[1] with "new value2"')

Insert:

set @xml.modify('insert <p n="name4">Value4.1
Value4.2</p> after (/ShortProps/p[@n="name2"])[1]')

Delete

set @xml.modify('delete (/ShortProps/p[@n="name3"])[1]')

Convert back to the property bag format:

print dbo.XmlToShortProps(@xml)

[[[name1]]]
value1
[[[name2]]]
new value2
[[[name4]]]
Value4.1
Value4.2

Query as a table:

select T.C.value('./@n', 'nvarchar(max)') as Name, T.C.value('.', 'nvarchar(max)') as Value from @xml.nodes('/ShortProps/p') T(C)

Name	Value
name1	value1
name2	new value2
name4	Value4.1 Value4.2

Unfortunately, the application uses characters that are invalid to SQL Server as XML characters and I can’t use it on that application. I’ll have to understand those values better and add other fields to the table and XML.

Playing With SQL Server CLR Integration – Part I

Paulo Morgado — Thu, 11 Jun 2009 23:41:14 GMT

I’m currently working with an application that stores a property bag in a SQL Server column like this:

[[[name1]]]
value1
[[[name2]]]
value2.1
value2.2
[[[name3]]]
value3

Don’t ask me why it’s done like this. It just is.

The application decodes this property bag into its inner structures and all works fine.

Sometimes I would like to query the database directly or do some reporting on those properties and just can’t.

So, I thought this was a good use case for SQL Server CLR Integration. I decided to build a CLR Table-Valued Function that would return the property bag as a two column table.

Parsing the property bag text can easily be achieved with a simple regular expression:

new Regex(
        string.Format(@"(?<Name>(?<=\[\[\[).*(?=\]\]\]{0}))\]\]\]{0}(?<Value>(([\s\S]*?(?={0}\[\[\[))|([\s\S]*?(?={0}$))))", Environment.NewLine),
        RegexOptions.Multiline | RegexOptions.ExplicitCapture | RegexOptions.CultureInvariant | RegexOptions.Compiled);

Ultrapico’s Expresso was a big help when developing this regular expression.

In case you don’t know, the way CLR Table-Valued Function are built is using an initial method to take the input and return an IEnumerable and row filler method the receives the enumerator item and outputs the column values.

Since these are really key-value pairs of strings, I decided to use )>) Structure" href="http://msdn.microsoft.com/library/5tbh8a42.aspx" target=_blank>KeyValuePair<string, string> instances to store each item and the enumerator became simply this:

private static IEnumerable<KeyValuePair<string, string>> ShortPropsEnumerable(string shortPropsText)
{
    return from Match m in shortPropsRegex.Matches(shortPropsText)
           select new KeyValuePair<string, string>(m.Groups["Name"].Value, m.Groups["Value"].Value);
}

And the implementation of the CLR Table-Valued Function is as simple as this:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToTable",
    FillRowMethodName = "ShortPropsToTableFillRow",
    TableDefinition = "Name NVARCHAR(4000), Value NVARCHAR(4000)",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static IEnumerable ShortPropsToTable(string shortPropsText)
{
    return ShortPropsEnumerable(shortPropsText);
}

public static void ShortPropsToTableFillRow(object item, out SqlChars name, out SqlChars value)
{
    KeyValuePair<string, string> shortProp = (KeyValuePair<string, string>)item;

    name = new SqlChars(shortProp.Key);
    value = new SqlChars(shortProp.Value);
}

To use this in SQL Server a few simple steps are need:

Load the assembly into the database:

CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
GO

Defining the Table-Valued User-Defined Function in Transact-SQL:

CREATE FUNCTION [dbo].[ShortPropsToTable](@shortPropsText [nvarchar](4000))
RETURNS  TABLE (
    [Name] [nvarchar](4000) NULL,
    [Value] [nvarchar](4000) NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [MyAssembly].[ShortProps].[ShortPropsToTable]
GO

Enable CLR Integration:

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

And all is set to go.

Now I can just query the property bag as a table:

SELECT
    e.[ID],
    e.[Name],
    e.[Class],
    e.[Type],
    p.[Name],
    p.[Value]
FROM
    dbo.Entity as e
    CROSS APPLY dbo.ShortPropsToTable(e.[ShortProps]) as p

Just for curiosity, for a little over 50000 entities (that can be retrieved on about 1 second on my laptop), I got a little under 630000 properties in les then 40 seconds.

40 seconds might seem a lot compared to the 1 second, but I would like to see better times using T-SQL. And develop and test the TVF in just a couple of hours.

Now, if only he DBAs would allow me to use it. It doesn’t fit into the company’s “security policy”, you know.

Typemock Is Launching The ASP.NET Bundle – Get Free Licenses

Paulo Morgado — Mon, 18 May 2009 22:57:00 GMT

Typemock is launching a new product for ASP.NET developers – the ASP.NET Bundle - and for the launch they are giving out FREE licenses to bloggers and their readers.

The ASP.NET Bundle is the ultimate ASP.NET unit testing solution, and offers both Typemock Isolator and Ivonna, the ASP.NET Isolator add-on, for a discounted price.

ASP.NET Bundle launch giveaway:
For the ASP.NET Bundle launch Typemock are giving away free licenses to bloggers and their readers.

How do I get the free license?

Are you a blogger, webmaster, or internet columnist? Get your free license by helping Typemock launch their new ASP.NET Bundle, the ultimate ASP.NET unit testing solution.

Post the text below on your blog (as is, with links) and tell them about it . If you are in the first 60 to mail us after posting, your license will be on its way!

If you have an ASP.NET dedicated blog, you'll get a license automatically (even if more than 60 submit) during the first week of this announcement.

8 bloggers will also get an additional 2 licenses (each) to give away / raffle to their readers or friends.

A few simple requirements:

You must own a website, blog space or internet column, older than 2 months, or get permission from the owner of such a site.
Your post must include the text below (including the links) between *Begin* and *End*.

*Begin*

Unit Testing ASP.NET? ASP.NET unit testing has never been this easy.

Typemock is launching a new product for ASP.NET developers – the ASP.NET Bundle - and for the launch will be giving out FREE licenses to bloggers and their readers.

The ASP.NET Bundle is the ultimate ASP.NET unit testing solution, and offers both Typemock Isolator, a unit test tool and Ivonna, the Isolator add-on for ASP.NET unit testing, for a bargain price.

Typemock Isolator is a leading .NET unit testing tool (C# and VB.NET) for many ‘hard to test’ technologies such as SharePoint, ASP.NET, MVC, WCF, WPF, Silverlight and more. Note that for unit testing Silverlight there is an open source Isolator add-on called SilverUnit.

The first 60 bloggers who will blog this text in their blog and tell us about it, will get a Free Isolator ASP.NET Bundle license (Typemock Isolator + Ivonna). If you post this in an ASP.NET dedicated blog, you'll get a license automatically (even if more than 60 submit) during the first week of this announcement.

Also 8 bloggers will get an additional 2 licenses (each) to give away to their readers / friends.

Go ahead, click the following link for more information on how to get your free license.

*End*

Once you post this in your site, just drop them a line at asp@typemock.com with your name and the post URL to get your free license.

Hurry up – this offer is limited to the first 60 bloggers, or to ASP.NET dedicated bloggers who will post this in the first week.

Make sure to follow their Blog, Site or Twitter for updates on this event.

LINQ With C# Book Is Finally Out

Paulo Morgado — Sun, 17 May 2009 23:39:51 GMT

It’s finally out!

The LINQ Com C# (LINQ With C#) book that Luís and I wrote is out. Well, mostly Luís than I.

This book, published by FCA, is targeted at anyone that already knows C# 2.0 and wants to know learn the new features introduced with C# 3.0 that made possible LINQ (Language INtegrated Query). The examples in the book are written in C#, but Visual Basic get be get from the book’s site.

	Title:	LINQ Com C#
	Authors:	Luís Abreu / Paulo Morgado
	ISBN:	978-972-722-547-7
	Number of Pages:	216
	Format:	17,0 x 24,0 x 1,3 cm (6.7 x 9.4 x 0.5 in)

The book can be found here:

ASP.NET Futures: Control ClientID Generation

Paulo Morgado — Wed, 18 Mar 2009 01:44:50 GMT

ASP.NET is expected to have some improvements on the generation of client IDs.

Although this is a major improvement, it comes short by not allowing the generation of shorter client IDs for server controls.

My good friend Nuno Gomes has done some work on generating shorter client IDs for controls (*).

Jeff has taken it one step further with his How to shorten ASP.NET automatically generated control IDs article on CodeProject.

If you want to see a running example, check out http://www.biocompare.com/.

(*)

How To Issue Server Callbacks

Paulo Morgado — Mon, 15 Dec 2008 01:03:48 GMT

Callbacks were introduced in ASP.NET 2.0 and is a simple mechanism for calling page or control functionality without page rendering and without the user noticing a post back.

For a page or control to handle callbacks, all it needs is to implement the ICallbackEventHandler Interface.

When the client calls back to de page or control, the initial state of the controls is posted along with the control being called upon in the __CALLBACKID field and the callback parameter in the __CALLBACKPARAM field.

It’s quite a simple procedure.

But what if you want to issue a callback server side?

In order for a request to be identified as a callback (IsCallback), the request must be a postback (IsPostback) and the before mentioned fields must be in the post data of the request. On the other hand, for a request to be considered a postback, the level of server calls (Transfer or Execute) must be 0 (meaning that the current request hasn’t made any Transfer or Execute calls) or the type of the page is the same of the Handler for the current request and the HTTP method is POST.

Changing the HTTP method is (as far as I know) impossible. So, if the request is not already a POST, there’s no way to issue a callback.

Setting the post data is easier. All it’s needed is to override the page’s DeterminePostBackMode method (or in a page adapter) and return the post data previously saved in a context item. Something like this:

protected override NameValueCollection DeterminePostBackMode()
{
    NameValueCollection postBackMode = Context.Items["callbackPostData"] as NameValueCollection;

    return (postBackMode != null) ? postBackMode : base.DeterminePostBackMode();
}

And issue a callback is something like this:

IHttpHandler handler = this.Context.Handler;
try
{
    NameValueCollection postData = new NameValueCollection();
    postData.Add("__CALLBACKID", sender);
    postData.Add("__CALLBACKPARAM", this.argument.Text);

    Context.Items["callbackPostData"] = postData;

    Page calledPage = (Page)PageParser.GetCompiledPageInstance("~/Callback1.aspx", this.Server.MapPath("~/Callback1.aspx"), this.Context);

    this.Context.Handler = calledPage;

    StringWriter writer = new StringWriter();

    Server.Execute(calledPage, writer, false);

    this.response.Text = writer.ToString();
}
finally
{
    this.Context.Handler = handler;
}

You can find an implementation of a caller and a called page here.

PDC2008: Is My House On The Cloud Or Is The Cloud In My House?

Paulo Morgado — Sun, 19 Oct 2008 17:41:00 GMT

This year’s PDC is mostly about the cloud.

Everyone is talking about the cloud: cloud services, cloud computing, etc. but I’m not sure everyone has the same understanding of what the cloud is. Let’s say it’s a cloudy concept.

So, what is this cloud thing, anyway?

Let’s take Windows Home Server (WHS) as an example. Microsoft provides a set of services through homeserver.com that allows me to control my home server and access my other PCs in the home network. I guess I could say that homeserver.com is a cloud platform that puts my house in the cloud.

On the other hand, WHS is built on top of Windows Server 2003 which allows me to take full advantage of its web server (IIS) to expose services through homeserver.com. Services that could be for my personal use or that I could provide to others, thus putting my house on the cloud.

At a larger scale, that’s how I see the cloud and it’s usage. An indistinct universe of service providers and service consumers where some are just providers, others are just consumers and others are both (service aggregators, value added brokers, etc.)

I guess that Microsoft’s view in regards to the cloud and WHS is aligned with mine because there will be two PDC sessions about this:

Developing Connected Home Applications and Services for Windows Home Server

Tue 10/28 | 5:15 PM-6:30 PM | 409A

Presenter(s): CJ Saretto, Fabian Uhse

Learn how to build applications and services deployed on Windows Home Server that power PCs and devices throughout a connected home. See an example home automation and energy management service, and watch the creation of an on-premises service that exposes HVAC, window shades, and lighting controls to all devices on the home network. Hear how to package the service as a Windows Home Server add-in, quickly create a configuration UI, deploy a PC application for controlling the service, and demonstrate control from other devices such as TVs and cell phones.
Exposing Connected Home Services to the Internet via Windows Home Server

Wed 10/29 | 10:30 AM-11:45 AM | 409A

Presenter(s): Brendan Grant, CJ Saretto

Learn how to leverage the Remote Access platform in Windows Home Server to expose on-premises services running in the connected home to the Internet. See how to expose a home automation service running on Windows Home Server to the Internet, and watch a demonstration that shows how to expose services that may be hosted on other devices inside the connected home using Windows Home Server as a gateway. Finally, see a sneak preview of Home Server and Live Mesh working together to further enhance the remote experience for the connected home.

Windows Home Server Resources

Windows Home Server
Microsoft Windows Home Server Home Page
"Stay-At-Home" Server Neighbor Networks
An online bulletin board resource for families with "Stay-At-Home" Servers
We Got Served
Windows Home Server & Your Digital Home

And, suddenly, a cloud in your future is not a ad thing, quite the opposite.

PDC2008: Are Windows Forms Dead?

Paulo Morgado — Thu, 09 Oct 2008 00:33:59 GMT

This year’s PDC is all about the cloud (or cloudy as I call it) and the .NET Framework 4.0.

No one at Microsoft has ever told me that Windows Forms are dead (quite the opposite) but looking at the list of sessions for this year’s PDC I can only see WPF.

So the question remains: Are Windows Forms dead?

Typemock Isolator 5.1 Released

Paulo Morgado — Mon, 06 Oct 2008 22:41:57 GMT

This major version adds static method support and non-public method faking to the AAA API. Check out the release notes.

I don’t like the reflective approach to testing private methods.

With the new additions to the AAA API, testing this class:

public class MyClass
{
    public string Public()
    {
        return this.Private();
    }

    private string Private()
    {
        throw new NotImplementedException();
    }
}

can be done like this:

[TestMethod]
[Isolated]
public void PrivateTest()
{
    MyClass fake = Isolate.Fake.Instance<MyClass>();

    Isolate.WhenCalled(() => fake.Public()).CallOriginal();

    Isolate.NonPublic.WhenCalled(fake, "Private").WillReturn("FAKE");

    string fakePublic = fake.Public();

    Assert.AreEqual("FAKE", fakePublic);

    Isolate.Verify.WasCalledWithExactArguments(() => fake.Public());

    Isolate.Verify.NonPublic.WasCalled(fake, "Private");
}

I would like it better if it was like this:

[TestMethod]
[Isolated]
public void PrivateTest()
{
    MyClass fake = Isolate.Fake.Instance<MyClass>();

    MyClass_Accessor fakeAccessor = MyClass_Accessor.AttachShadow(fake);

    Isolate.WhenCalled(() => fakeAccessor.Private()).WillReturn("FAKE");

    Isolate.WhenCalled(() => fake.Public()).CallOriginal();

    string fakePublic = fake.Public();

    Assert.AreEqual("FAKE", fakePublic);

    Isolate.Verify.WasCalledWithExactArguments(() => fake.Public());

    Isolate.Verify.WasCalledWithExactArguments(() => fakeAccessor.Private());
}

Looks almost the same but there aren’t any method names in the test code.

They were able to do it for Natural Mocks. I’m sure they will eventually do it for AAA.

Faking Output Parameters With Typemock Isolator

Paulo Morgado — Mon, 06 Oct 2008 20:51:40 GMT

Some time ago I was asked if it was possible to fake output parameters with Typemock Isolator.

It’s actually very easy using any of the APIs.

Given this class:

public class MyClass
{
    public bool MyMethod(string input, out int output1, out double output2)
    {
        throw new NotImplementedException();
    }
}

Using the new AAA API, it's as clean as:

[TestMethod]
[Isolated]
public void TestMethodIsolated()
{
    MyClass target = Isolate.Fake.Instance<MyClass>();

    string input = "test value";
    int expectedOutput1 = 1;
    double expectedOutput2 = 2;

    Isolate.WhenCalled(() => target.MyMethod(input, out expectedOutput1, out expectedOutput2)).WillReturn(true);

    int output1;
    double output2;
    bool result = target.MyMethod(input, out output1, out output2);

    Assert.IsTrue(result);
    Assert.AreEqual<int>(expectedOutput1, output1);
    Assert.AreEqual<double>(expectedOutput2, output2);
}

Using Natural Mocks, it's as easy as:

[TestMethod]
[VerifyMocks]
public void TestMethodNatural()
{
    MyClass target = RecorderManager.CreateMockedObject<MyClass>();

    string input = "test value";
    int expectedOutput1 = 1;
    double expectedOutput2 = 2;

    using (RecordExpectations recorder = RecorderManager.StartRecording())
    {
        recorder.ExpectAndReturn(target.MyMethod(input, out expectedOutput1, out expectedOutput2), true);
    }

    int output1;
    double output2;
    bool result = target.MyMethod(input, out output1, out output2);

    Assert.IsTrue(result);
    Assert.AreEqual<int>(expectedOutput1, output1);
    Assert.AreEqual<double>(expectedOutput2, output2);
}

It's also possible using Reflective Mocks:

[TestMethod]
[VerifyMocks]
public void TestMethodReflective()
{
    MockObject<MyClass> targetMock = MockManager.MockObject<MyClass>();

    string input = "test value";
    int expectedOutput1 = 1;
    double expectedOutput2 = 2;

    targetMock.ExpectAndReturn(
        "MyMethod",
        new DynamicReturnValue(delegate(object[] parameters, object context)
            {
                parameters[1] = expectedOutput1;
                parameters[2] = expectedOutput2;
                return true;
            }));

    int output1;
    double output2;
    bool result = targetMock.Object.MyMethod(input, out output1, out output2);

    Assert.IsTrue(result);
    Assert.AreEqual<int>(expectedOutput1, output1);
    Assert.AreEqual<double>(expectedOutput2, output2);
}

All you have to do is choose which one you like most.

LINQ With C# Book

Paulo Morgado — Sun, 21 Sep 2008 23:42:00 GMT

Luís just broke the news on our LINQ with C# book.

I was honored with the invitation from Luís to write this book with him for FCA, for which he has already published a few books [^] [^] [^] before.

This will be an entry level book in Portuguese targeted to anyone wanting to learn LINQ with C#.

It has been a fun project with great discussions (only possible because we have half an ocean between us :) ).

PDC2008: Meet Me In Los Angeles

Paulo Morgado — Thu, 04 Sep 2008 00:23:28 GMT

It’s not the first time I try but, for one reason or another, this is the first time I will go to the PDC (Microsoft’s Professional Developers Conference).

According to the site “The PDC is designed for leading-edge developers and software architects. If you’re interested in the future of the Microsoft platform, you’re responsible for the technical strategy in your organization, or you’re a highly skilled developer who likes to delve deep into the heart of the platform, then the PDC is for you!”. If you fit this description, hurry up and register. Early bird discount has been extended until September 8^th.

What I like most of these events is networking with Microsoft staff and other attendees. So, if you want to meet me, I’ll be glad to meet you.

Clone Detective For Visual Studio

Paulo Morgado — Thu, 28 Aug 2008 22:41:36 GMT

Clone Detective is a tool that integrates with Visual Studio and uses the ConQAT (Continuous Quality Assessment Toolkit) to analyze C# projects and search for duplicated source code.

Watch the videos and see if this is the tool you were looking for.

StyleCop 4.3 Is Out

Paulo Morgado — Wed, 20 Aug 2008 23:36:21 GMT

The StyleCop team announced the release of a version 4.3 of the StyleCop tool. You can get it from here.

On this version there are some bug fixes, new rules and documentation.

Also in this version, the list of errors and warnings goes to the Errors List window like with the compilers. I whish that the errors and warnings would also be sent to the Output window.

SDK documentation on how to author custom rules and integrate the tool with custom build environments is expected soon.

Lutz Roeder’s .NET Reflector Is Now Red Gate’s .NET Reflector

Paulo Morgado — Wed, 20 Aug 2008 22:34:29 GMT

Let’s face it, if you don’t know .NET Reflector, you can never claim to be a .NET developer.

Today Red Gate announced the acquisition of Lutz Roeder’s .NET Reflector.

On .NET Reflector’s page, Red Gate states that “will continue to maintain a free version for the benefit of the community”.

You can read an interview with Lutz Roeder and James Moore (general manager of .NET Developer Tools at Red Gate) at simple-talk.

James doesn’t know yet how to improve Reflector, but I do. Reflector needs a major improvement on UI usability and performance. Let’s see if I can come up with a list:

For me, search as you type is not a good idea as it is in Reflector.
Still in the search theme, search as you type would be nice for the active code window.
I cannot understand why changing any of the options fires a total repaint and lost of the view of the active code item.
Settings like code, documentation and number formatting should possible to change on the fly with a simple toolbar click.

I’m sure Red Gate is more than capable of taking good care of .NET Reflector.