Raw SQL Query Question

I have a raw SQL query that I need to execute against a database that's external to my application. The query is expected to return >5,000,000 records. Therefore, returning the records in memory (list) causes the application to crash due to running out of memory, even on a server with 122GB.

Is it possible to enumerate over the records instead, so that I don't have to deal with them all in memory?

public IList<R> GetExternalResults(string con) {
  using (DbContext tempContext = new DbContext(con)) {
    return tempContext.Database.SqlQuery<R>("SELECT * FROM xx").ToList();
  }
}

Parallel.ForEach(GetExternalResults(con), record => {
  this.AddRow(record);
  this.AddBulkRecords(record); // if batch > xx, then save the record using SQL Bulk Copy
});

public IList<R> GetExternalResults(string con) {
  using (DbContext tempContext = new DbContext(con)) {
    return tempContext.Database.SqlQuery<R>("SELECT * FROM xx").ToList();
  }
}

Parallel.ForEach(GetExternalResults(con), record => {
  this.AddRow(record);
  this.AddBulkRecords(record); // if batch > xx, then save the record using SQL Bulk Copy
});

C#•3y ago•

18 replies

engineertdog

Raw SQL Query Question

public IList<R> GetExternalResults(string con) {
  using (DbContext tempContext = new DbContext(con)) {
    return tempContext.Database.SqlQuery<R>("SELECT * FROM xx").ToList();
  }
}

Parallel.ForEach(GetExternalResults(con), record => {
  this.AddRow(record);
  this.AddBulkRecords(record); // if batch > xx, then save the record using SQL Bulk Copy
});

public IList<R> GetExternalResults(string con) {
  using (DbContext tempContext = new DbContext(con)) {
    return tempContext.Database.SqlQuery<R>("SELECT * FROM xx").ToList();
  }
}

Parallel.ForEach(GetExternalResults(con), record => {
  this.AddRow(record);
  this.AddBulkRecords(record); // if batch > xx, then save the record using SQL Bulk Copy
});

Raw SQL Query Question

Similar Threads

Raw SQL Query Question

Similar Threads

Similar Threads

Similar Threads