Databases
StackOverflow2013
Postsdbo
POLinqToSql DataContext with extracted interface doesn't compose SQL for functions
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLinqToSql DataContext with extracted interface doesn't compose SQL for functions
    primarykey
    data
    text
    <p>I have setup a test database and console app to confirm the following:</p> <p>Given a SQL Database with the following function:</p> <pre><code>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 </code></pre> <p>And the following table:</p> <pre><code>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] </code></pre> <p>The Scalar valued function ufn_GTFO is normally composable, such that the following C#:</p> <pre><code>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(); } </code></pre> <p>Is translated into the following SQL:</p> <pre><code>SELECT [t0].[PKey], [dbo].[ufn_GTFO]([t0].[WFT]) AS [GTFO] FROM [dbo].[Test] AS [t0] </code></pre> <p>However, if I use the refactor -> extract interface on the DataContext, and use an instance of that:</p> <pre><code>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(); } </code></pre> <p>I get the following SQL, and calls to ufn_GTFO occur once for each record as .ToArray() enumerates the results.</p> <pre><code>SELECT [t0].[PKey], [t0].[WFT] AS [guid] FROM [dbo].[Test] AS [t0] </code></pre> <p><strong>So, my question is why does this happen and what can I do to prevent it while still using the interface?</strong></p> <p><strong>Update 1:</strong> 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.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPeter T. LaComb Jr.
    1. USPeter T. LaComb Jr.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLinqToSql DataContext with extracted interface doesn't compose SQL for functions
      1. USPeter T. LaComb Jr.
      1. VTFavorite
    2. VO
      singulars
      1. POLinqToSql DataContext with extracted interface doesn't compose SQL for functions
      1. USPeter T. LaComb Jr.
      1. VTBountyStart
    3. VO
      singulars
      1. POLinqToSql DataContext with extracted interface doesn't compose SQL for functions
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI won't post an answer to this, as I don't know from a LINQ perspective, but I do know that SQL Server will run `ufn_GTFO` in the first query once for every row, as well.
      singulars
      1. POLinqToSql DataContext with extracted interface doesn't compose SQL for functions
      1. USEric
    2. CO@eric - you are correct, but running it locally vs. making another call per row is vastly different from a performance perspective.
      singulars
      1. POLinqToSql DataContext with extracted interface doesn't compose SQL for functions
      1. USPeter T. LaComb Jr.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload