LINQ to SQL template for Visual Studio 2008

24 responses to LINQ to SQL template for Visual Studio 2008

1. 

   Information is only used to show your comment. See my Privacy Policy.

2. 

   Fabricio13/04/2009

   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

3. 

   BryceH31/03/2009

   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);
   }
   

4. 

   Steve07/01/2009

   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

5. 

   Peter12/12/2008

   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.

6. 

   Lucas10/12/2008

   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. ;)

7. 

   Lucas07/12/2008

   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!

8. 

   Mike17/11/2008

   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))]
   

9. 

   michaeln17/10/2008

   liviu,

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

10. 

    Damien Guard29/09/2008

    Is this the encoding issue or something else?

11. 

    liviu29/09/2008

    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 .. :-(

12. 

    Jay24/09/2008

    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.

13. 

    Sidar Ok24/09/2008

    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.

14. 

    Steele23/09/2008

    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.

15. 

    liviu23/09/2008

    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…

16. 

    Sidar Ok22/09/2008

    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

17. 

    liviu18/09/2008

    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…

18. 

    liviu18/09/2008

    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.

19. 

    Damien Guard18/09/2008

    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.

20. 

    liviu18/09/2008

    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?

21. 

    liviu17/09/2008

    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 ;)

22. 

    Damien Guard17/09/2008

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

23. 

    Carlos Beppler17/09/2008

    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.

24. 

    Damien Guard17/09/2008

    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 :)

25. 

    liviu17/09/2008

    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.