"Could not translate expression " Error trying to insert using LINQ to SQL classes with custom SP

Question

Using LINQ to SQL classes, and the following code:

clindt.clinDataContext ctx = new clindt.clinDataContext();

clindt.RoleMembership rm = new clindt.RoleMembership();
rm.Created_By ="bob";
rm.Modified_By ="bob";
rm.ProfileID = 2;
rm.RoleID=1;
rm.Created_Date = DateTime.Now;
rm.Modified_Date = DateTime.Now;

ctx.RoleMemberships.InsertOnSubmit(rm);
ctx.SubmitChanges();

I get this error:

"Could not translate expression 'value(clindt.clinDataContext).p_InsertRoleMembership(0, 2, 1, 11/10/2010 2:33:38 PM, "bob", 11/10/2010 2:33:38 PM, "bob")' into SQL and could not treat it as a local expression."

Some additional info: The classes are configured to use a custom stored procedure for inserts. This error occurs with only 3 of my tables. Interestingly, they are all in a schema named 'Security'. Every other table is in 'dbo' schema.

Answer 1

I've seen this behavior when you try to add a stored procedure and the designer cannot determine the return type, which generates a message like this:

"The return types for the following stored procedures could not be detected..."

if you look at the generated DBML you will see the generated signature:

  <Return Type="object" />
</Function>

Later on this signature causes the runtime error you are talking about.

You can fix the problem by editing the .dbml file: just change the signature to the correct return type (typically Int).

  <Return Type="System.Int32" />
</Function>

Then you have to rebuild to re-regenerate the other files.

Or if you prefer the designer do the following:

• Remove the procedure from the Dbml, Otherwise you are not going to be able to change the Return Type.

• Add the stored procedure and ignore the error "The return types ...".

• Go to the stored procedure (Properties | Return Type) and change the type from Empty to your type, in my case was Int. Choose 'Yes' when the message "This operation cannot be undone ..." pops up. The return will show up as 'None' but internally it will generate an Int return type (you can check the.cs file).

• Rebuild the project.

You should be all set now.