LINQ to SQL template for Visual Studio 2008

A newer version of this LINQ to SQL template is available.

If you want to customize the LINQ to SQL code generation phase in your project without additional tool dependencies this could be what you’re looking for.

*11 Dec 2008* Fixes to association code with one-to-one’s and with no serialization required. *7 Dec 2008* Added IsDelayLoaded, IsUnique. Fixed association code, stored proc update overrides. *17 Nov 2008* Added IsInheritanceDefault read/emit IsDefault on type *26 Oct 2008* Emit column Name attribute if it doesn’t match the member (thanks Steele) *20 Oct 2008* Handle class naming better, fix associations for renamed or out-of-sequence keys and their access modifiers *25 Sep 2008* Figure out missing association keys by using either sides primary key *25 Sep 2008* Empty DBML name-space is now no namespace instead of “MyApplication” *23 Sep 2008* fixed stored procedures with 0 parameters *22 Sep 2008* fixed VB.NET IsForeignKey attribute for associations *18 Sep 2008* now generates stored procedures including insert/update/delete with concurrency checking.

New since ‘reloaded’ version

  • Inheritance – generates sub-classes with all properties and code mappings.
  • VB.NETCSharpDataContext.tt is joined by a VB.NET emitting VBNetDataContext.tt.
  • DataContract SP1 – additional mode to emit SP1-compatible DataContract serialization via Roger Jennings.
  • Composite keys – both as the primary key and as a foreign key in an association.
  • Type attributes – the data context and entity types can now be sealed or abstract as well as public, private, protected, internal or protected internal.
  • Associations – prevents foreign key values changing once the object association is made and updates parent side of one-to-many associations.
  • Stored procedures – generates method wrappers and associated methods to facilitate insert/update/delete with concurrency support.

Functionality compared to designer

A primary goal in developing the template was to allow for easy switching between the template and the LINQ to SQL designer so things are very similar.

Missing

  • Comprehensive sanity checking on the DBML.
  • The Custom Tool Namespace and project namespaces are not pulled in when the DBML namespaces not specified.

Fixed

The designer has a few bugs which helpfully this template doesn’t suffer from.

  • Modifying a table via a stored procedure using original values for concurrency will throw ChangeConflictException and not silently fail.
  • Protected internal virtual property doesn’t forget to be virtual.
  • Checks all associations based on a foreign key are not loaded before allowing change and not just the first one.

Improved

  • Fully customizable with full source.
  • Serialization mode to support DataContract improvements in .NET 3.5 SP1 To use uncomment the line // data.Serialization = SerializationMode.DataContractSP1; in xDataClasses.tt
  • CanBeNull attribute generated for value types (useful when working with metadata).

Source compared to designer

The designer generated code can be difficult to read and isn’t well suited to template generation so the output from this template is different in a number of ways:

Sequence

Everything related to a column mapping – the storage variable, event signatures, attribute and the property itself – is batched together so it can be hit with a single loop making the template shorter and easier to work with.

#regions

Opinion may be divided on the usefulness of #regions in your own code but for code generation of large files I found it invaluable. There are regions for the logical parts of the data context and within each entity such as construction, column mapping, associations and serialization.

Style

The code generated should be a little easier to follow – if/else ordering, no redundant casts or extra brackets etc.

Namespace

I don’t believe adding “this” everywhere or fully qualifying attributes and exceptions makes things easy to read. I realize this might cause some name conflicts for some people but it is easy to change yourself and means the code is shorter and easier to work with for the majority.

Getting started

Although I work on the LINQ to SQL team this template should be treated as a third-party sample and is not supported by Microsoft.

Download via CodePlex

  1. Add the L2ST4.ttinclude and either CSharpDataContext.tt or VBNetDataContext.tt to your project depending on your language type
  2. Rename the xDataContext.tt to match your DBML file but with .tt extension instead of .dbml
  3. Set the existing DBML file’s .designer.cs/vb Build Action property to None to ignore the LINQ to SQL built-in code generation

Note that the template will only regenerate when it has been changed so use Run Custom Tool from the template’s right-mouse button menu in Solution Explorer when you’ve changed the DBML.

Should you wish to switch back to using the designer code then set the DBML file’s .designer.cs/vb Build Action to Compile and either remove the .tt and .ttinclude file for permanent removal or just set the .generated.cs/vb Build Action to None to keep it around. VB.NET users will need to use Show All Files to see the .designer.vb file.

Customization

The template run-time built into Visual Studio 2008 is called T4 and requires no additional tools however if you do a lot of editing you might want to install the Clarius T4 Editor for syntax highlighting and also check out the treasure trove of T4 material that is Oleg Sych’s blog.

The template is simple to follow, it loads the DBML file as an XML document then uses LINQ to XML to instantiate wrapper objects over the elements. This gives you a simple way to change default naming and behavior while making the template simpler to work with.

Let me know how you get on by leaving a comment here.

[)amien

24 responses

  1. Avatar for liviu

    Simply excellent. Congratulations for sharing this!

    I have encountered one problem till now:

    It does not use the BaseType set for the DataContext in dbml.

    liviu 17 September 2008
  2. Avatar for Damien Guard

    The template has now been fixed to honour BaseType on the DataContext.

    Thanks for reporting this and the other issues to me and helping with the fixes :)

    Damien Guard 17 September 2008
  3. Avatar for Carlos Beppler

    Hi, looking at the code on L2ST4.tt, I can not find how to activate the DataContractSP1 feature without break the dbml designer as it does not accept the Serialization="DataContractSP1" on Database tag.

    About Visual Studio reports: Microsoft Visual Studio 2008 Version 8.0.30729.1 SP

    Microsoft .NET Framework Version 3.5 SP1.

    Carlos Beppler 17 September 2008
  4. Avatar for Damien Guard

    If you look at the DataContext.tt file you will see a line commented out that you can enable that forces the SP1 mode.

    Damien Guard 17 September 2008
  5. Avatar for liviu

    Regarding the fact that the code generation is not triggered by DBML file changes, but by .tt template.... I think that an addin could do the trick ;)

    liviu 17 September 2008
  6. Avatar for liviu

    Hi, I tested also the codesmith templates for Linq2sql. They are much cleaner vs the T4 code and easier to customize and there is a good designer for the code. Another big advantage for me is that i can specify the code generation in a prebuild event, so i don't need to trigger the t4 template manually, I just BUILD the project...

    Would you like to develop also a Codesmith variant of the t4 templates?

    liviu 18 September 2008
  7. Avatar for Damien Guard

    I did a CodeSmith version of the templates a long time ago although they built off SQL directly and not the DBML and didn't do relationships etc.

    One of my primary goals here is to avoid tool dependencies - getting 10+ developers on a project to install, learn and pay for additional tools can be a real barrier to entry in my experience.

    Damien Guard 18 September 2008
  8. Avatar for liviu

    Hi Damien, I am back to the TT version. What i am experimenting right now is a way to avoid the designer completely, that is to generate the dbml automatically from a specification written inside an assembly. This is awesome because it is MORE productive to write a property in a cs file than to click around the designer. It takes 30 seconds to write:

    public abstract class Entity : PersistTable {
        [Size(200)]
        public string EntityName { get; set; }
    }
    
    [ForeignKey(typeof(Entity), "Fields", "Entity")]
    public abstract class Field : PersistTable {
        [Size(80)]
        public string FieldName { get; set; }
        [Size(150)]
        public string FieldType { get; set; }
    }
    
    public class DbModelConfiguration : PersistenceConfiguration {
        public DbModelConfiguration() {
        AutoKeyName = "Id";
        AutoKeyType = typeof(Guid);
        ContextBaseClass = "DataContextEx";
        ContextClassName = "DbModelDataContext";
    }
    

    Based on this at prebuild event of my Linq2sql model assembly, i generate the dbml file. After that, of course i have to click on the .tt template, but t4 engine can be started independently of the ide. So my next step will be to automate just that.

    liviu 18 September 2008
  9. Avatar for liviu

    Hi, I found the way to automate the t4 transformation. Microsoft offers TextTransform.exe in Common Files\Microsoft Shared. Awesome. Now i am happy. I just build after modifying the specification classes and my linq classes are generated. No more designer stuff, no more click around...

    liviu 18 September 2008
  10. Avatar for Sidar Ok

    This is going in a very nice direction. We can add the ability to make DataContext implement an IMyDataContext interface so that it can easily be mocked out when needed on repository tests.

    Also we can do a POCO generator to do all he heavy code lifting with identity management and lazy loading, but without EntitySets and EntityRef s.

    Just ideas. If you care to open source it, we can send patches :)

    Keep up the good work.

    Sidar

    Sidar Ok 22 September 2008
  11. Avatar for liviu

    Sidar,

    Could you please post a link with an example of poco linq without EntitySets and EntityRefs. I hide the primary key implementation in my base class but I still use EntitySets...

    liviu 23 September 2008
  12. Avatar for Steele

    I found a minor bug in the VBNetDataClasses.tt file. StoredProcs with no Input Parameters generate an ExecuteMthodCall with an extra Comma on Line 121

    Dim result As IExecuteResult = ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod,MethodInfo),  "[" + p.Name + "]").ToArray())#>)
    

    the Comma before the String.Join needs to trap for no parameters.

    Steele 23 September 2008
  13. Avatar for Sidar Ok

    liviu, I gave a talk recently and in that talk, I did a demo to enable pocos without entity sets and entity refs. (http://www.sidarok.com/web/blog/index.php)

    Here are the presentation slides : http://www.sidarok.com/files/linq2sqlms200.ppt here is the demo solutions dumped here : http://www.sidarok.com/files/presentationdemos.rar

    Hope this helps, please contact me if you have issues with it.

    Sidar Ok 24 September 2008
  14. Avatar for Jay

    Damien - thanks for today's update to the template. It resolved the issue I was having and worked perfectly to produce the generated LINQ-To-SQL types.

    Jay 24 September 2008
  15. Avatar for liviu

    Sidar, thank you for the links. I will take a look.

    Damien, the template unfortunately still doesn't work 100% compatible with the built in generator. I have that dbml that crashes the template but does produce correct output otherwise. I had to use SQlMetal for now .. :-(

    liviu 29 September 2008
  16. Avatar for Damien Guard

    Is this the encoding issue or something else?

    Damien Guard 29 September 2008
  17. Avatar for michaeln

    liviu,

    Are you going to make your assembly (to generate the dbml) available to download?

    michaeln 17 October 2008
  18. Avatar for Mike

    Thank you for this work - this has resolved a lot of issues for me.

    However, I have a class that is using InheritanceMapping and I notice that the Inheritance Default is not generated even though I have it set in the dbml, for example,

    [Table(Name=@"dbo.People")]
    [DataContract(IsReference=true)]
    [InheritanceMapping(Code="0", Type=typeof(Person), IsDefault=true)] //isDefault=true is not being generated here
    [InheritanceMapping(Code="1", Type=typeof(Engineer))]
    
    Mike 17 November 2008
  19. Avatar for Lucas

    Hi, sir. Love your templates, thanks for publishing them :)

    I made a few small changes to support the IsDelayLoaded attribute in the DBML file. All you need is to use Link instead of T for the column's private storage field, and use _storageField.Value instead of just _storageField in the getter and setter of the column's public property.

    I also added a bool IsDelayLoaded property to your Column class in the ttinclude file and got the value from the XElement like you did for the other attributes. Voila!

    Lucas 7 December 2008
  20. Avatar for Lucas

    Hi, thanks for including IsDelayLoaded. I got something else which seems to be a small bug. In EntitySet associations, the get accesor references a "serializing" variable which is never declared:

    get {
        if (serializing && !_DuplicatedPictures.HasLoadedOrAssignedValues) {
            return null;
    

    As soon as I remove it the generated code compiles and seems to work fine. ;)

    Lucas 10 December 2008
  21. Avatar for Peter

    The standard generation tool supports using an enum as the type of a database int field. A type load exception occurs if you try this with the template. This can be replicated by creating an enum and setting a fields type to that in the designer.

    I will give it a go and post the result if I make it happen, but would like to know if it has been done already.

    Thanks.

    Peter 12 December 2008
  22. Avatar for Steve

    Hi Damien,

    Should this template support SQL functions? I am getting an error when executing a method that calls a function:

    'System.Boolean' is not a valid return type for a mapped stored procedure method.

    Comparing the template generated cs with the original O/R designer generated code it looks like I am missing the IsComposable attribute. How easy would it be to modify the template to support functions?

    Thanks in advance for your help.

    Regards,

    Steve

    Steve 7 January 2009
  23. Avatar for BryceH

    First of all, I just wanted to say that this is great stuff. Thanks Damien!!!

    Secondly... I ran into an issue when generating separate class files. Since each class file was rebuilt no matter if there was truly a change my source control would always see them as modified files due to the timestamp on the file. So, I added some code to the L2ST4.ttinclude file, ManagementStrategy class, CreateFile method. The new code is below.

    internal virtual void CreateFile(String fileName, String content) {
        if (File.Exists(fileName)) {
            //get the date saved to the new version
            //note: 32 is the length of the Generated at comment plus the datetime info
            String newDate = content.Substring(content.IndexOf("Generated at"), 32);    //get the text of the existing file
            String existingText = File.ReadAllText(fileName);
            //get the date in the existing file
            String textToReplace = existingText.Substring(existingText.IndexOf("Generated at"), 32);
            //replace the date in the existing file with the new date
            existingText = existingText.Replace(textToReplace, newDate);
            //compare contents to determine if save is necessary
            if (existingText != content)
                File.WriteAllText(fileName, content);
        }
        else
            File.WriteAllText(fileName, content);
    }
    
    BryceH 31 March 2009
  24. Avatar for Fabricio

    Hi Damien,

    First of all, you've done a great job!

    I've just found an issue with a specific scenario that is causing your template to generate an invalid code. It seems that OOTB dbml code generator has an workaround to circumvent this issue.

    If you have a primary key of type uniqueidentifier, and define a default constraint of (new_id()) for it, when you drag the table from database explorer into dbml file, the AutoSync settings for the column will remain as ".Never" even if the DbAutoGenerated property is true. But, in the .designer.cs file, the column will not receive the AutoSync=AutoSync.Never attribute value. By using your template, the attribute value above will be included, and the following error will be displayed when you try to load data into DataContext with the offending configuration: "Incorrect AutoSync specification for member 'PKColumnName'".

    I've just changed the line 258 of your TT script as the following: if (column.AutoSync != AutoSync.Default && column.Type != typeof(System.Guid)) {#>, AutoSync=AutoSync.<#}

    this way, if the column has an AutoSync set, it will only generate the code if the column type isn't Guid.

    I hope my change will solve the problem, and I hope it is right. Please, let me know if I did anything stupid here. :)

    Regards, Fabrício

    Fabricio 13 April 2009