Update database items from the website

Question

My current problem is (probably) not necessarily directly related to MVC 6, but how working with database actually works, and therefore any help/suggestions in this matter would be more than appreciated.

For the sake of this question, let's say that we have a very simple database with the following tables (C# classes) [we are using Entity Framework to work with the database]:

public class ShoppingUser
{
    public int Id { get; set; }
    public string UserName { get; set; }

    public ICollection<ShoppingItem> Items { get; set; }
}

public class ShoppingItem
{
    public int Id { get; set; }
    public string Quantity { get; set; }
    public string Text { get; set; }
    public bool ToRemove { get; set; }//if item has been bought, it can be removed from the shopping list
}

This demo will be for a super duper simple shopping list app, where user (ShoppingUser who is registered in the system can have a List of ShoppingItem where user can decide on what is the text of the item (e.g. Bread, Butter, Tomatoes, ...) and also a quantity (3 pieces, 5kg, ... simple string)

Afterwards in my ASP.NET Core app, I have defined a repository which is communicating with the database and has access to the ShoppingItem class (as we are only interested in shopping items of currently logged in user).

Example of some method we could use here:

    public IEnumerable<ShoppingItem> ReturnUserItems(string sUsername)
    {
        if (string.IsNullOrWhiteSpace(sUsername))
            return null;

        var result = _context.ShoppingUsers.Include(n => n.Items).Where(n => n.UserName == sUsername).FirstOrDefault();

        if (result != null)
            return result.Items;
        else
            return null;
    }

Finally we have an API controller with JsonResult for either GET, POST, DELETE, ..., which is used for communication between client side AngularJs App and our server side logic.

Example of GET Method:

    // GET: /<controller>/
    [HttpGet("")]
    public JsonResult Get(string sUserName)
    {
        try
        {
            var results = _repository.ReturnUserItems(User.Identity.Name);

            if (results != null)
            {
                var result = Mapper.Map<IEnumerable<ShoppingItemViewModel>>(results);
                return Json(result);
            }

            Response.StatusCode = (int)HttpStatusCode.OK;
        }
        catch (Exception ex)
        {
            Response.StatusCode = (int)HttpStatusCode.BadRequest;
            return Json(new { Message = ex.Message });
        }

        return null;
    }

Here comes the tricky part (at least for me). From video tutorials I have learned, that I should never (or almost never) expose my real database model to the website (I guess it's for security reasons). Due to that (as visible from my GET method above) I have declared my ShoppingItemViewModel which contains only properties I want to expose to the user (e.g. meaning that Id of my item is not visible).

This is how it looks like:

public class ShoppingItemViewModel
{
    public string Quantity { get; set; }
    [Required]
    public string Text { get; set; }
    [Required]
    public bool ToRemove { get; set; }//if item has been bought, it can be removed from the shopping list
}

And for the communication from my AngularJS App I am using simple $http.get and $http.post calls for retrieving / posting updated data.

Finally the question:

My problem is, that if a user decides to either delete an item from his shopping list, or decides to change the content of either text / quantity (meaning that originally in the database it was tomatoes - 5 kg but he manages to buy only 2 kg and therefore changes the quantity to tomatoes - 3kg), how can the app understand which elements have actually been changed and how? The problem I have in this case is, that we are no longer exposing the database Id of the items.

If I was writing a desktop app, where I wouldn't have to create this sub view (ShoppingItemViewModel), my EntityFramework is intelligent enough to check & update all the changes in my database. Unfortunately in this case, I do not understand how this is achievable.

When I was thinking about it I came with the following: Add a new property into the ShoppingItem and ShoppingItemViewModel: public string sCustomKey {get; set; }, which would serve as a unique key for every item. This way, we no longer need to expose our database Id, but we are exposing the 'fake' one.

Second question: I case my solution would be accurate, what is the best way to update items in the database? The only way I can think of is iterating through all the items in the database and manually check for changes?

Example of what I have in mind:

    //IEnumerable<ShoppingItem> would be re-mapped result of ShoppingItemViewModel we have received back from the website
    public void UpdateValues(IEnumerable<ShoppingItem> items, string sUserName)
    {
        //retrieves list of shopping items for specified customer
        var allItems = _context.ShoppingUsers
            .Include(n => n.Items)
            .FirstOrDefault(n => n.UserName == sUserName);

        //updates the values
        foreach (var sItem in items)
        {
            var updatedItem = allItems.Items.FirstOrDefault(n => n.Text == sItem.sCustomKey);

            if (updatedItem == null)
            {
                //create new item
                var newItem = new ShoppingItem();
                newItem.Text = sItem.Text;
                newItem.ToRemove = sItem.ToRemove;
                allItems.Items.Add(newItem);
            }
            else
                updatedItem.ToRemove = sItem.ToRemove;
        }


        _context.SaveChanges();
    }

But this approach does not seem right to me.

Any help regarding these matters would be more than appreciated as I am still learning how to work with ASP.NET Core and web projects.

Answer 1

1. In your first question, exposing the item ID in the ViewModels is fine. In your domain layer, you can add validation logic that those ID exists/valid item. Alternatively, you can use a Guid for your item/product because the ID (int) can easily be predicted.

2. As far as updating the items, you should not use the "username" as Identifier (of the cart) because that can be predicted/altered by the calling client. You can use Guid either persisted(to Db) or in-memory. You can add validation as well if this Guid belongs to this username/emailAddress. So updating the items in the cart, consider adding/removing one at a time if that is doable instead of sending list of items.

Answer 2

I think you have misunderstood something.

Here comes the tricky part (at least for me). From video tutorials I have learned, that I should never (or almost never) expose my real database model to the website (I guess it's for security reasons). Due to that (as visible from my GET method above) I have declared my ShoppingItemViewModel which contains only properties I want to expose to the user (e.g. meaning that Id of my item is not visible).

ViewModel <=> Domain Model <=> ReadModel (Database Model)

The point is that you shouldn't use your ReadModel(Database model) as your ViewModel in Presentation Layer (MVC). All three models will have identity.