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.

Is there any data in the database? Make sure the object is not null before trying to extract data from a null object. It doesn't look like the code is finding the user in the database. — jdweng
– jdweng, Commented Dec 23, 2020 at 16:04
Can you see in debugging with a breakpoint at line "Consultation = await _context.Consultation.Where...." if the Email property is null for the object "userFromDb " ? — Romain Lazaud
– Romain Lazaud, Commented Dec 23, 2020 at 16:05
Yes there is. I have inserted data for the current user. The current user's Identity Number is there. But the error is still there. — Farhan Ahmed
– Farhan Ahmed, Commented Dec 23, 2020 at 16:07
Yes "userFromDb" seems to be null. How can I retrieve current users Identity_Number then? Identity_Number is a custom attribute that I made with Users class and Used it with .Net Identity. @Romka — Farhan Ahmed
– Farhan Ahmed, Commented Dec 23, 2020 at 16:24
#nullable enable isn't related to your problem. Nullable context allows for one to design their contracts (interfaces) by explicitly stating an object as nullable or not. It's a design decision. — Paul Carlton
– Paul Carlton, Commented Dec 23, 2020 at 16:38

Farhan Ahmed · Accepted Answer · 2020-12-23 17:22:59Z

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

answered Dec 23, 2020 at 17:22

Farhan Ahmed

215 bronze badges

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

Collectives™ on Stack Overflow

Cannot retrieve data from MS SQL Server using EF Core

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related