I have setup a test database and console app to confirm the following:
Given a SQL Database with the following function:
CREATE FUNCTION ufn_GTFO
(
@Guid as uniqueidentifier
)
RETURNS VARCHAR(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result as VARCHAR(100)
-- Add the T-SQL statements to compute the return value here
SELECT @Result = 'This is a test'
-- Return the result of the function
RETURN @Result
END
GO
And the following table:
CREATE TABLE [dbo].[Test](
[PKey] [int] IDENTITY(1,1) NOT NULL,
[WFT] [uniqueidentifier] NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[PKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The Scalar valued function ufn_GTFO is normally composable, such that the following C#:
static void ConcreteTest()
{
DataClasses1DataContext db = new DataClasses1DataContext();
var q = from t in db.Tests
select new { t.PKey, GTFO = db.ufn_GTFO(t.WFT) };
var p = q.ToArray();
}
Is translated into the following SQL:
SELECT [t0].[PKey], [dbo].[ufn_GTFO]([t0].[WFT]) AS [GTFO] FROM [dbo].[Test] AS [t0]
However, if I use the refactor -> extract interface on the DataContext, and use an instance of that:
static void InterfaceTest()
{
IDataClasses1DataContext db = new DataClasses1DataContext();
var q = from t in db.Tests
select new { t.PKey, GTFO = db.ufn_GTFO(t.WFT) };
var p = q.ToArray();
}
I get the following SQL, and calls to ufn_GTFO occur once for each record as .ToArray() enumerates the results.
SELECT [t0].[PKey], [t0].[WFT] AS [guid]
FROM [dbo].[Test] AS [t0]
So, my question is why does this happen and what can I do to prevent it while still using the interface?
Update 1: I've compared the IL generated for the concrete method versus the interface method, and they differ only in the reference to the interface and a compiler generated display class that doesn't seem to have any bearing on the result.
ufn_GTFOin the first query once for every row, as well.