2

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?}");
        });
    }
}
5
  • Code first approach, right? Are you sure you have applied your migrations? Maybe your DB is still empty. Commented Jun 27, 2021 at 22:49
  • Check you db data, whether all the fields having data for the fields which are marked as [required] in your entity model. Commented Jun 28, 2021 at 2:39
  • You may be trying to read the response, before it has had time to fetch it from the database. You may need to await the response and put it into a variable, e.g. var userLink = await db2.UserLinks.FindAsync(user.Id).Link (maybe it's not syntactically correct, but you get the idea: await the response and FindAsync). Then afterwards: RedirectToAction(userLink); Commented Jun 28, 2021 at 6:19
  • If the data is Null, you cannot call methods that contain specific types, because they involve type conversion internally. Solution: You can call IsDBNull first to check whether the data is Null or directly specify that the database field cannot be Null, which is fundamentally eliminated Null value. Commented Jun 28, 2021 at 7:59
  • Thank you for your suggestions, I checked my database and made sure that I do have data in dbo.UserLinks and that the "Link" field in the table cannot be null. Unfortunately, this didn't help and I still have the same problem with the same error message. Commented Jun 28, 2021 at 9:51

2 Answers 2

1

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

Instead find() use

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

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

2 Comments

Tried that approach, but still getting the same error message. But thanks for the help regardless.
First of all, Find() method can return null value (for any reason) so you have to check if it is not null. Find method Or use null proppagation operator with default value (page) RedirectToAction(db2.UserLinks.Find(user.Id)?.Link??"defaultPage")
0

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; }

1 Comment

I am happy with this. Please also check this site Best way to store password in database.It will help you to change user query u => u.Email == model.Email && u.Password == model.Password

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.