Linq to SQL查询待插入记录：获取后端数据及待更新/插入数据

Alright, let's tackle your problem step by step. You need to pull three types of data from your NtsSuiteDataContext: existing records, ones marked for update, and pending inserts—with the last one being the tricky part depending on the scenario. Let's dive in.

1. Grab Existing Data #

This is the straightforward part. You can query the DataContext's table directly:

NtsSuiteDataContext DbContext = new NtsSuiteDataContext();
// Get all existing Person records from the database
var existingPeople = DbContext.Persons.ToList();

// Or target a specific record using your key variable
int key = 1;
var targetExistingPerson = DbContext.Persons.SingleOrDefault(p => p.PersonKey == key);

2. Get Pending Update Data #

Linq to SQL automatically tracks changes to entities you've fetched from the context. To get updates waiting to be submitted, use the GetChangeSet() method:

var changeSet = DbContext.GetChangeSet();
// Filter the Updates collection to only Person entities
var pendingUpdates = changeSet.Updates.OfType<Person>().ToList();

Any time you modify a property of an existing entity (e.g., targetExistingPerson.FirstName = "Rick"), that entity gets flagged as Modified and shows up in the Updates list.

3. Retrieve Pending Insert Data (The Tricky Bit) #

Pending inserts fall into two scenarios, each with a different approach:

Scenario A: Inserts Already Tracked by the DataContext #

If you've called InsertOnSubmit() for your new entity, it's already being tracked. You can pull these from the Inserts collection in GetChangeSet():

// Example: Create and track a new Person
Person newTrackedPerson = new Person { PersonKey = 3, FirstName = "Jane", LastName = "Smith" };
DbContext.Persons.InsertOnSubmit(newTrackedPerson);

// Fetch all pending tracked inserts
var pendingTrackedInserts = changeSet.Inserts.OfType<Person>().ToList();

Scenario B: Inserts Not Yet Added to the DataContext #

If you've created a new Person object but haven't called InsertOnSubmit() yet, the DataContext has no idea it exists. You'll need to maintain a local collection to manage these untracked entities:

// Create a local list to hold untracked pending inserts
List<Person> untrackedPendingInserts = new List<Person>();

// Add new entities to this list instead of directly to the context
Person newUntrackedPerson = new Person { PersonKey = 4, FirstName = "Bob", LastName = "Brown" };
untrackedPendingInserts.Add(newUntrackedPerson);

// When you're ready to submit, add them to the context and save
foreach (var person in untrackedPendingInserts)
{
    DbContext.Persons.InsertOnSubmit(person);
}
DbContext.SubmitChanges();

This way, you can check untrackedPendingInserts anytime to see all inserts that haven't been registered with the DataContext yet.

Full Example Code #

Here's how all these pieces fit together in practice:

int key = 1;
NtsSuiteDataContext DbContext = new NtsSuiteDataContext();

// 1. Fetch existing data
var allExistingPeople = DbContext.Persons.ToList();
var targetExistingPerson = DbContext.Persons.SingleOrDefault(p => p.PersonKey == key);

// Simulate an update to trigger tracking
if (targetExistingPerson != null)
{
    targetExistingPerson.FirstName = "Rick";
}

// 2. Get pending updates
var changeSet = DbContext.GetChangeSet();
var pendingUpdates = changeSet.Updates.OfType<Person>().ToList();

// 3. Scenario A: Tracked pending inserts
Person newTrackedPerson = new Person { PersonKey = 3, FirstName = "Jane", LastName = "Smith" };
DbContext.Persons.InsertOnSubmit(newTrackedPerson);
var pendingTrackedInserts = changeSet.Inserts.OfType<Person>().ToList();

// 3. Scenario B: Untracked pending inserts
List<Person> untrackedPendingInserts = new List<Person>();
Person newUntrackedPerson = new Person { PersonKey = 4, FirstName = "Bob", LastName = "Brown" };
untrackedPendingInserts.Add(newUntrackedPerson);

// Output results to verify
Console.WriteLine("Existing Records:");
foreach (var p in allExistingPeople)
{
    Console.WriteLine($"{p.PersonKey} | {p.FirstName} {p.LastName} | DOB: {p.DateOfBirth?.ToShortDateString() ?? "NULL"}");
}

Console.WriteLine("\nPending Updates:");
foreach (var p in pendingUpdates)
{
    Console.WriteLine($"{p.PersonKey} | Updated Name: {p.FirstName} {p.LastName}");
}

Console.WriteLine("\nPending Tracked Inserts:");
foreach (var p in pendingTrackedInserts)
{
    Console.WriteLine($"{p.PersonKey} | {p.FirstName} {p.LastName}");
}

Console.WriteLine("\nPending Untracked Inserts:");
foreach (var p in untrackedPendingInserts)
{
    Console.WriteLine($"{p.PersonKey} | {p.FirstName} {p.LastName}");
}

A quick reminder: GetChangeSet() only knows about entities the DataContext is actively tracking. Untracked new entities won't show up here, so maintaining a local list is the way to go for those cases.

内容的提问来源于stack exchange，提问作者M Moore