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.Retu rnValue));
}

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?

Whops, maybe I should just avoid wrapping the UDF's into SP. Some articles I
found googling around about CLR UDF had given me the impression I should do
this, but I suppose this is not the right thing to do in this case...

Hi Tekton,

Calling SP in Linq to SQL is definitely supported, the problem here should
be specific to the case that you've mixed CLR UDF into the query. Here are
some articles provide some info about customize or extend the LINQ query
expression or call SP in LINQ query:

#LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)
http://weblogs.asp.net/scottgu/archi...part-6-retriev
ing-data-using-stored-procedures.aspx

#LINQ to SQL (Part 8 - Executing Custom SQL Expressions)
http://weblogs.asp.net/scottgu/archi...part-8-executi
ng-custom-sql-expressions.aspx

You can also have a look to see whether you can get any ideas.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.


--------------------

When you bounce back and forth from SQL to managed code, you can create some
problems. When you exacerbate it with LINQ, you are almost guaranteed to run
into issues. If you want to see what is actually going on, you can look at
the query produced by LINQ by stopping on a breakpoint. One of th LINQ
videos also has a demo where they show how to output this information so you
can go through it. You can then run that query and see what is happening and
get a better understanding of what you can and can't do easily with LINQ in
conjunction with SQL CLR and sprocs.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
"Tekton" <tekton (AT) community (DOT) nospam> wrote