C
C#engineertdog

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? 
c#
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#
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
});
A
Angius•161d ago
Can you really not paginate this data?
E
engineertdog•161d ago
The actual query itself takes ~15 minutes to run via SSMS, and there's nothing I can do about that because it's a 3rd party system I can show you if you'd like, but not really. It's merging a bunch of tables together
X
x0rld•161d ago
you have access to the database ?
E
engineertdog•161d ago
Yes I believe read-only for this client though
X
x0rld•161d ago
you can't do a view and paginate the view ?
A
Angius•161d ago
You could try .AsEnumerable() instead of .ToList(), this will give you the ability to process each record individually without loading them all: <>
E
engineertdog•161d ago
I'd have to look into that. If I can, how would that change the C# execution? Just run the execute as normal?
A
Angius•161d ago
E
engineertdog•161d ago
I did try to use AsEnumerable, but I received a message that my dbContext changed or was no longer valid
X
x0rld•161d ago
cause you don't inject your dbcontext using (DbContext tempContext = new DbContext(con)) {
A
Angius•161d ago
^
J
Jimmacle•161d ago
can you put the query results into a temporary table then query that in pages?
E
engineertdog•161d ago
Yeah, the data controller and the data processor are separate I could probably do that, would just have to figure out how to set it up I fixed the context issue. It's just sort of an odd way to do it because I was trying to keep DB logic separate from app logic. But since this is only 1 function required for the abstract for a specific use-case, it'll be fine
Want results from more Discord servers?
Add your server
More Posts
✅ Help for unityGood evening I am looking for someone who can teach me how to create a game from A to Z. Even if I dAny way to get XAML Auto-complete in VS Code?I recognize this isn't quite a C# question, but it's related. I want to make cross platform apps usi.Net 8 Blazor Web App Identity Framework HelpSuper quick overview: I have a Blazor Web App that is single process only hosting UI. It communicaRegex Group Containshey simple easy question, but are there ways to access individual capture groups in a regex match? I"netsh" cmd Commands not being executedWell, I am trying to set up some firewall-rules and such, the problem is non of the commands is beinAutomate the process of transferring a Microsoft SQL Server .bak file to MongoDBI'm looking to automate the process of transferring a Microsoft SQL Server backup file (e.g., bulk.bMapping controller to / without breaking other controllersHi there, I would like to rename `HomeController` to `DefaultController` and instead of going to `/helpwhy is doesn't workTrack Thread/Task Progress through SignalR/WebSocketsI have an ASP.NET Web Core API and an Angular project both connected. In the dashboard of my Angularazor pages vs .cshtmlTeam , What is the difference between razor pages and cshtml.? razor pages looks like old asp.net w✅ mobile applicationMy question is quite fundamental, the way I imagine mobile apps is that they utilize an API to conneIt's not showing the database in datagridviewI'm having trouble trying to show the database that the teacher gave me and the demo she gave me to Can't seem to make a universal filepathHi there could anyone explain how I can make a universal filepath so other pc's can locate the fileshey! looking for fellow C# noobs!Does anyone want to vc and group study,go over things etc? No dumb questions or rudeness I'm alsl beStuttering in UI and I don't know whyEverytime I launch the app, the very first time I navigate to any FlyoutItem I get this stuttering iIssue with Filtering in DataGridI use Telerik UI's DataGrid control for .Net Maui. As you see VIPStatus column holds values such as Minimum Required Knowledge before I am job ready.Hello guys, I want to know this info from someone that has work experience. What is minimum requiredAsssembly Version WrongIt was working for a little bit, but now it's displaying `v1.0.0-alpha+9fb67d6af368e6cd5a05c8f401327enum HasFlagI would've thought this would return true, but it's false. How can I fix this logic to return true fI gotta fix tha "mappings.Add()", It allways calls null, pls help (UPnP)well, as said every time I Initiate the programm the "mappings" is called 0, I checked every variabl