CODEWITHSUNDEEP
c#sqlasp.net-mvcentity-framework
mitchellJ
mitchellJ

Reputation: 734

Running count of assigned tasks

I am working on an MVC EF site that has a SQL DB with two tables: Entries, Members.

Moderators of the site can assign tasks to Members which will be logged in Entries.

I'm trying to determine a way to prioritize the Members list so that the Member with the least amount of tasks (as an always-incrementing number) will always OrderBy from the top so that the Moderators can assign based on who has taken the least amount of tasks.

I explored doing this in the DB itself: I can create a View that will check the Entries against the names on Members and provide a total of how many tasks they have been assigned. However; EF/dbContext doesn't work with SQL Views apparently.

I have an EntryController which feeds the Entries Table into the View and a MemberController which simply lists the members. My next thought is to simply call an action in the MemberController that increments a specific Member's count number when called. I'm not sure if this is the best way to do this or how I can even call both the Input-Submit POST from the Html.BeginForm and the Increment function at the same time.

The BeginForm is on a strongly-typed view of the EntryController so I'm also not sure how I could pass a Member back to the member controller so instead I made the function to identify the member based on a string grab .first and increment:

    public void incrementCount(string member)
    {
        Member[] members = null;
        members[0] = (repository.Members.Where(p => p.Name == member).First());
        members[0].Count = members[0].Count + 1;
    }

I am completely lost at this point so any suggestions would be greatly appreciated.

Upvotes: 0

Views: 108

Answers (2)

Anderson Matos
Anderson Matos

Reputation: 3147

Sounds to me that you're lost on all 3: EF, ASP.NET MVC and Linq.

IMHO you should send a HTTP POST or a HTTP PATCH (depends on both context & interpretation) request with jQuery (or other) back to your server/controller which will then increment the task count for the member.

Your controller would, then, have a method Increment(int memberId) with a route like [Route("/lorem/ipsum/members/increment/{id}")] so you can access it from client-side.

This is an interesting approach rather than a full form post to the server because you can send only relevant data (only the memberId instead of the whole form and receive nothing, instead of a whole new page), reducing server load and increaing performance.

Now, for the link, you can either use the regular syntax as Brendan posted ot the Linq bellow, which should be fine too:

var memberId = repository.Entries
   .GroupBy(_entry => _entry.MemberId)
   .Select(_group => new { Id: _group.Key, Count: _group.Count() })
   .OrderBy(_group => _group.Count)
   .First().Id;

Some samples to illustrate possible approaches

First, this will be our controller/method on ASP.NET MVC:

[RoutePrefix("Lorem/Ipsum/Task")]
public class LoremController : Controller
{
   [Route("Increment"), HttpPost]
   public JsonResult Increment(int id)
   {
      try
      {
         // Increment your task count!
         return Json(new { Success = true, ErrorMessage = "" });
      }
      catch(Exception err)
      {
         return Json(new { Success = false, ErrorMessage = err.Message });
      }
   }
}

Sample with jQuery

<div class="blabla">
   <button id="btnIncrement" class="btn btn-primary" type="button" data-member-id="1">
      Increment!
   </button>
</div>

<script>
   $("#btnIncrement").on("click", function() {
      var _this = $(this);
      $.ajax({
         url: "/lorem/ipsum/task/increment",
         data: { MemberId: _this.data("member-id") },
         method: "POST",
         success: function(json) { 
            if (json.Success) alert ('Success!');
            else alert(json.ErrorMessage);
         },
         error: function () { alert("error!); }
      });
   });
</script>

Sample using a simple form (non-jquery/javascript)

<form action="/lorem/ipsum/task/increment" method="POST">
   <input type="hidden" name="MemberId" value="1" />
   <button type="submit" class="btn btn-primary">Increment!</button>
</form>

Sample with Angular.JS

<div ng-app>
   <button ng-controller="lalaController" ng-click="increment(1)" class="btn btn-primary" type="button>
      Increment!
   </button>
</div>

<script>
   angular.controller("lalaController", ["$scope", "$http", function($scope, $http) {
      $scope.increment = function(id) {
         $http({ url: "/lorem/ipsum/task/increment", data: { MemberId: id }, method: POST })
            .success(function(json) {
               if (json.Success) alert ("Success!");
               else alert(json.ErrorMessage);
            })
            .error(function() { alert("Error!"); });
      }
   }]);
</script>

Sure sounds much more interesting on going with pure HTML.

But now, try to disable that button unless it's a prime number. It will be impossible with pure HTML. Also, try to validate your input or parse the response (let's say: udpate your grid on the page with a JSON array of new values)...

Upvotes: 1

Brendan Green
Brendan Green

Reputation: 11944

You can do this via Linq:

    var query = from e in repository.Entries
        group e by e.MemberId into g
        select new
        {
          name = g.Key,
          count = g.Count()
        };

Will return a list of member id's along with the number of Entry records they have.

Upvotes: 1

Related Questions