Geek Answers Handbook

Processing very large strings between SQL Server and .NET + LINQ

I have an application that needs to process very large lines between a SQL Server database and .NET code. I have a LINQ query that generates rows when saving them to a database, but when I try to create a database from , the application crashes with an OutOfMemoryException due to the size of the rows.

Do I need to do something to avoid the generated LINQ code? Using some kind of compression may be an option, but I would like to avoid this for performance reasons.

+3
string sql-server linq
source share
2 answers

What do you call "very big"? And what is a string? CLOB? Blob? XML?

I suspect you should use things like ExecuteReader() , which (via IDataReader ) provides methods for reading such columns in pieces:

  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 need an XmlReader through cmd.ExecuteXmlReader() .

Updated re LINQ comment (now deleted):

To use IDataReader directly from LINQ-to-SQL, I expect that closest you will get ctx.GetCommand() by passing it a query. Then you would use ExecuteReader or ExecuteXmlReader as above. I'm not very good at EF ...

If you give an example of a query type that fails, some tricks are possible - for example, if you filter or select a subset of xml, there are things you can do in SQL / XML - perhaps in UDF called through LINQ-to-SQL.

+1
source share

I did not measure the size of the lines, but they quickly reach .Net limits. The XML information is "stored" in the XStreamingElement generated by the LINQ query. I will give your IDataReader suggestion a shot to decide if it solves the problem. Basically, I am reading back from LINQ to SQL, is there any hook so I can provide code for IDataReader code?

0
source share

More articles:


All Articles