CODEWITHSUNDEEP
c#mysqlsql-serverlinq
dahui
dahui

Reputation: 2166

LINQ SQL query, SubmitChanges is not submitting changes to DB

I am writing a LINQpad script to anonymise certain attributes of an XML object, stored in XML columns of a table. I can access data fine, and then change it, and through LINQpad Dump()s, it seems to be updated. However when I run SubmitChanges() and then run another query from LINQ (or SQL Server), the relevant row has not been updated.

I've tried googling this problem, a common issue I found is related to the primary key, I've verified in SQLStudio the table in use does have a primary key, so I don't think this is the issue.

Also, you can see in the first line I've set Log on the DataContext to Console.Out. With this on, I get a couple of lines verifying my first query (the select) works. However, I don't get any sort of UPDATE query in the console after running my UpdateRecord function, or running SubmitChanges, maybe the update logic is incorrect?

Update: I added GetChangeSet().Dump() before running SubmitChanges, there are 0 Updates and 0 Inserts in the ChangeSet, confirming my UpdateRecord function is not correctly updating anything.

void Main() {
    Log = Console.Out;

    AnonymiseCommand("MyCommandName");

}

void AnonymiseCommand(string command) { 

    // Note we need to pluralise the table name for the LINQ query
    // Table is actually called ChangeSet_Detail
    var results = ChangeSet_Details
                    .Where(c => c.Command.ToString().Contains(command) )
                    .ToDictionary(row => (int) row.ChangeSetID,
                                  row => row.Changes);

    int commandCount = results.Count;
    Print("Command count for " + command + " is " + commandCount + ".");

Everything up to this point works fine, commandCount correctly returns 1, and the results dictionary contains the correct values. Next I will iterate through the dictionary, and attempt to update a row with an ID matching the dicts key, with the XElement mapped to the key.

    foreach (KeyValuePair<int, XElement> entry in results) {
        AnonymiseXml(entry.Value);
        UpdateRecord(entry.Key, entry.Value);
    }

}

// This function isn't so important, it anonymises the attributes and seems to work
void AnonymiseXml(XElement xml) {
    // anonymise attributes
    var attributes = xml.Attributes();
    foreach(var attr in attributes) {
        attr.Value = new String('?', attr.Value.Length);
    }

    // repeat across child nodes
    var kiddies = xml.Elements();
    foreach (var kid in kiddies) {
        AnonymiseXml(kid);
    }
}

void UpdateRecord(int rowID, XElement newXml) {
    ChangeSet_Detail entry = 
        (from c in ChangeSet_Details
            where c.ChangeSetID == rowID
            select c).Single();

    entry.Dump();
    entry.ChangeSetID = rowID;
    entry.Changes = newXml;
    entry.Dump();

    GetChangeSet().Dump();

    try{
        SubmitChanges();
    } catch(Exception e) {
        e.Dump();
    }

}

The UpdateRecord function is where I am trying to commit the changes to the DB. I pass in the row ID that we are currently looking at, and the new XML element. When I retrieve the entry, it seems my changes are still in effect, on the first entry.Dump() I can see the attributes are anonymised. I change the entries XML column (column is named Changes) anyway, and finally call SubmitChanges().

If I then query the table in LINQpad or SQL Server, my changes have not taken effect and the attributes have not been anonymised.

Upvotes: 1

Views: 886

Answers (2)

user11621331
user11621331

Reputation: 

      ComplaintComment tcc = new ComplaintComment();

            var Dat = DateTime.Now;

            tcc.comp_Id =1;
            tcc.cust_Id = 1;

            tcc.cc_Comments = txtComment.Text;

dbContext.ComplaintComments.InsertOnSubmit(tcc);

Upvotes: 0

dahui
dahui

Reputation: 2166

Solved it myself.

So I think the issue is, I am changing the XML values in place, i.e. the object references never change, the XElement and its objects stay the same, but I change the string values of the attributes, perhaps this means LINQ detects no changes.

When I tell LINQ to refresh changes before running SubmitChanges(), the Update is logged and my changes are persisted.

The line added is simply:

Refresh(RefreshMode.KeepCurrentValues, entry);

Upvotes: 2

Related Questions