C# – Error: The cast to value type ‘System.Int32’ failed because the materialized value is null

asp.net-mvccentity-frameworklinq

I had a problem with

The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

I have looked up for the answer that will fix the issue that i have, but couldn't find the similar case.

I am trying to find out the max transaction id in the tblTransactions from the account id as input. If the account doesn't have any transactions,it will cause the error. The relationship between those two tables as tblTransaction. accountId = tblAccount.Id

my Controller:

//GET: Balance

    [Authorize]
    public ActionResult Index()
    {
        string useracc = (string)Session["AccountNumber"];

        var accountInstance = db.Accounts.FirstOrDefault(w => w.AccountNumber.ToString() == useracc);


        List<Transaction> AccountTransactions = db.Transactions.Where(w => w.AccountId == accountInstance.Id&&w.IsCancelled ==false).Select(w => w).OrderByDescending(w => w.Date).ToList();

            var accountStatement = AccountTransactions.Where(w => w.TransactionTypeId == 2 && w.Date.Year >= 2015).OrderByDescending(w => w.Date).ToList();

            var lastTransactionId = db.Transactions.Where(w => w.AccountId == accountInstance.Id && w.IsCancelled == false && w.TransactionTypeId == 2 && w.Date.Year >= 2015).Max(t => t.Id);

            var needDueDate = db.SystemInvoices.Where(s => s.Id == lastTransactionId).Select(s => s.DueDate).FirstOrDefault();



            List<customBalanceInfoItem> currCustomBalance = new List<customBalanceInfoItem>();

            customBalanceInfoItem displayItem = new customBalanceInfoItem();

            displayItem.AccountNumber = accountInstance.AccountNumber;
            displayItem.DueDate = needDueDate;

            currCustomBalance.Add(displayItem);
            return View(currCustomBalance);
        }

the error happened on the var lastTransactionId.

Best Answer

Modify it using DefaultIfEmpty(), check here

Modified Query

var lastTransactionId = db.Transactions.Where(w => w.AccountId == accountInstance.Id && w.IsCancelled == false && w.TransactionTypeId == 2 && w.Date.Year >= 2015)
                                       .Select(t=>t.Id)
                                       .DefaultIfEmpty(-1)
                                       .Max()

You can define the value need to returned, if the collection is empty, like I have made it -1, else it will be default value