[RESOLVED] Linq2SQL statements appending issue with temp tables

[i00]

This is prob a little hard to demonstrate in an example ...

but basically I am attaching extra statements to Linq2SQL database hits ...

the issue I am having is this...

I am creating a temp table in one fetch...

Dim temp = dbml.Staff.Where(Function(x) x.FirstName.Contains("K")).IntoTempTable
'This actually gets created when this is called - so it creates one db hit...
Dim asd = temp.Where(Function(x) x.FirstName.Contains("e")).ToArray()

... generates this sql:

SELECT * INTO [#TempTable0] FROM
(
    SELECT  [tstaff0].[prodno] AS [Prodno], [tstaff0].[FirstName] AS [FirstName]
    FROM [dbo].[staff] AS [tstaff0]
    WHERE [tstaff0].[FirstName] LIKE CAST(N'%K%' AS VarChar(8000))
) AS TEMPTABLE
SELECT [tstaff0].[prodno] AS [Prodno], [tstaff0].[FirstName] AS [FirstName]
FROM [#TempTable0]
WHERE ([tstaff0].[FirstName] LIKE '%e%')

.. and a third...

Dim asd2 = temp.Where(Function(x) x.FirstName.Contains("D")).ToArray()

... however this one errors as it tries to execute this sql:

SELECT [tstaff0].[prodno] AS [Prodno], [tstaff0].[FirstName] AS [FirstName]
FROM [#TempTable0]
WHERE ([tstaff0].[FirstName] LIKE '%D%')

... and states: "Invalid object name '#TempTable0'"

... the issue is that the temp table is not kept after the first statement... for some reason... I do not believe that Linq2SQL performs reads in transactions that are rolled back... so what is going on?

Thanks,
Kris

[PlausiblyDamp]

Is there any other code between the first query on the temp table and the second?

[jmcilhinney]

There's something fishy about that code. In the first snippet, you declare 'temp' and 'asd' while in the second you declare 'asd', which suggests that they are in different scope, but you use 'temp' which suggests that they are in the same scope. What gives?

[GaryMazzone]

I assume (an I know this is bad) that you are using SQL Server is so temp tables that start with # are only available on a single connection to the database if you run a statement and close or leave the connection the temp table is no longer available. You could try ## (global temp table, not something I like) of create a true table in the database temp_TableName and then when completed drop the table.

[i00]

There's something fishy about that code. In the first snippet, you declare 'temp' and 'asd' while in the second you declare 'asd', which suggests that they are in different scope, but you use 'temp' which suggests that they are in the same scope. What gives?

Whops ... I was testing putting the 2nd statement as the first statement just to double check that it worked (which it did) and must have ended copying that...

Is there any other code between the first query on the temp table and the second?

No there isn't the code looks like this:

Using dbml As New Database.DBML
    Dim temp = dbml.Staff.Where(Function(x) x.FirstName.Contains("K")).IntoTempTable
    Dim asd = temp.Where(Function(x) x.FirstName.Contains("e")).ToArray()
    'The below line throws the error:
    Dim asd2 = temp.Where(Function(x) x.FirstName.Contains("D")).ToArray()
    'do stuff here
    '...
End Using

I assume (an I know this is bad) that you are using SQL Server is so temp tables that start with # are only available on a single connection to the database if you run a statement and close or leave the connection the temp table is no longer available. You could try ## (global temp table, not something I like) of create a true table in the database temp_TableName and then when completed drop the table.

I know this and for all I can tell it should be the same connection ... isn't that how Linq2SQL works (in the case of the above snippet)?

## is not really a good option for me.

Kris

[i00]

Ok... just ran an SQL profiler on it ...

There appears to be:

Audit Login
{stuff}
Audit Logout

For each query when running any L2SQL statement...
So even if I go:

Using dbml As New Database.DBML
    Dim s1 = dbml.Staff.ToArray()
    Dim s2 = dbml.Staff.ToArray()
End Using

I will get:

Audit Login
{s1 stuff}
Audit Logout

Audit Login
{s2 stuff}
Audit Logout

Is there anyway to stop L2SQL doing this?

Thanks
Kris

[i00]

If I don't do it in a transaction scope (which I don't want to do anyway) it appears to get rid of the AuditLogins/Logouts between statements but strangely this still doesn't fix the issue???

What is going on???

Thanks,
Kris

[PlausiblyDamp]

Is this running as a windows app or a web application? I am wondering if connection pooling is possibly getting involved, although I can't see why it would if you are executing the statements that close together.

Is there anywhere that connection time outs or similar are being configured?

Other than that I can't think of anything, and those ideas are clutching at straws really

[GaryMazzone]

You are stuck you can't control that. I agree as a DBA I don't like the global temp table. The only other suggestion is create a physical temp table (maybe in TempDB) do the work and drop at the end

[i00]

Is this running as a windows app or a web application? I am wondering if connection pooling is possibly getting involved, although I can't see why it would if you are executing the statements that close together.

Is there anywhere that connection time outs or similar are being configured?

Other than that I can't think of anything, and those ideas are clutching at straws really

WinForms application ... not configuring anything that isn't defined in the connection string.

Kris

[PlausiblyDamp]

Out of interest where is the .IntoTempTable method coming from? It has been a while since I have used Linq2Sql but I don't remember that being part of the standard set of functions?

Do you have access to the source of the method?

[i00]

Out of interest where is the .IntoTempTable method coming from? It has been a while since I have used Linq2Sql but I don't remember that being part of the standard set of functions?

Do you have access to the source of the method?

It's not ... It is my method... I do have the source code but I can't share it sorry...

Hence why I said it would be "a little hard to demonstrate in an example"

Kris

[PlausiblyDamp]

No problem, I completely understand.

Given the rest of the code looks fairly normal linq I can only suggest investigating that method and seeing if it causes the problem, it could be down to some weird linq related issue.

[i00]

No problem, I completely understand.

Given the rest of the code looks fairly normal linq I can only suggest investigating that method and seeing if it causes the problem, it could be down to some weird linq related issue.

It's code I am doing for my day job so I will ask my manager what I can post on here... Hopefully it will be enough to build up a test case and get some answers...

Kris

[i00]

I have finally gotten around to creating a test project...

Which you can get here:
Attachment 145443

This will create a TestStuff database on .\SQLExpress ... If your SQL server instance is something else you can change it by editing the DataSource Const in Form1.

Hit the "Start Test" button to test and watch it fail.

Thanks in advance,
Kris

[PlausiblyDamp]

Stepping through the code it looks like the code ends up calling the SqlProvider.Execute method (https://referencesource.microsoft.co...e9d902d6d122f6) as part of this it calls into SqlConnectionManger.UseConnection (https://referencesource.microsoft.co...9a6f21a01f4b82) which sets autoclose to true, I sort of made an educated guess that this is what resulted in the connection logging out...

Digging through the DataContext source it seems that if you create an instance of context using a connection string then it will instantiate a connection object for you and will take care of opening and closing it for you.

If you change how you use the TestStuff to

 
Using con As New SqlClient.SqlConnection(ConnectionString)
    con.Open()
    Using DBML = New TestStuff(con)
         Dim ExtraCommands As New List(Of String)

and create it by passing in an open connection then you are responsible for also closing the connection, it looks like the DataContext leaves it alone, wrapping it in the Using block just makes this easier. Running the app now doesn't throw the exception, hope the stuff you see on screen is what you were expecting to see.

[i00]

Stepping through the code it looks like the code ends up calling the SqlProvider.Execute method (https://referencesource.microsoft.co...e9d902d6d122f6) as part of this it calls into SqlConnectionManger.UseConnection (https://referencesource.microsoft.co...9a6f21a01f4b82) which sets autoclose to true, I sort of made an educated guess that this is what resulted in the connection logging out...

Digging through the DataContext source it seems that if you create an instance of context using a connection string then it will instantiate a connection object for you and will take care of opening and closing it for you.

If you change how you use the TestStuff to

 
Using con As New SqlClient.SqlConnection(ConnectionString)
    con.Open()
    Using DBML = New TestStuff(con)
         Dim ExtraCommands As New List(Of String)

and create it by passing in an open connection then you are responsible for also closing the connection, it looks like the DataContext leaves it alone, wrapping it in the Using block just makes this easier. Running the app now doesn't throw the exception, hope the stuff you see on screen is what you were expecting to see.

Thanks soooo much ... works like a treat

Kris