CODEWITHSUNDEEP
asp.net-mvc-5mappingfieldentity-framework-5
TTCG
TTCG

Reputation: 9113

EntityFramework Exclude Some Fields in Updating and Inserting

This is my 1st ever project with Entity Framework and I want to ignore some data columns in my Insert, Update activities. But I still need to bind them when I display the data on my view.

In the following picture, you can see some codes in my Model and Table Structure

enter image description here

In the SQL Server, DateCreated, I set the Default Value of it to 'GETDATE()' and it should be ignored in the Insert and Update statement at all.

Similarly, UserCreated and DateCreated should be excluded in the Update operation too.

But when I tried to catch the SQL scripts sent to the server, those values are included in the Generated SQLs and it's causing me error like DateTime overflow, Null value not accepted, etc..

Generated SQL Script in SQL Profiler

exec sp_executesql N'INSERT [dbo].[Events]([Title], [Detail], [StartDate], [StartTime], [EndTime], [CategoryID], [Campus], [Location], [DateCreated], [UserCreated], [DateModified], [UserModified], [state], [IsPosted]) 
        VALUES (@0, @1, @2, @3, @4, @5, @6, NULL, @7, @8, NULL, NULL, @9, @10) 
SELECT [EventID]
                FROM [dbo].[Events] WHERE @@ROWCOUNT > 0 AND [EventID] = scope_identity()',
        N'@0 varchar(1000),@1 varchar(4000),@2 datetime2(7),@3 time(7),@4 time(7),@5 varchar(10),@6 varchar(100),@7 datetime2(7),@8 varchar(50),@9 tinyint,@10 bit',@0='Football',
    @1='Welcome to <strong>Football </strong>Match',@2='2016-01-29 00:00:00',@3='07:00:00',@4='12:00:00',@5='GIG',@6='xxxx',@7='0001-01-01 00:00:00',@8='username',@9=0,@10=0

Upvotes: 1

Views: 3853

Answers (3)

TapiwaJoel Mudavanhu
TapiwaJoel Mudavanhu

Reputation: 53

When Inserting or updating just leave out the fields you don't want to be updated or inserted as below

 [HttpPost]
 public ActionResult Index(TimeSpan StartTime, TimeSpan EndTime, string CategoryID, string Campus, string Location, DateTime DateModified, string UserModified,
 byte State, bool IsPosted)
    {

    var yourDbContext = new yourDbContext();
    Events events = new Events();
    events.StartTime=StartTime;
    events.EndTime=EndTime;
    events.CategoryID=CategoryID;
    events.Campus=Campus;
    events.Location =Location;
    events.DateModified=DateModified;
    events.UserModified=UserModified;
    events.State= State;
    events.IsPosted= IsPosted;      

    //Inserting
        try
        {
            yourDbContext.Events.Add(events);
            yourDbContext.SaveChanges();

            return Json("Inserted", JsonRequestBehavior.AllowGet);
        }
    catch (Exception e)
        {

            return Json(e.ToString(), JsonRequestBehavior.AllowGet);
        }

    //end of inserting


    //Updating
    try
        {
            yourDbContext.Entry(events).State = EntityState.Modified;
            yourDbContext.SaveChanges();
               return Json("Updated", JsonRequestBehavior.AllowGet);
        }
    catch (Exception e)
        {
            return Json(e.ToString(), JsonRequestBehavior.AllowGet);
        }   

    //End of updating   

    }

When displaying your data on your view, just generate a strongly typed view based on your events model

Upvotes: 2

Vini
Vini

Reputation: 2134

This could also be achieved in the controller.

Create

In the create method you could set the value of CreatedBy and CreatedOn.

 CreatedOn =  DateTime.Now;
 CreatedBy = User.Identity.Name; //In case of Windows authentication else user name can be taken from the db
 ModifiedOn = null;
 ModifiedBy = null;

Update

In Update you could again set the value of ModifiedBy and ModifiedOn as above and to retain CreatedBy and CreatedOn the following could be added

events.ModifiedOn = DateTime.now;
events.ModifiedBy = User.Identity.Name;
db.Entry(events).State = EntityState.Modified;
db.Entry(events).Property("CreatedOn").IsModified = false;
db.Entry(events).Property("CreatedBy").IsModified = false;

Upvotes: 0

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

Insert

Use [DatabaseGenerated(DatabaseGeneratedOption.Computed)] data annotation on DateCreated column to use default value generated by the database.

https://msdn.microsoft.com/en-us/data/jj591583.aspx#DatabaseGenerated

Update

Entity Framework only update modified field, so simply don't change the value of UserCreated and it will not be updated.

Upvotes: 2

Related Questions