Calling a UDF in LINQ without "dbo" database user

Question

Hi every one!

I am using LINQ to call a function named "GetTabMenuTheme", I dragged it in the Database Model to generate the function like this:

[Function(Name="dbo.GetTabMenuTheme", IsComposable=true)]
public string GetTabMenuTheme([Parameter(DbType="NVarChar(MAX)")] string state)
{
    return ((string)(this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), state).ReturnValue));
}

and use it like this:

from mi in db.ModuleInstances
select db.GetTabMenuTheme(mi.State)

and my connection is:

Data Source=MyServer;Initial Catalog=MyDB;uid=MyUser;pwd=MyPassword

There is no problem executing the code locally (I mean with connection: Data Source=.;Initial Catalog=MyDB;Integrated Security=True;), but when I deploy in web, this error is produced:

Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetTabMenuTheme", or the name is ambiguous.

Answer 1

Edited after comment: You can fall back to non-LINQ database interaction:

var com = db.Connection.CreateCommand();
com.CommandText = "select MyUser.GetTabMenuTheme(@state);";
com.Parameters.Add(new SqlParameter("@state", state));
var result = (string) com.ExecuteScalar();

This allows you to choose the schema, it's MyUser in the code sample.

I've searched for a way to dynamically change the LINQ schema name, and this blog post describes it exactly.

Well, it’s not as easy as it seams because the table name is stored in an attribute of the partial class generated by the LINQ to SQL designer and Microsoft didn’t provide a way or method to change it at runtime.

The blogger then describes a solution where you load an XML specifying the schema at runtime. It looks very complex and slow; I'd go with the simpler non-LINQ option.

Answer 2

I guess you have different default schema for your local database and for remote one. According to your local connection string, you're working under local administrator account which has a default schema set to dbo. Meanwhile you connect to your remote database using SQL Server authentication and (I guess) default schema for this user is different from dbo. You should either:

• change default schema of MyUser to dbo (in case you're sure that you've deployed your function to dbo schema)

• change target schema in your local database (so it is the same as in your 'production' server). This option will involve changing you (local) deployment procedure a bit + modifying function definition in your model (sample follows)

  ([Function(Name="YourSchemaNameHere.GetTabMenuTheme", IsComposable=true)] public string GetTabMenuTheme([Parameter(DbType="NVarChar(MAX)")] string state) { return ((string)(this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), state).ReturnValue)); }

Answer 3

Or you can build a map file with SqlMetal and load it dinamically. Before installing the CMS you can run a tool that change the schema name of the tables inside the map. Take a look there: http://www.techwave.it/blog/2010/01/28/linq-to-sql-modificare-dinamicamente-il-mapping-del-database-schema-2/

(it's in Italian but you can understand reading the code)