More on DLINQ and Projection Operator

I posted my observation on DLINQ and Projection Operator on MSDN Forum and soon received a response from Keith J. Farmer at Microsoft.

The answer was somewhat expected: DLINQ intends to translate everything to SQL to avoid any type of client side query for performance reasons. I can understand this for where and orderby clause, but not for select and projection operator I was using.

On the other hand, Keith did recommend a practical solution: put the .Net method call in SQL/CLR so that it can be called as a user defined function in SQL. Then map the UDF in DLINQ so that it can be used from the query:

• First, create a SQL Server Project in Visual Studio and create the UDF. This is really nothing new and can be done in VS2005.
  
  

      [Microsoft.SqlServer.Server.SqlFunction]

      public static SqlString StringFormat(

          SqlString format,

          SqlString o1,

          SqlMoney o2)

      {

          return new SqlString(string.Format(

              format.Value, o1.Value, o2.Value)

          );

      }

• Deploy the UDF in SQL Server. This can be done inside VS2005 (Build -> Deploy [project name]) or manually (see this post for steps).
• You probably don’t want to manually write the mapping code for the UDF this time. So use SqlMetal, which is provided as part of .NET 3.5 Beta, to generate the database mapping file:
  
  

  sqlmetal /server:localhost /database:northwind

           /code:northwind.cs /functions
           /namespace:MyDLinqTest

• Now we have mapped the entire Northwind database with all functions, we can use its tables and UDFs in DLINQ query like this:
  

  Northwind db = new Northwind(ConnStr);

  var list = from p in db.Products

             where p.UnitPrice > 30

             select new {

                p.ProductID,

                Description 
                  = db.StringFormat("{0} - {1}",

                    p.ProductName, p.UnitPrice)

             };