-
Feb 21st, 2017, 02:08 AM
#1
[RESOLVED] Linq2SQL statements appending issue with temp tables
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...
VB.Net Code:
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:
SQL Code:
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...
VB.Net Code:
Dim asd2 = temp.Where(Function(x) x.FirstName.Contains("D")).ToArray()
... however this one errors as it tries to execute this sql:
SQL Code:
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
Last edited by i00; Feb 21st, 2017 at 06:13 PM.
-
Feb 21st, 2017, 05:26 AM
#2
Re: Linq2SQL statements appending issue with temp tables
Is there any other code between the first query on the temp table and the second?
-
Feb 21st, 2017, 07:03 AM
#3
Re: Linq2SQL statements appending issue with temp tables
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?
-
Feb 21st, 2017, 08:30 AM
#4
Re: Linq2SQL statements appending issue with temp tables
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.
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Feb 21st, 2017, 06:19 PM
#5
Re: Linq2SQL statements appending issue with temp tables
Originally Posted by 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?
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...
Originally Posted by PlausiblyDamp
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:
VB.Net Code:
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
Originally Posted by 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.
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
Last edited by i00; Feb 21st, 2017 at 11:17 PM.
-
Feb 21st, 2017, 06:28 PM
#6
Re: Linq2SQL statements appending issue with temp tables
Ok... just ran an SQL profiler on it ...
There appears to be:
Code:
Audit Login
{stuff}
Audit Logout
For each query when running any L2SQL statement...
So even if I go:
VB.Net Code:
Using dbml As New Database.DBML Dim s1 = dbml.Staff.ToArray() Dim s2 = dbml.Staff.ToArray() End Using
I will get:
Code:
Audit Login
{s1 stuff}
Audit Logout
Audit Login
{s2 stuff}
Audit Logout
Is there anyway to stop L2SQL doing this?
Thanks
Kris
-
Feb 21st, 2017, 06:56 PM
#7
Re: Linq2SQL statements appending issue with temp tables
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
-
Feb 22nd, 2017, 07:58 AM
#8
Re: Linq2SQL statements appending issue with temp tables
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
-
Feb 22nd, 2017, 08:15 AM
#9
Re: Linq2SQL statements appending issue with temp tables
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
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Feb 24th, 2017, 03:38 AM
#10
Re: Linq2SQL statements appending issue with temp tables
Originally Posted by 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
WinForms application ... not configuring anything that isn't defined in the connection string.
Kris
-
Feb 24th, 2017, 06:29 AM
#11
Re: Linq2SQL statements appending issue with temp tables
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?
-
Feb 24th, 2017, 09:03 PM
#12
Re: Linq2SQL statements appending issue with temp tables
Originally Posted by 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?
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
-
Feb 25th, 2017, 05:42 AM
#13
Re: Linq2SQL statements appending issue with temp tables
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.
-
Feb 25th, 2017, 07:55 AM
#14
Re: Linq2SQL statements appending issue with temp tables
Originally Posted by 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.
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
-
Feb 28th, 2017, 10:59 PM
#15
Re: Linq2SQL statements appending issue with temp tables
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
-
Mar 1st, 2017, 07:20 PM
#16
Re: Linq2SQL statements appending issue with temp tables
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
Code:
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.
-
Mar 2nd, 2017, 03:57 AM
#17
Re: Linq2SQL statements appending issue with temp tables
Originally Posted by 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
Code:
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|