How to Use Enumeration within Linq Query to Update Multiple Rows in a Database

Question

I am using ASP.NET MVC 4 and I have a View where I can see a few rows (expenses) and I have a checkbox inputs beside each row. There is a Boolean property called "Submitted" and a DateTime property called DateSubmitted in the model.

I have a method called SubmitExpenses() and I only want the rows that are 'checked' to be updated.

The columns that would be updated are DateSubmitted and Submitted.

Here is my model:

public class Expense
{
    public Expense() { }

    [Key]
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int ExpenseId { get; set; }

    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
    [Display(Name = "Date Submitted")]
    public DateTime? DateSubmitted { get; set; }

    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}")]
    [Display(Name = "Expense Date")]
    public DateTime? ExpenseDate { get; set; }

    [Display(Name = "Submitted?")]
    public bool Submitted { get; set; }

    [Required]
    [Display(Name = "Name")]
    public int UserId { get; set; }
    [ForeignKey("UserId")]
    public virtual UserProfile UserProfile { get; set; }

}

Here is the SubmitExpenses() method:

public ActionResult SubmitExpenses(List<Expense> expenses, DateTime? expenseDate = null, DateTime? expenseDate2 = null, int? userId = 0)
{
    expenseDate = (DateTime)Session["FirstDate"];
    expenseDate2 = (DateTime)Session["SecondDate"];

    if (expenseDate == null || expenseDate2 == null)
    {
        expenseDate = DateTime.Now.AddMonths(-1);
        expenseDate2 = DateTime.Today;
    }

    string currentUserId = User.Identity.Name;

    var query = from e in db.Expenses
                join user in db.UserProfiles on e.UserId equals user.UserId
                where user.UserName == currentUserId && (e.ExpenseDate >= expenseDate && e.ExpenseDate <= expenseDate2) && e.DateSubmitted == null
                orderby e.ExpenseDate descending
                select e;

    if (User.IsInRole("admin") && userId != 0)
    {

        query = from e in db.Expenses
                join user in db.UserProfiles on e.UserId equals user.UserId
                where user.UserId == userId && e.ExpenseDate >= expenseDate && e.ExpenseDate <= expenseDate2 && e.DateSubmitted == null
                orderby e.ExpenseDate descending
                select e;
    }
    else if (User.IsInRole("admin"))
    {
        query = from e in db.Expenses
                join user in db.UserProfiles on e.UserId equals user.UserId
                where e.ExpenseDate >= expenseDate && e.ExpenseDate <= expenseDate2 && e.DateSubmitted == null
                orderby e.ExpenseDate descending
                select e;
    }


    foreach (Expense exp in query)
    {
        exp.DateSubmitted = DateTime.Today;
    }

    try
    {
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    catch (Exception e)
    {
        Console.WriteLine(e);
        return RedirectToAction("Submit");
    }

}

Here is the View:

<table>
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.DateSubmitted)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.ExpenseDate)
        </th>

        @if (User.IsInRole("admin"))
        {
            <th>
                @Html.DisplayNameFor(model => model.UserProfile.UserName)
            </th>
        }
        <th></th>
    </tr>
    <tr>
        <td>
            <b>Select All:</b>
            <br />
            <input type="checkbox" name="expense" value="Expense" id="selectAllCheckboxes" class="expenseCheck">
        </td>
    </tr>
    @foreach (var item in Model)
    {
        <tr>
            <td class="submitCheck">
                @Html.DisplayFor(modelItem => item.Submitted)
            </td>

            <td>
                @Html.DisplayFor(modelItem => item.DateSubmitted)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.ExpenseDate)
            </td>

            @if (User.IsInRole("admin"))
            {
                <td>
                    @Html.DisplayFor(modelItem => item.UserProfile.UserName)
                </td>
            }
        </tr>
    }

</table>

 @Html.ActionLink("Submit Expenses", "SubmitExpenses")

 @section scripts {
<script type="text/javascript">


    $("#selectAllCheckboxes").click(function () {
        $('.submitCheck input:checkbox').not(this).prop('checked', this.checked);
    });

    $('.submitCheck input:checkbox').prop('disabled', false);


</script>

}

I know how to submit all expenses that are in the date range specified (or all expenses that are showing in the view), but I'm not sure how I would update each row for only the rows that are checked.

Thank you.

Gert Arnold · Accepted Answer · 2016-05-13 20:55:39Z

1

First, as an aside, you can use query composition instead of repeating your query each time:

var query = from e in db.Expenses
            join user in db.UserProfiles on e.UserId equals user.UserId
            where e.ExpenseDate >= expenseDate && e.ExpenseDate <= expenseDate2 && e.DateSubmitted == null
            orderby e.ExpenseDate descending
            select new { e, user };

if (User.IsInRole("admin") && userId != 0)
{
    query = query.Where(x => x.user.UserId == userId);
}
else if (!User.IsInRole("admin"))
{
    query = query.Where(x => x.user.UserName == currentUserId);
}

Now for the question: If I understand you correctly you want to filter out the Expenses from the database that were marked as Submitted in the view. These filtered expenses should be updated. A way to do that is by joining the database expenses with the ones from the view:

var joined = from dbExpense in query.Select(x => x.e).AsEnumerable()
             join localExpense in expenses on dbExpense.ExpenseId equals localExpense.ExpenseId
             where localExpense.Submitted
             select dbExpense;

foreach (Expense exp in joined)
{
    exp.DateSubmitted = DateTime.Today;
}

By using AsEnumerable on the database query, you can join it with a local sequence (expenses) as LINQ to objects.

answered May 13, 2016 at 20:55

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

thetipsyhacker Over a year ago

Thank you for the style tips. I didn't know I could shorten my code like that! I was just wondering where the 'e' in 'x.e' comes from.

Gert Arnold Over a year ago

From new { e, user }.

thetipsyhacker Over a year ago

The 'joined' variable is coming back as null. Is there anything I need to do in the view to get variable to be set?

Gert Arnold Over a year ago

joined is null? I can't imagine that, because the result of a LINQ query returning an enumerable is never null. Or is it empty?

Gert Arnold Over a year ago

Isn't expenses null?

|

Collectives™ on Stack Overflow

How to Use Enumeration within Linq Query to Update Multiple Rows in a Database

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related