There are three problems with using LINQ to SQL with DotNetNuke:
Problem 1: You have to add keys to the web.config
Currently you have to alter the DotNetNuke web.config to use any code that uses LINQ to SQL. (see the end of this article: http://www.adefwebserver.com/DotNetNukeHELP/Blogs/Linq_FirstLook.htm for a list of the changes required).
Problem 2 & 3: LINQ to SQL is unable to properly handle the {databaseOwner} and the {objectQualifier} features.
Normally the DotNetNuke module installation scripts are written like this:
CREATE TABLE {databaseOwner}[{objectQualifier}ThingsForSale]
The script commands "{databaseOwner}" and "{objectQualifier}" indicate that they are to be replaced by configuration settings in the web.config file. Normally "{databaseOwner}" is set to ".dbo" and "{objectQualifier}" is set to nothing (it would not have a setting). However, if alternate settings were indicated in the web.config file, those settings would be inserted into the script at the appropriate point. If the object qualifier were “web1” then the table created would be named:
“dbo.web1_ThingsForSale”
If this happens LINQ to SQL will not be able to find the table because it woud be looking for:
“dbo.ThingsForSale”
The solution that did not work: Instructing LINQ to SQL to look for the proper name at run-time.
LINQ to SQL is very extensible. It stores the database schema in a DataContext class file (a file with the extension .designer that inherits from “System.Data.Linq.DataContext”) . This file contains methods that you can implement in a partial class to add functionality. One method is:
partial void OnCreated();
I created a method like this:
public partial class MessagesDataContext
{
partial void OnCreated()
{
Table
objThingsForSale = (Table
)this.GetTable
();
}
}
And I was able to set a break point and see that it was indeed called when the DataContext was created (this DataContext is then used to retrieve data from the database and update it using LINQ). The problem is you can not tell it that “dbo.ThingsForSale” Is now “dbo.web1_ThingsForSale”. You have to make the change before LINQ to SQL gets to this point.
However, changing the table attribute in the .designer file from “dbo.ThingsForSale” to “dbo.web1_ThingsForSale” will allow LINQ to SQL to find the table properly.
The solution:
A module needs to be created, let’s call it the “installer”, that when installed will create the needed keys in the web.config (or not if they already exist). The module needs to first ensure that ASP.NET 3.5 is installed first (see: Module Installer - Permissions and Dependencies (http://support.dotnetnuke.com/issue/ViewIssue.aspx?id=5232) for how this can be done).
Next this “installer” module can be used to install any LINQ to SQL DotNetNuke modules. This module will read the {databaseOwner} and the {objectQualifier} in the web.config and rename the table names in the .designer file.
Why I won’t be working on this:
The reason why I am writing a blog about this and not creating the module is that I don’t need it. We don’t use the {databaseOwner} and the {objectQualifier} at my job and we simply added the keys needed to the web.config. We are happily using LINQ to SQL in our DotNetNuke modules and we couldn’t be happier.
It’s the module developers who are selling modules who could use a method such as this. They could tell their customers to upload the first “installer” module and then use it to upload the main module. The first module, the “installer” should be Open Source and made available to everyone.
When and if a Core solution/change is made available nothing should “break”.
Just an idea.