Skip to content

Instantly share code, notes, and snippets.

@joe-oli
Last active March 8, 2020 15:35
Show Gist options
  • Save joe-oli/4f07ef8785a9711018a3f47fb4ba91fa to your computer and use it in GitHub Desktop.
Save joe-oli/4f07ef8785a9711018a3f47fb4ba91fa to your computer and use it in GitHub Desktop.
Entity Framework Update Entity Props - not whole Entity, just some props
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