CODEWITHSUNDEEP
sqlasp.net-mvclinqcrud
Eduard
Eduard

Reputation: 722

Delete and Update database at the same time MVC

I want to delete all records in a table as long as there exist more than X records based on some ID in database.

        using(ApplicationDbContext app = new ApplicationDbContext())
        {             
            try
            {
                var UserImg = app.Images.Where(x => x.UserID == LoggedUserId).Select(s => s.ID).FirstOrDefault();

                if (UserImg != null)
                {
                    app.Database.ExecuteSqlCommand("TRUNCATE TABLE [Image] LIMIT 2");                 
                }
                else if(UserImg == null)
                {
                    app.Images.Add(img);
                }

                app.SaveChanges();

                return RedirectToAction("Details", "Jobs", new { controller = "JobsController", action = "Details", id = Session["DetailsURL"] });

            }

            catch (DbEntityValidationException ex)
            {
                //non-relevant stuff
            }
        }

More precise on ExecuteSqlCommand, how do I manage to keep X records ? Because I want to keep 1 Image by UserID all the time. If I just truncate it will delete everytime everything and I don't know how to use LIMIT here.

Basically I want to Update current Image with a new one, if there is another approach I feel more open to try.

Solution:

            try
            {                   

                foreach (var id in app.Images.Where(x => x.UserID == LoggedUserId).Select(e => e.ID))
                {
                    var entity = new Image { ID = id };
                    app.Images.Attach(entity);
                    app.Images.Remove(entity);
                }

                app.Images.Add(img);
                app.SaveChanges();

                return RedirectToAction("Details", "Jobs", new { controller = "JobsController", action = "Details", id = Session["DetailsURL"] });

            }

Upvotes: 0

Views: 1018

Answers (2)

Jishnu KM
Jishnu KM

Reputation: 234

@Eduard try this

i think this is your answer

it will remove all existing data except the last one and insert a new one

this is pure EF and faster than SubQuerys

using(ApplicationDbContext db = new ApplicationDbContext())
{             
    try
    { 
        //collect all images except final one
        var existImgRows = db.Images.Where(x => x.ID == UserID).OrderByDescending(x => x.ID).Skip(1).Select(x=>x); 

        if(existImgRows != null)
        {
            foreach (var item in existImgRows)
            {
                db.Entry(item).State = System.Data.Entity.EntityState.Deleted;
            }
        }
        db.Entry(img).State = System.Data.Entity.EntityState.Added;
        db.SaveChanges();
    }
    catch (DbEntityValidationException ex)
    {
        //non-relevant stuff
    }
}

Upvotes: 1

Ahsan
Ahsan

Reputation: 2518

You can't use truncate as far as i know for this purpose. you can do something like the following.

DELETE FROM [image] 
WHERE  [id] IN (SELECT [id] 
                FROM   [image] 
                WHERE  id < (SELECT Max(id) 
                             FROM   [image]))

This would leave out the last record (by id) and also would perform poorly than truncate since it would be transaction logged.

Upvotes: 2

Related Questions