1 reply to this topic

[LiLMsNinja]

I have generated this code which is supposed to get a record from User_Info, change the projectID, and insert the record into Secondary_User_Info. For some reason, it deleted the record from User_Info and inserted to Secondary_User_Info. The SQL server is also holding onto the 'delete transaction' from user_info 'in case the user who made the change wishes to rollback.' Where, how, and why does LINQ think it should delete from User_Info based on this code:

[COLOR=blue][FONT=Courier New]public[/FONT][/COLOR][FONT=Courier New] [COLOR=blue]static[/COLOR] [COLOR=blue]void[/COLOR] updateIt([COLOR=blue]string[/COLOR] username, [COLOR=blue]string[/COLOR] newProjectid)[/FONT]
[FONT=Courier New]      {[/FONT]
[FONT=Courier New]          [COLOR=#2b91af]V4DataContext[/COLOR] db = [COLOR=blue]new[/COLOR] [COLOR=#2b91af]V4DataContext[/COLOR](Properties.[COLOR=#2b91af]Settings[/COLOR].Default.strConBAM);[/FONT]
[FONT=Courier New]          [COLOR=blue]var[/COLOR] query = ([COLOR=blue]from[/COLOR] r [COLOR=blue]in[/COLOR] db.User_Infos[/FONT]
[FONT=Courier New]                       [COLOR=blue]where[/COLOR] r.ProjectID == [COLOR=#2b91af]Program[/COLOR].ProjectID &&[/FONT]
[FONT=Courier New]                       r.Username == username[/FONT]
[FONT=Courier New]                       [COLOR=blue]select[/COLOR] r).FirstOrDefault();[/FONT]
[FONT=Courier New]          query.ProjectID = newProjectid;[/FONT]
 
[FONT=Courier New]          [COLOR=#2b91af]Secondary_User_Info[/COLOR] su = [COLOR=blue]new[/COLOR] [COLOR=#2b91af]Secondary_User_Info[/COLOR]()[/FONT]
[FONT=Courier New]          {[/FONT]
 
[FONT=Courier New]              ProjectID = query.ProjectID,[/FONT]
[FONT=Courier New]              Username = query.Username,[/FONT]
[FONT=Courier New]              Fullname = query.Fullname,[/FONT]
[FONT=Courier New]              Active = query.Active,[/FONT]
[FONT=Courier New]              HierarchyID = query.HierarchyID,[/FONT]
[FONT=Courier New]              UnitID = query.UnitID,[/FONT]
[FONT=Courier New]              UserLevel = query.UserLevel,[/FONT]
[FONT=Courier New]              SMG = query.SMG,[/FONT]
[FONT=Courier New]              EditQuickScores = query.EditQuickScores,[/FONT]
[FONT=Courier New]              EditCharts = query.EditCharts,[/FONT]
[FONT=Courier New]              MultiHierarchyUser = query.MultiHierarchyUser[/FONT]
[FONT=Courier New]          };[/FONT]
 
 
[FONT=Courier New]          db.Secondary_User_Infos.InsertOnSubmit(su);[/FONT]
 
 
 
[FONT=Courier New]          db.SubmitChanges();[/FONT]

[LiLMsNinja]

Soo, i solved this and wanting to post up the solution. It's kind of hard to explain, i'll do my best, if anyone comes across a similar issue don't hesitate to PM me.

I didn't realize that LINQ was smart enough to 'assume' that when i said:

query.ProjectID = newProjectid;

It meant update ProjectID in 'User_Info' ON TOP of creating the new record for Secondary_User_Info in the last part of the code.

My mentality was, after i pulled the data i needed from User_Info into the 'var query' that i was free to manipulate that 'var query' as i see fit without impact to the actual database. I thought that only the very specific 'InsertOnSubmit' statement would be the only thing being updated after 'db.SubmitChanges'. That's not the case. Linq assumed i wanted to update projectID in User_Info and Insert a new record to Secondary_User_Info.

The fun part begins here.... I THOUGHT the record for User_Info was being deleted, but the newly updated projectID was causing the DB to kick it back as a primary key violation; holding it in a transaction in case the user wishes to rollback. Only after using a test DataSet where the only primary key was an autonumber did i get a chance to truely see what was happening and relate that back to the database structure applied to the 2 user_info tables.

Nonetheless, I have been advised by another fellow C# developer that if I have to do it in this manner(manipulate one linq object for another) then keep my actual inserts and updates on a separate datacontext object. Saying, db.SubmitChanges will make all changes within the given datacontext. Or, in the situation above it is better to just use the 'newProjectID' down in the insert for Secondary_User_info without every actually changing the 'var query' Like so:

public static void updateIt(string username, string newProjectid)

      {

          V4DataContext db = new V4DataContext(Properties.Settings.Default.strConBAM);

          var query = (from r in db.User_Infos

                       where r.ProjectID == Program.ProjectID &&

                       r.Username == username

                       select r).FirstOrDefault();


 

          Secondary_User_Info su = new Secondary_User_Info()

          {

 

              ProjectID = newProjectid,

              Username = query.Username,

              Fullname = query.Fullname,

              Active = query.Active,

              HierarchyID = query.HierarchyID,

              UnitID = query.UnitID,

              UserLevel = query.UserLevel,

              SMG = query.SMG,

              EditQuickScores = query.EditQuickScores,

              EditCharts = query.EditCharts,

              MultiHierarchyUser = query.MultiHierarchyUser

          };

 

 

          db.Secondary_User_Infos.InsertOnSubmit(su);

 

 

 

          db.SubmitChanges();

Now, i feel kind of silly with the way i had the code before. This make much more sense and is now giving me the desired results! Hope this helps someone! Thanks!