Cannot retrieve data from MS SQL Server using EF Core

Question

I'm using ASP.NET Core 3.1, SQL Server 18, ASP.NET Identity 3.1.9 and EF Core 3.1.9 for a project I'm doing for my University course. For my project I have taken code first approach to create models for database. One of the models is Consultation.

Consultation.cs :

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Runtime.Serialization;
using System.Threading.Tasks;

namespace National_Healthcare_System.Models
{
    public class Consultation
    {
        [Key]
        [Display(Name = "Consultation ID")]
        public Guid Consultation_Id { get; set; }

        public Guid Doctor_Id { get; set; }

        [Required]
        [Display(Name = "Date and Time")]
        public DateTime Consultation_DateTime { get; set; } = DateTime.Now;

        [Display(Name = "NID/Birth Certificate No.")]
        [StringLength(13, ErrorMessage = "NID/Birth Certificate Number is Invalid Size", MinimumLength = 10)]
        public string Identity_Number { get; set; }

        [Required]
        [Display(Name = "Comment")]
        public string Comment { get; set; }

        [IgnoreDataMember]
        public virtual Doctor Doctor { get; set; }

        public virtual Users Users { get; set; }
    }
}

I have CRUD Pages for this Model. Now, in the "Index.cshtml" page, I want to show the consultations that only the "current user" only. "Identity_Number" is the FK in Consultation table to establish relation with the Users table. The tutorials I followed generated the CRUD pages automatically and I also looked for some more materials in the internet but they weren't that helpful for me.

The automatically generated Index.cshtml.cs :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using National_Healthcare_System.Data;
using National_Healthcare_System.Models;

namespace National_Healthcare_System.Pages.Consultations
{
    public class IndexModel : PageModel
    {
        
        private readonly ApplicationDbContext _context;

        public IndexModel(ApplicationDbContext context)
        {
            _context = context;
        }

        public List<Consultation> Consultation { get; set; }

        public async Task OnGetAsync(Guid id)
        {
            Consultation = await _context.Consultation.ToListAsync();
        }
    }
}

I tried to modify it so I can achieve the data for the Current User only.

Modified Index.cshtm.cs :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using National_Healthcare_System.Data;
using National_Healthcare_System.Models;

namespace National_Healthcare_System.Pages.Consultations
{
    public class IndexModel : PageModel
    {
        private readonly ApplicationDbContext _context;
        private readonly UserManager<IdentityUser> _userManager;
        private readonly SignInManager<IdentityUser> _signInManager;

        public IndexModel(UserManager<IdentityUser> userManager,
                          SignInManager<IdentityUser> signInManager,
                          ApplicationDbContext context)
        {
            _userManager = userManager;
            _signInManager = signInManager;
            _context = context;
        }

        #nullable enable
        public List<Consultation> Consultation { get; set; }

        public async Task OnGetAsync(IdentityUser user)
        {
            var userFromDb = await _context.User.FirstOrDefaultAsync(u => u.Email == user.Email);
            try 
            { 
                Consultation = await _context.Consultation.Where(c => c.Identity_Number == userFromDb.Identity_Number).ToListAsync();
            }
            catch { Exception ex; }
        }
    }    
}

The code without Try Catch throws this error:

NullReferenceException: Object reference not set to an instance of an object. lambda_method(Closure)

InvalidOperationException: An exception was thrown while attempting to evaluate a LINQ query parameter expression. To show additional information call EnableSensitiveDataLogging() when overriding DbContext.OnConfiguring. Microsoft.EntityFrameworkCore.Query.Internal.ParameterExtractingExpressionVisitor.GetValue(Expression expression, out string parameterName)]

Screenshot: [1]

Tried #nullable enable as I thought it was returning exception in case the consultation table was empty, but later when I entered data into table, it was still the same.

I wanted to read current user using Identity core. I don't really know if it works that way and also how can I achieve my goal of reading Current Users Consultation Data only? I'm learning C#, ASP.NET Core and Entity Framework recently. So don't know much about them, tried these modifications just out of the blue.

Answer 1

Thanks to @PaulCarlton and @Romka, I now know where was the problem. Helped me to find solution to this problem. I had to modified my code further:

public async Task OnGetAsync()
        {
            //this line helped to get current users data from DB
var user = await _userManager.GetUserAsync(HttpContext.User);
            var userFromDb = await _context.User.FirstOrDefaultAsync(u => u.Email == user.Email);
                     Consultation = await _context.Consultation.Where(c => c.Identity_Number == userFromDb.Identity_Number).ToListAsync();  
        }

This thread was helpful: stackoverflow: How to get current user in asp.net core