Why can't recursive queries contain...?
in [Programming SQL]
|
Prev: Sql Server on VMWare
Next: AdventureWorks database
From: Michael C on 4 Dec 2009 01:02 "Michael Coles" <admin(a)geocodenet.com> wrote in message news:e77OO7JdKHA.2188(a)TK2MSFTNGP04.phx.gbl... >I see you're using an ADO.NET SqlCommand and .Executing it your example >below, but I'm at a loss as to what this has to do with using Table-Valued >*Parameters* with LINQ? You're assigning the results of some function that >appears to return the results of a LINQ query as a parameter to an ADO.NET >SqlCommand. As Erland pointed out, TVPs work with ADO.NET. That's my point, any limitation is due to the code generator that generates linq to sql, it's not a limitation of linq to objects. Whether you use linq to objects or linq to sql it's going to come down to calling a command object. Michael
From: Michael Coles on 4 Dec 2009 11:11 "Michael C" <mike(a)nospam.com> wrote in message news:uEYcpdKdKHA.1156(a)TK2MSFTNGP05.phx.gbl... > "Michael Coles" <admin(a)geocodenet.com> wrote in message > news:e77OO7JdKHA.2188(a)TK2MSFTNGP04.phx.gbl... >>I see you're using an ADO.NET SqlCommand and .Executing it your example >>below, but I'm at a loss as to what this has to do with using Table-Valued >>*Parameters* with LINQ? You're assigning the results of some function >>that appears to return the results of a LINQ query as a parameter to an >>ADO.NET SqlCommand. As Erland pointed out, TVPs work with ADO.NET. > > That's my point, any limitation is due to the code generator that > generates linq to sql, it's not a limitation of linq to objects. Whether > you use linq to objects or linq to sql it's going to come down to calling > a command object. I'm not understanding how your example proves your point. Your example proves (assuming a few things about your code, that is) that ADO.NET can handle TVPs, which is what Erland has already stated a few times. Is the point you're trying to make that LINQ to Objects does support SQL Server Table-Valued Parameters? I'd love to see that code sample sometime! The "code generator" is not the *only* limitation. You can generate all the code in the world, incorporating all kinds of features, but it does you no good if the guts of your provider can't take advantage to make the new features actually work. This is a provider limitation. The LINQ to SQL provider, and all LINQ providers that I know of, do not have the capability to handle SqlDbType.Structured data. If it were as simple a change as you speculate, LINQ to SQL TVP support would probably be scheduled for .NET 4.0. In fact, the only change that appears to be on the radar for LINQ TVPs in .NET 4.0 is that SQL Metal will simply skip/ignore stored procs that use TVPs instead of aborting processing.
From: Michael Coles on 4 Dec 2009 11:58 Come to think of it, and I have to ask this based on your comments in this thread, do you actually know what a "Table-Valued Parameter" is? "Michael C" <mike(a)nospam.com> wrote in message news:uEYcpdKdKHA.1156(a)TK2MSFTNGP05.phx.gbl... > "Michael Coles" <admin(a)geocodenet.com> wrote in message > news:e77OO7JdKHA.2188(a)TK2MSFTNGP04.phx.gbl... >>I see you're using an ADO.NET SqlCommand and .Executing it your example >>below, but I'm at a loss as to what this has to do with using Table-Valued >>*Parameters* with LINQ? You're assigning the results of some function >>that appears to return the results of a LINQ query as a parameter to an >>ADO.NET SqlCommand. As Erland pointed out, TVPs work with ADO.NET. > > That's my point, any limitation is due to the code generator that > generates linq to sql, it's not a limitation of linq to objects. Whether > you use linq to objects or linq to sql it's going to come down to calling > a command object. > > Michael >
From: Erland Sommarskog on 5 Dec 2009 05:39 Erland Sommarskog (esquel(a)sommarskog.se) writes: > And there is a difference. Consider this script: > > declare @g varchar(MAX) = '' > declare @x varchar(MAX) = replicate(convert(varchar(MAX), 'K'), 1000000) > declare @i int = 1500 > while @i > 0 > select @g += @x, @i -= 1 > select datalength(@g) > > I'm running this on a virtual machine with 516 MB of memory and some > 786 MB of pagefile. How do think it ends? > > As a clue, it has been running for soon four hours, and tempdb has > expanded from 8 MB to almost 1GB. We don't know yet, but I'm optimistic > that the script will complete successfully. Indeed it did! It took 77 hours, but it finally printed 1500000000. I was getting worried, because tempdb grew a lot more than I expected. The final size is just over 3 GB, with 683 MB to spare on the disk. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Erland Sommarskog on 5 Dec 2009 06:26
Michael C (mike(a)nospam.com) writes: >> All you have been able to produce is that it works, but you have >> completely refused to make any effort to prove it. > > No, I have provided sample code. Did you try my sample code and prove it > does not work? Permit me to point out that in the programming trade, nothing is proven to work until code is written and tested. Later in the thread, you can this sample: SqlCommand command = CodeToCreateMyCommand(); command.parameters["MyTableVariable"].Value = ResultsFromMyLinqQuery(); command.Execute(); This sample is not likely to work of the simple reason that you fail to set the parameter type. You also need to make sure that ResultsFromMyLinqQuery returns either a DataSet, a List<SqlDataRecord> or an IDataReader. Of course, all these are no-brainers. But the more important point is that is not what I've been talking about. As Michael Coles have pointed out, I'm talking about calling a stored procedure through LINQ (to SQL or EF) and passing a TVP. > Even though linq does do this, *if* the limitation did exist it is not > silly like many of the limitations in sqlserver. C# does have > limitations but they are not as silly as those in sqlserver. Don't you > think an 8k limit is the most rediculous thing you've every heard of in > 2005? C# just doesn't have limitations that silly. My point was never > that C# didn't have limitations, just that they weren't as silly as > those in sqlserver. Of course what is silly is in the eye of the beholder. Limitations can exist of various reasons. One really "silly" restriction in SQL Server Management Studio is that it does not support connection to SQL 7. I'm fairly sure this was possible in early betas. So why was it cut? Maybe they found some pieces that did not work. Or they simply ran out of time to test it. Since it was not deemed to be high priority, they cut it. People who still have SQL 7 in their shops would of course disagree. But limitations can also exist because of technical hurdles. When it comes to LINQ to SQL, we should keep in mind that VS 2008 shipped before SQL 2008, so it is not strange that they did not support a future feature (although, the feature was fully known at at the time). But if I understand Michael Coles correctly, there are deeper problems than just add a few lines to the code genereator. Silly or not? Well, what in my opinion think is silly is to tout LINQ as a the right way to call SQL Server, when this means that you cannot use all capabilities in SQL Server. Of course, EF may be good if you need to be portable and support multiple platforms. In such a scenario you don't have interest in using proprietary features. But using LINQ to SQL or EF when you only have the intention against SQL Server? I would advice against that, because you will lose to much of the power in SQL Server. As for the 8k limitations in SQL Server, I have explained a number of times that SQL Server distinguishes between small objects and big objects, as distinction which is unknown in C#. C# does not impose any limits, but the OS will tell you when you have hit the ceiling. This is certianly permissible for C#, because many C# programs are single-user programs, and it can permit the OS to handle memory and spilling to disk. However, this is not permissible for something that always runs in a server context with multiple users. The 8K limitations I can think of on the top of my head, SQL 2005 are for CLR UDTs, user-defined aggregates and the sql_variant data type. In SQL 2008, it applies only to sql_variant. Whether it was silly to limit UDTs and UDAs to 8K in SQL 2005 can be disputed. Certainly for UDAs it's quite a limitation. But keep in mind that it was the 1.0 of the CLR in SQL Server, and they had to ship at some point. And certainly it was better to ship with UDAs limited to 8K, than no UDAs at all? You thought yourself that it was OK to ship C# 1.0 without generics, which in an object-oriented perspective, it's a far bigger handicap than the 8K limitation for UDAs, as this is a core feature for an object-oriented language. After all, in all the years SQL 2005 have been available, I've written one user-defined aggregate, and I was able to work around the 8K limitation without too much pain. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |