在SQL Server和.NET代码+ LINQ之间处理非常大的字符串

I have an app that needs to handle very large strings between a SQL Server database and .NET code. I have a LINQ query that generates the strings when saving them to the database, but when trying to create the strings from the database, the app crashes with an OutOfMemoryException because of the size of the strings.

我有一个应用程序需要处理SQL Server数据库和.NET代码之间的非常大的字符串。我有一个LINQ查询,在将它们保存到数据库时生成字符串,但是当尝试从数据库创建字符串时,由于字符串的大小,应用程序因OutOfMemoryException而崩溃。

Do I have to do something to make the LINQ generated code avoid that? Using some kind of compression might be an option, but would like to avoid that for performance reasons.

我是否必须做一些事情来使LINQ生成的代码避免这种情况?使用某种压缩可能是一种选择,但出于性能原因,我们希望避免这种情况。

2 个解决方案

#1

What do you call "very large"? And what is the string? CLOB? BLOB? xml?

你叫什么“非常大”?什么是字符串? CLOB? BLOB? XML?

I suspect you should be using things like ExecuteReader(), which (via IDataReader) exposes methods for reading such columns in chunks:

我怀疑你应该使用像ExecuteReader()这样的东西,它(通过IDataReader)公开了以块的形式读取这些列的方法:

        using (var reader = cmd.ExecuteReader(
            CommandBehavior.SequentialAccess)) {
            char[] buffer = new char[8040]; // or some multiple (sql server page size)
            while (reader.Read()) {
                long dataOffset = 0, read;
                while((read = reader.GetChars(colIndex, dataOffset, buffer, 0, buffer.Length)) > 0) {
                    // process "read"-many chars from "buffer"
                    dataOffset += read;
                }
            }
        }

Obviously with xml you might want an XmlReader via cmd.ExecuteXmlReader().

显然,使用xml,您可能需要通过cmd.ExecuteXmlReader()获取XmlReader。

Updated re LINQ comment (now deleted):

更新了重新LINQ评论(现已删除):

To use IDataReader directly from LINQ-to-SQL, I expect the closest you can get is ctx.GetCommand(), passing it a query. You would then use ExecuteReader or ExecuteXmlReader as above. I don't know much about EF...

要直接从LINQ-to-SQL使用IDataReader,我希望你能得到的最接近的是ctx.GetCommand(),并传递一个查询。然后,您将使用上面的ExecuteReader或ExecuteXmlReader。我对EF不太了解...

If you give an example of the type of query that is failing, there might be some tricks possible - for example, if you are filtering or selecting subsets of the xml, there are things you can do in SQL/XML - perhaps in a UDF called via LINQ-to-SQL.

如果您举例说明失败的查询类型,可能会有一些技巧 - 例如,如果您正在过滤或选择xml的子集,那么您可以在SQL / XML中执行某些操作 - 可能在UDF中通过LINQ-to-SQL调用。

#2

I haven't measured the size of the strings, but they quickly reach .Net limits. The information is XML "stored" in a XStreamingElement object generated by a LINQ query. I will give a shot to your IDataReader suggestion to see if it solves the problem. Basically, I'm reading back with LINQ to SQL, is there any hook so I can provide that IDataReader chunking code?

我没有测量琴弦的大小,但很快达到.Net限制。该信息是XML“存储”在LINQ查询生成的XStreamingElement对象中。我将尝试一下你的IDataReader建议,看看它是否解决了这个问题。基本上,我正在阅读LINQ to SQL,是否有任何钩子,所以我可以提供IDataReader分块代码?

#1