Last active
March 8, 2020 15:35
-
-
Save joe-oli/4f07ef8785a9711018a3f47fb4ba91fa to your computer and use it in GitHub Desktop.
Entity Framework Update Entity Props - not whole Entity, just some props
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using (SECEntities dbCtx = new SECEntities()) | |
{ | |
dbCtx.Configuration.LazyLoadingEnabled = false; | |
dbCtx.Configuration.ProxyCreationEnabled = false; //NO dynamic PROXY, want strongly typed entities ! | |
//select * from TB_ADMIN_UserSession where accessToken = '12345678-...' and getDate() <= expireAt | |
var qry = dbCtx.TB_ADMIN_UserSession.Where(x => x.accessToken == token && DateTime.Now <= x.expireAt); | |
var TB_SessionFound = qry | |
.AsNoTracking() //readonly ! (faster) | |
.SingleOrDefault(); | |
if (TB_SessionFound != null) | |
{ | |
//Extend expiry date from NOW + 20 mins | |
DateTime newExpiry = DateTime.Now.AddMinutes(20); | |
//remove annoying millisecs etc. | |
newExpiry = new DateTime(newExpiry.Year, newExpiry.Month, newExpiry.Day, newExpiry.Hour, newExpiry.Minute, newExpiry.Second); | |
//then re-SAVE the extended time... //BUT NOTE that TB_SessionFound is DISCONNECTED ( .AsNoTracking used above); | |
var dbEntityEntry = dbCtx.Entry(TB_SessionFound); //SO YOU NEED TO RE-ATTACH | |
dbEntityEntry.State = EntityState.Unchanged; //change from .Detached (not tracked) to .Unchanged (tracked) | |
var TB_newSession = dbEntityEntry.Entity; | |
TB_newSession.expireAt = newExpiry; //.. AND THEN UPDATE the prop or props to be updated (AFTER ATTACHING) | |
dbCtx.SaveChanges(); //.. only then you can Save !!! | |
/* OR in EF6, you can log the statements generated as follows: | |
using (var sqlLogFile = new StreamWriter("C:\\temp\\EFlog.txt")) | |
{ | |
dbCtx.Database.Log = sqlLogFile.Write; | |
dbCtx.SaveChanges(); | |
} | |
--> file contents as per below: | |
Opened connection at 28/10/2019 3:43:29 PM +11:00 | |
Started transaction at 28/10/2019 3:43:29 PM +11:00 | |
UPDATE [dbo].[TB_ADMIN_UserSession] | |
SET [expireAt] = @0 | |
WHERE ([accessToken] = @1) | |
-- @0: '28/10/2019 4:03:02 PM' (Type = DateTime2) | |
-- @1: '51c58a3b-5402-4d50-a8b8-e69abd45a880' (Type = Guid) | |
-- Executing at 28/10/2019 3:43:29 PM +11:00 | |
-- Completed in 11 ms with result: 1 | |
Committed transaction at 28/10/2019 3:43:29 PM +11:00 | |
Closed connection at 28/10/2019 3:43:29 PM +11:00 | |
*/ | |
return newExpiry; | |
/* TODO. | |
var rtnObj = new UserInfo() | |
{ | |
NewExpiry = newExpiry, | |
UserID = TB_SessionFound.TB_ADMIN_USER_MASTER.userID, | |
TenantID = TB_SessionFound.TB_ADMIN_USER_MASTER.ten | |
}*/ | |
} | |
else | |
{ | |
return new DateTime(1970, 1, 1); //Not valid (Token Not Found, or expired). | |
} | |
} //end-Using | |
//--- ANOTHER EXAMPLE | |
public static string ChangePwd(int userID, string oldPwd, string newPwd) | |
{ | |
//-- step#1. verify that oldPwd IS VALID FOR current User | |
string errMsgOut = ""; | |
if (!isPwdValid(userID, oldPwd)) | |
{ | |
return "Password change failed; current (old) password is incorrect."; | |
} | |
//-- step#2. Validate New Pwd rules: | |
errMsgOut = ""; | |
if (!Utils.IsPasswordValid(newPwd, out errMsgOut)) | |
{ | |
return errMsgOut; | |
} | |
//================ YAY !! Proceed to persist changes to DB ================= | |
// Hash / Salt, etc and INSERT INTO DB *** Actually leave Salt out, ran out of time, do the quick and dirty + reuse Cix-V1 code. | |
//var hashStruct = Crypto.CreateHash(newPwd); **** | |
var hashedPwd = RandomPasswordGenerator.encode(newPwd); //newPwd is clear-text at this stage, but hashedPwd is the (unclear) text ! | |
//.encode() is a misnomer, it's actually doing a (one-way) Hash, rather than Encoding | |
//============================== | |
//-- step#3. UPDATE row with new PWD ... | |
int rowsAffected = -1; | |
DateTime currDt = DateTime.Now; | |
using (SECEntities dbCtx = new SECEntities()) | |
{ | |
dbCtx.Configuration.LazyLoadingEnabled = false; | |
dbCtx.Configuration.ProxyCreationEnabled = false; //NO dynamic PROXY, want strongly typed entities ! | |
dbCtx.Configuration.ValidateOnSaveEnabled = false; //SO the Entity to Update can be a "PATCH", i.e. dont provide all mandatory fields, only the ones you want. | |
TB_ADMIN_USER_MASTER Tb_userEntity = new TB_ADMIN_USER_MASTER() | |
{ | |
userID = userID, //IDENTITY / PK. | |
pwd = hashedPwd, //Save Hashed-PWD to DB. | |
lastPwdChange = currDt, | |
updatedAt = currDt, | |
updatedBy = userID //updated by the User itself which is logged in. | |
}; | |
using (var sqlLogSW = new StreamWriter(@"C:\TMP\EFSqlLog.txt")) | |
{ | |
dbCtx.Database.Log = sqlLogSW.Write; | |
//Above sets up the loggin mechanism... | |
dbCtx.TB_ADMIN_USER_MASTER.Attach(Tb_userEntity); //State= Unchanged | |
var myState = dbCtx.Entry(Tb_userEntity).State; | |
//dbCtx.Entry(Tb_userEntity).Property("pwd").IsModified = true; //only this prop is dirty, so Generated SQL UPDATE STATEMENT only updates this field. | |
dbCtx.Entry(Tb_userEntity).Property(x => x.pwd).IsModified = true; //prefer to above line, strongly typed ! | |
dbCtx.Entry(Tb_userEntity).Property(x => x.lastPwdChange).IsModified = true; //prefer to above line, strongly typed ! | |
dbCtx.Entry(Tb_userEntity).Property(x => x.updatedAt).IsModified = true; //prefer to above line, strongly typed ! | |
dbCtx.Entry(Tb_userEntity).Property(x => x.updatedBy).IsModified = true; //prefer to above line, strongly typed ! | |
//**** ARRRRHHHHHHH FARKKKK - ONLY THE PROPS explicitly modified will appear in SQL STATEMENT ****.. wild goose chase *** | |
var myState2 = dbCtx.Entry(Tb_userEntity).State; | |
var myState3 = dbCtx.Entry(Tb_userEntity).Property(x => x.email).IsModified; //verify | |
rowsAffected = dbCtx.SaveChanges(); | |
/* ERROR (1) | |
Member 'IsModified' cannot be called for property 'pwd' because the entity of type 'TB_ADMIN_USER_MASTER' does not exist in the context. To add an entity to the context call the Add or Attach method of DbSet<TB_ADMIN_USER_MASTER>. | |
ERROR (2) | |
Entity of type TB_ADMIN_USER_MASTER in state Modified has the following validation errors: | |
- Property: firstName, Error: The firstName field is required. | |
- Property: lastName, Error: The lastName field is required. | |
- Property: loginName, Error: The loginName field is required. | |
- Property: email, Error: The email field is required. | |
, *** unless you set dbCtx.Configuration.ValidateOnSaveEnabled = FALSE **** | |
* | |
* YAY! log confirms the UPDATE sql statement is the "partial/patch" one that we want few fields only; | |
UPDATE [dbo].[TB_ADMIN_USER_MASTER] | |
SET [pwd] = @0 | |
WHERE ([userID] = @1) | |
-- @0: 'cjhweXBKaFgrNlZHTnIwOS9maDB1dGErYTBZPQ==' (Type = AnsiString, Size = 400) | |
-- @1: '18' (Type = Int32) | |
OR: (after adding additional fields 2,3,4) | |
UPDATE [dbo].[TB_ADMIN_USER_MASTER] | |
SET [pwd] = @0, [lastPwdChange] = @1, [updatedAt] = @2, [updatedBy] = @3 | |
WHERE ([userID] = @4) | |
-- @0: 'YlJiVVNHaXNUVzNudjNvL3d6R2lrcDZRbFI0PQ==' (Type = AnsiString, Size = 400) | |
-- @1: '8/03/2020 6:16:37 AM' (Type = DateTime2) | |
-- @2: '8/03/2020 6:16:37 AM' (Type = DateTime2) | |
-- @3: '18' (Type = Int32) | |
-- @4: '18' (Type = Int32) | |
* FURTHERMORE, stackoverflow says: | |
* I think is worth mention that if you are using db.Configuration.ValidateOnSaveEnabled = false, | |
* ; you might want to keep validating the field you are updating, | |
* ; if (db.Entry(user).Property(x => x.pwd).GetValidationErrors().Count == 0) for all fields. | |
*/ | |
} | |
} | |
if (rowsAffected > 0) | |
{ | |
return "success"; | |
} | |
else | |
{ | |
return "Failed to change password."; | |
} | |
//...OR OTHER ERROR is handled by the caller. | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment