c# - The cast to value type System.Boolean failed because the materialized value is null, result type's generic parameter must use a nullable type

Question

This code was working before but now I've got this error: The cast to value type 'System.Boolean' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.

public async Task<ActionResult> BankDepositVoucher(BankDepositVoucherSearchViewModel search, int? PageNo)
        {
            var model = new BankDepositVoucherListViewModel
            {
                Search = search ?? new BankDepositVoucherSearchViewModel()
            };
            if (search != null)
            {
                search.StartDate = search.StartDate.ToStartOfDay();
                search.EndDate = search.EndDate.ToEndOfDay();
            }
            try
            {
                var Vouchers = DbManager.Invoices.Include(x => x.BankDepositVoucher)
                                             .Where(x => x.Type == InvoiceType.BankDepositVoucher
                                             && (x.VoucherNumber == search.VoucherNo || search.VoucherNo == null)
                                             && (x.BankDepositVoucher.SlipNo.Contains(search.SlipNo) || search.SlipNo == null)
                                             && (x.BankDepositVoucher.ChequeNo.Contains(search.ChequeNo) || search.ChequeNo == null)
                                             && (x.BankDepositVoucher.Bank.AccountName.Contains(search.BankDetails)
                                             || search.BankDetails == null)
                                             && (x.BankDepositVoucher.AccountName.Contains(search.AccountName) || search.AccountName == null)
                                             && (x.BankDepositVoucher.Narration.Contains(search.Narration) || search.Narration == null)
                                             && (x.TotalAmount == search.Amount || search.Amount == null)
                                             && (x.Date >= search.StartDate || search.StartDate == null)
                                              && (x.Date <= search.EndDate || search.EndDate == null));
                //model.Pager = new Pager(await Vouchers.CountAsync(), PageNo, 10);
                model.Vouchers = await Vouchers.OrderByDescending(x => x.VoucherNumber)
                                    //.Skip((model.Pager.CurrentPage - 1) * model.Pager.PageSize)
                                    //.Take(model.Pager.PageSize)
                                    .Select(x => new BankDepositVoucherBaseViewModel
                                    {
                                        Id = x.Id,
                                        VoucherNumber = x.VoucherNumber,
                                        AccountName = x.BankDepositVoucher.AccountName,
                                        BankAccountName = x.BankDepositVoucher.Bank.AccountName,
                                        Date = x.Date,
                                        ChequeNo = x.BankDepositVoucher.ChequeNo,
                                        Narration = x.BankDepositVoucher.Narration,
                                        SlipNo = x.BankDepositVoucher.SlipNo,
                                        TotalAmount = x.TotalAmount,
                                        IsCleared = x.BankDepositVoucher.IsCleared
                                    }).ToListAsync();
            }
            catch (Exception ex)
            {
                Console.WriteLine("", ex.Message);

            }
            return PartialView(model);
        }

This is the part throwing above mentioned exception

model.Vouchers = await Vouchers.OrderByDescending(x => x.VoucherNumber)
                                    //.Skip((model.Pager.CurrentPage - 1) * model.Pager.PageSize)
                                    //.Take(model.Pager.PageSize)
                                    .Select(x => new BankDepositVoucherBaseViewModel
                                    {
                                        Id = x.Id,
                                        VoucherNumber = x.VoucherNumber,
                                        AccountName = x.BankDepositVoucher.AccountName,
                                        BankAccountName = x.BankDepositVoucher.Bank.AccountName,
                                        Date = x.Date,
                                        ChequeNo = x.BankDepositVoucher.ChequeNo,
                                        Narration = x.BankDepositVoucher.Narration,
                                        SlipNo = x.BankDepositVoucher.SlipNo,
                                        TotalAmount = x.TotalAmount,
                                        IsCleared = x.BankDepositVoucher.IsCleared
                                    }).ToListAsync();

标签： c#asp.net-mvcentity-framework

Answer 1

The issue is likely that when populating the view model it cannot deal with the fact that a record may not have a BankDepositVoucher.

For instance: IsCleared = x.BankDepositVoucher.IsCleared

This should probably be: IsCleared = x.BankDepositVoucher?.IsCleared ?? false

One other thing to improve performance considerably:

While it may look concise in the code to write statements like this:

.Where(x => x.Type == InvoiceType.BankDepositVoucher
    && (x.VoucherNumber == search.VoucherNo || search.VoucherNo == null)
    && (x.BankDepositVoucher.SlipNo.Contains(search.SlipNo) || search.SlipNo == null)
    && (x.BankDepositVoucher.ChequeNo.Contains(search.ChequeNo) || search.ChequeNo == null)
    && (x.BankDepositVoucher.Bank.AccountName.Contains(search.BankDetails)
        || search.BankDetails == null)
    && (x.BankDepositVoucher.AccountName.Contains(search.AccountName) || search.AccountName == null)
    && (x.BankDepositVoucher.Narration.Contains(search.Narration) || search.Narration == null)
    && (x.TotalAmount == search.Amount || search.Amount == null)
    && (x.Date >= search.StartDate || search.StartDate == null)
    && (x.Date <= search.EndDate || search.EndDate == null));

It is more efficient to write it out as:

.Where(x => x.Type == InvoiceType.BankDepositVoucher);

if(!string.IsNullOrEmpty(search.VoucherNo))
    Voucher = Voucher.Where(x => x.VoucherNumber == search.VoucherNo);
if(!string.IsNullOrEmpty(search.SlipNo))
    Voucher = Voucher.Where(x => x.BankDepositVoucher.SlipNo.Contains(search.SlipNo))
// etc.

The reason is that in the first case you are generating a much larger SQL statement to be sent to the database, and it is quite easy to "slip up" on conditions if that query is ever edited in the future. (missing parenthesis, etc.) The second example only adds conditions to the query if they are needed, keeping the resulting SQL statement much more compact.