Error: The cast to value type 'System.Int32' failed because the materialized value is null

Question

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.

what does the last `db.Transactions.Where( ... )` evaluates to? — Amit Kumar Ghosh, Mar 21 '16 at 06:12
I am trying to get the due date of invoice for account. But for some accounts which haven't got invoices yet. when i use those accounts run will cause the error, because in the transactions table couldn't find any accountId. I will list full controller for my balance page with due date. — Felix JIN, Mar 21 '16 at 06:21
just add a check to see if the evaluation is `null` and if not call `max` on it. Displaying an error message for invalid accounts is more proper that to just shadow it. — Amit Kumar Ghosh, Mar 21 '16 at 06:37
Possible duplicate of [The cast to value type 'Int32' failed because the materialized value is null](http://stackoverflow.com/questions/6864311/the-cast-to-value-type-int32-failed-because-the-materialized-value-is-null) — Michael Freidgeim, Jul 13 '16 at 04:26

Mrinal Kamboj · Accepted Answer · 2016-03-21T06:44:52.730

7

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

edited Mar 21 '16 at 06:44

answered Mar 21 '16 at 06:10

Mrinal Kamboj

11,300
5
40
74

I added DefaultIfEmpty(-1), but still query under red line before DefaultIfEmpty(-1). – Felix JIN Mar 21 '16 at 06:16
Add ToList() before it this is a call on List type and you are doing it on IEnumerable type,check edited version – Mrinal Kamboj Mar 21 '16 at 06:21
Sorry , tried still not working ,query under red line before ToList().DefaultIfEmpty(-1).Max(t => t.Id) – Felix JIN Mar 21 '16 at 06:26
Post the exact error message provided by Red line, that's a compilation issue – Mrinal Kamboj Mar 21 '16 at 06:29
Severity Code Description Project File Line Error CS1929 'List' does not contain a definition for 'DefaultIfEmpty' and the best extension method overload 'Queryable.DefaultIfEmpty(IQueryable, int)' requires a receiver of type 'IQueryable' CustomerPortal – Felix JIN Mar 21 '16 at 06:36
Check the modified version, tested on my system, this would surely work, you will get -1 if it is empty or the Max is selected – Mrinal Kamboj Mar 21 '16 at 06:45
Thanks , it works finally. Sorry dont have enough reputation to vote :) – Felix JIN Mar 21 '16 at 06:54
That's fine, good that it worked :), accept this as answer please – Mrinal Kamboj Mar 21 '16 at 06:55

score -2 · Answer 2 · answered Mar 21 '16 at 06:04

-2

try adding first or default which prevents null exception

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

answered Mar 21 '16 at 06:04

REDEVI_

684
8
18

1

Thanks for your solution. But FirstOrDefault() seems cannot apply at the end. – Felix JIN Mar 21 '16 at 06:10
This is failing at Max itself, so FirstOrDefault will not help – Mrinal Kamboj Mar 21 '16 at 06:13
Syntax error. `Max()` already returns a value. Therefore, `FirstOrDefault()` method will not exist on your returned type. – hastrb Oct 13 '20 at 10:21

Error: The cast to value type 'System.Int32' failed because the materialized value is null

2 Answers2