T
Tekton
Hi group, I've a question about calling stored procedures in LINQ queries.
Here's my scenario: I have some CLR user-defined functions in a SQL Server
2005 database for custom string matching, e.g. using regular expressions. Say
one of them is called RegexMatch and accepts 3 parameters: the text to match,
the expression pattern, and a boolean telling if the regex is to be compiled
or not. In SQL I can succesfully call my function like this:
SELECT dbo.RegexMatch('word', 'someregexhere', 0)
Now I'd like to make calls to these UDF's in my LINQ to SQL queries, so that
when LINQ translates the query expression into SQL it can invoke them (of
course I could not insert a Regex match call in the LINQ code as it will be
impossible to translate it into SQL statements). To this end I did the
following:
1) I create several stored procedures wrapping each UDF, like:
CREATE PROCEDURE dbo.RegexMatchWord
(
@word NVARCHAR(100),
@pattern VARCHAR(100),
@compiled BIT
)
AS
SET NOCOUNT ON
SELECT dbo.RegexMatch(@word, @pattern, @compiled);
2) in my DBML I simply drag the stored procedures from the database server
pane into the designer area containing SP. VS automatically generates for me
the corresponding methods, like:
[Function(Name="dbo.RegexMatchWord")]
public ISingleResult<RegexMatchWordResult>
RegexMatchWord([Parameter(DbType="NVarChar(100)")] string word,
[Parameter(DbType="VarChar(100)")] string pattern, [Parameter(DbType="Bit")]
System.Nullable<bool> compiled)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), word, pattern, compiled);
return ((ISingleResult<RegexMatchWordResult>)(result.ReturnValue));
}
3) in my LINQ query code I invoke such SP like (say dc is my data context):
var result = from e in dc.SomeEntity
where dc.RegexMatchWord(e.word, "a regular expression here",
false).First().Column1.Value == true
select e;
The code compiles but when it's executed I get the exception "Stored
Procedures cannot be used inside queries". What's going on here? How can I
use such UDF's in my LINQ queries?
Here's my scenario: I have some CLR user-defined functions in a SQL Server
2005 database for custom string matching, e.g. using regular expressions. Say
one of them is called RegexMatch and accepts 3 parameters: the text to match,
the expression pattern, and a boolean telling if the regex is to be compiled
or not. In SQL I can succesfully call my function like this:
SELECT dbo.RegexMatch('word', 'someregexhere', 0)
Now I'd like to make calls to these UDF's in my LINQ to SQL queries, so that
when LINQ translates the query expression into SQL it can invoke them (of
course I could not insert a Regex match call in the LINQ code as it will be
impossible to translate it into SQL statements). To this end I did the
following:
1) I create several stored procedures wrapping each UDF, like:
CREATE PROCEDURE dbo.RegexMatchWord
(
@word NVARCHAR(100),
@pattern VARCHAR(100),
@compiled BIT
)
AS
SET NOCOUNT ON
SELECT dbo.RegexMatch(@word, @pattern, @compiled);
2) in my DBML I simply drag the stored procedures from the database server
pane into the designer area containing SP. VS automatically generates for me
the corresponding methods, like:
[Function(Name="dbo.RegexMatchWord")]
public ISingleResult<RegexMatchWordResult>
RegexMatchWord([Parameter(DbType="NVarChar(100)")] string word,
[Parameter(DbType="VarChar(100)")] string pattern, [Parameter(DbType="Bit")]
System.Nullable<bool> compiled)
{
IExecuteResult result = this.ExecuteMethodCall(this,
((MethodInfo)(MethodInfo.GetCurrentMethod())), word, pattern, compiled);
return ((ISingleResult<RegexMatchWordResult>)(result.ReturnValue));
}
3) in my LINQ query code I invoke such SP like (say dc is my data context):
var result = from e in dc.SomeEntity
where dc.RegexMatchWord(e.word, "a regular expression here",
false).First().Column1.Value == true
select e;
The code compiles but when it's executed I get the exception "Stored
Procedures cannot be used inside queries". What's going on here? How can I
use such UDF's in my LINQ queries?