1

I have the following situation: i need to create some temporary tables to optimize a load problem the recently has ocurred. It seems that LINQ to SQL doesn't work well with temporary table, unless they are mapped on the DBML. I, honestly, still don't understand how scope works on LINQ to SQL. With that in mind i went to define every temporary table on the DBML.

But, as always, things can't be that easy. I can't define on compilation time (which is what linq needs) what name my temporary table will have, because it will be defined when an user logs on the system. To add more: i will have several of these dynamic temporary table, so there's no way i can map it all to the DBML.

When i tried to create my temporary tables through executeCommand, select its results and cast it to strong type (TempTableDefinition). However, when i tried to insert values on this new created temporary table i got a SQLException saying 'Invalid object name #NewTempTable' (this was the same name i used to create the table).

It appears that i will have to use pure old plain ADO.NET to create every temporary table and map it's properties to a strong typed object (i prefer this approach). I really wouldn't like to mix ADO.NET with LINQ, since i just read that it's a bad ideia. Plus, i prefer linq approach of strong type objects to the ADO.NET way.

Resume: So, do you know or is it even possible to create dynamic temporary tables that linq to sql can work with? I can't define it's name on compilation time, only on execution time. Any tips will be appreciated.

Yusubov
  • 5,815
  • 9
  • 32
  • 69
AdrianoRR
  • 1,131
  • 1
  • 17
  • 36
  • Linq to Sql is not a comprehensive solution. Like any other ORM, it covers about 80% of what you might want to do with a database. The remaining 20% is better served by other techniques. – Robert Harvey Dec 07 '12 at 19:28
  • @Robert so, would you suggest me to use the plain old ADO.Net to do it? I can't think on any other solution, other than it. – AdrianoRR Dec 07 '12 at 19:31

2 Answers2

1

The problem seems to be that L2S by default opens and closes the connection for each logical request. That kills your temp tables.

Either open the connection manually (and close it, of course) or wrap everything in a TransactionScope which integrates with L2S and keeps the connection open.

usr
  • 168,620
  • 35
  • 240
  • 369
  • Are you sure - every submitchanges is already wrapped in a single transaction and usually you need only one SubmitChanges per logical request. – Pleun Dec 07 '12 at 21:45
  • I'm sure, yes, but I'm not sure what you mean. Do you mean that all your submits are wrapped in a transaction or that each submit is wrapped in a new tran? The latter case would waste your temp tables.; "Logical request" was confusing. What I mean by that is either a query or a submit. – usr Dec 07 '12 at 21:48
  • When you call SubmitChanges, LINQ to SQL checks to see whether the call is in the scope of a Transaction or if the Transaction property (IDbTransaction) is set to a user-started local transaction. If it finds neither transaction, LINQ to SQL starts a local transaction (IDbTransaction) and uses it to execute the generated SQL commands. http://stackoverflow.com/q/542525/532498 – Pleun Dec 07 '12 at 21:49
  • @Pleun, I agree that is true. My point is that LINQ closes the connection if no user transaction is present. That destroys the temp table. If a user tran is present (detected through Transaction.Current) the connection is kept open. – usr Dec 07 '12 at 21:51
  • @usr However, once i a close this transaction, will i still be able to retrieve my temp table later? I need this temp table to be avaliable to some insert after its creation. But i guess it will be gone, right? – AdrianoRR Dec 13 '12 at 18:49
  • The tran in independent of the temp table. If you manually open the connection before starting the tran, the temp table will stay alive as long as the connection lives. So it will work this way. – usr Dec 14 '12 at 10:49
0

to optimize a load problem

Linq-2-sql and batch/bulk will anyway not work together. Every insert/update/delete will result in a single statement (ok, one transaction but still). For hardcore performance, avoid Linq-2-sql and once you have your data in, use Linq with all the advantages like strong typing etc.

Pleun
  • 8,856
  • 2
  • 30
  • 50
  • Unfortunately it is not i who works with the database. I have several workarounds to work with a not normalized DB. The load problem is not 'per si' the greatest issue. But i understood your suggestion to not use linq-to-sql in this case. However since it is my default ORM, i just wanted to know it there was a possibility. – AdrianoRR Dec 07 '12 at 19:43
  • Have you checked out SqlBulkCopy - it is very fast and not that hard to implement – Pleun Dec 07 '12 at 21:54