Having trouble with accessing database table in ASP. NET Core

Viewed 111

I am currently learning ASP. NET Core by writing a web application with cookie authentication. I have two tables in Azure SQL database - Users (containing login and password data) and UserLinks (which contains links used to redirect logged users to a certain page).

The thing is, after logging in, each authenticated user is supposed to be redirected to a page that is unique to that user (link to this page is stored in UserLinks table in database). I tried to implement this by using this line of code:

return RedirectToAction(db2.UserLinks.Find(user.Id).Link);  

This obviously doesn't work and gets me this error message:

SqlNullValueException: Data is Null. This method or property cannot be called on Null values

which means that the Find() method is not appropriate here.

My question is: what is the correct way to address my table to find right UserLinks.Link corresponding to Users.Id?

All relative code (including SQL tables) is below:

Table dbo.Users:

CREATE TABLE [dbo].[Users] (  
    [Id]       INT           NOT NULL,  
    [Email]    NVARCHAR (50) NOT NULL,  
    [Password] NVARCHAR (50) NOT NULL,  
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Table dbo.USerLinks (has link to dbo.Users via foreign key UserID):

CREATE TABLE [dbo].[UserLinks] (
    [Post_ID]   INT            NOT NULL,  
    [UserID]    INT            NOT NULL,  
    [TableName] NVARCHAR (50)  NOT NULL,  
    [Options]   INT            NULL,  
    [Link]      NVARCHAR (MAX) NOT NULL,  
    PRIMARY KEY CLUSTERED ([Post_ID] ASC),  
    UNIQUE NONCLUSTERED ([UserID] ASC),  
    FOREIGN KEY ([UserID]) REFERENCES [dbo].[Users] ([Id])
);

AccountController.cs:

public class AccountController : Controller
{
    private UserContext db;
    private UserLinkContext db2;

    public AccountController(UserContext context, UserLinkContext ulcontext)
    {
        db = context;
        db2 = ulcontext;
    }

    [HttpGet]
    public IActionResult Login()
    {
        return View();
    }

    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> Login(LoginModel model) 
    {
        if (ModelState.IsValid)
        {
            
            User user = await db.Users.FirstOrDefaultAsync(u => u.Email == model.Email && u.Password == model.Password);
            if (user != null)
            {
                await Authenticate(model.Email); 
                
                 return RedirectToAction(db2.UserLinks.Find(user.Id).Link);
 
            }
            ModelState.AddModelError("", "Incorrect login and(or) password");
        }
        return View(model);
    }
 
    private async Task Authenticate(string userName)
    {        
        var claims = new List<Claim>
        {
            new Claim(ClaimsIdentity.DefaultNameClaimType, userName)
        };
        
        ClaimsIdentity id = new ClaimsIdentity(claims, "ApplicationCookie", ClaimsIdentity.DefaultNameClaimType, ClaimsIdentity.DefaultRoleClaimType);
        
        await HttpContext.SignInAsync(CookieAuthenticationDefaults.AuthenticationScheme, new ClaimsPrincipal(id));
    }
 
    [HttpPost]
    public async Task<IActionResult> Logout() 
    {
        await HttpContext.SignOutAsync(CookieAuthenticationDefaults.AuthenticationScheme);
        return RedirectToAction("Index", "Home");
    }
}

Startup.cs:

public class Startup
{
    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }
 
    public IConfiguration Configuration { get; }
 
    public void ConfigureServices(IServiceCollection services)
    {
        string connection = Configuration.GetConnectionString("DefaultConnection");
        services.AddDbContext<UserContext>(options => options.UseSqlServer(connection)); 
        services.AddDbContext<UserLinkContext>(options => options.UseSqlServer(connection));
 
        
        services.AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme)
            .AddCookie(options => //CookieAuthenticationOptions
            {
                options.LoginPath = new Microsoft.AspNetCore.Http.PathString("/Account/Login");   
            });
        services.AddControllersWithViews();
    }
 
    public void Configure(IApplicationBuilder app)
    {
        app.UseDeveloperExceptionPage();
 
        app.UseStaticFiles();
 
        app.UseRouting();
 
        app.UseAuthentication();    
        app.UseAuthorization();     
 
        app.UseEndpoints(endpoints =>
        {
            endpoints.MapControllerRoute(
                name: "default",
                pattern: "{controller=Home}/{action=Index}/{id?}");
        });
    }
}
2 Answers

Find works with primary key, in the table user_links it is post_id column.

Instead find() use

firstOrDefault(x=>x.UserId== user.Id)

I figured it out. The problem was that I mapped my UserLinks table wrong. Since the "Options" column can be null in my example, I shoud've used int? and not just int in my UserLinks.cs file:

    [Key]
    public int Post_ID { get; set; }
    public int UserID { get; set; }
    public string TableName { get; set; }
    public int? Options { get; set; }
    public string Link { get; set; }
Related