How to execute LINQ TO SQL within a T4 Template?

Question

Is it possible to execute a SQL (TSQL or Linq To SQL) from within a T4 Template so that the schema information from a table can be used to customized the code generation?

Thanks

Answer 1

Yes of course - you can execute any arbitrary valid .NET code in a T4 template - that template gets converted into a .NET class, after all, which then gets compiled and executed.

What's your issue? What have you tried to do and how far have you come? Where are the roadblocks??

UPDATE:
I tried the following:

• I put a Linq-to-SQL model into a class library called AdventureWorksData
• I then created a new project (console app)
• I referenced that AdventureWorksData assembly

With this setup in place, I can write a T4 template something like this:

<#@ Template Language="C#v3.5" Debug="true" #>
<#@ Output Extension=".cs" #>
<#@ Assembly Name="System.Core.dll" #>
<#@ Assembly Name="System.Data.dll" #>
<#@ Assembly Name="System.Data.Linq.dll" #>
<#@ Assembly Name="AdventureWorksData.dll" #>
<#@ Import Namespace="System" #>
<#@ Import Namespace="System.Data" #>
<#@ Import Namespace="System.Data.Linq" #>
<#@ Import Namespace="System.Linq" #>
<#@ Import Namespace="AdventureWorksData" #>
using System;
using AdventureWorksData;

namespace AdventureWorks.Products
{
    public class ProductList
    {
<#
      AdventureWorksDataContext ctx = new AdventureWorksDataContext();

      var result = from p in ctx.Products 
                   where p.ProductCategoryID == 5 
                   select p;

      foreach (Product product in result)
      { 
#>
            public string <#= SanitizeName(product.Name) #> = "<#= product.ProductNumber #> / ID= <#= product.ProductID #>";
<#
      }
#>
    }
}
<#+
       internal string SanitizeName(string input)
       {
           return input.Replace(" ", "_").Replace("-", "_").Replace("/", "_").Replace(".", "_").Replace(",", "_");
       }
#>

What I do here is enumerate over the Products in the AdventureWorksLT sample database, and I grab all products with ProductCategoryID == 5, using Linq.

I then create a class which holds strings where the name of the string variable corresponds to the (sanitized) name of the product itself, and the string value contains some of the bits of information on that product in the database.

As you can see, you can fairly easily incorporate a Linq-to-SQL model and a LINQ query into your T4 template.