CODEWITHSUNDEEP
entity-frameworkasp.net-core
Qiuzman
Qiuzman

Reputation: 1739

How to use data from entity Raw SQL query in entity framework

I will state up front I am still very new to asp.net core and entity framework but I am familiar with ADO and recordset which it seems entity framework is built from. I am struggling to use entity framework because I am able to run the query but I am not sure how to use the results and most help docs I see online only discuss the procedures and methods and not how to use the results. I am building login functionality on my site and have developed the following code to query DB in my UserAccount table. FOr this login I really only want the Username, Password, and the ID however I have multiple fields in this table that I dont need for this aspect. I come from using ADO and recordsets so really I just prefer to use raw sql and determine what i want to do with those results and not bind them to some objects which it seems entity framework does for the most part. I do liek the ease of use of the entity framework and using parameters though so I prefer to just go this route (not sure if this is bad practice or not). Everything works great except when I add ToList it starts to say error "InvalidOperationException: The required column 'AccessLevel' was not present in the results of a 'FromSql' operation." I am not even tryign to use that field in the query so not sure why that is even coming up and I am using a rawsql query method so why is it trying to see what I have in the model for that table? Lastly, this shoudl return a single record in which case I want to pull the password value from that record field similar to how i use to do in ADO as you will see in my passwordhash verification but I cannot figure out how to even pull that from the query result. Thanks for any help on this as I am getting a headache trying to learn this stuff!!

               var UserResults = _context.UserAccounts.FromSqlInterpolated($"SELECT USERACCOUNT.USERNAME, USERACCOUNT.PASSWORD, USERACCOUNT.ID,USERACCOUNT.ACCESS_LEVEL FROM DBO.USERACCOUNT WHERE USERACCOUNT.USERNAME={UserName}");

            if (UserResults.Count() == 1) //if we have more than 1 result we have security issue so do not allow login
            {
                var passwordHasher = new PasswordHasher<string>();
                var hashedPassword = passwordHasher.HashPassword(null, Password);

                var testResults = UserResults.ToList();

                if (passwordHasher.VerifyHashedPassword(null, hashedPassword, THIS SHOULD BE VALUE FROM DB RIGHT HERE!!!) == PasswordVerificationResult.Success)
                {
                    var claims = new List<Claim>
                {
                    new Claim(ClaimTypes.Name, UserName)
                };

My DBContext is as follows:

public partial class LoginDBContext : DbContext
{
    public DbSet<UserAccount> UserAccounts { get; set; }

    public LoginDBContext(DbContextOptions<LoginDBContext> options) : base(options)
    {

    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<UserAccount>(entity =>
        {
            //entity.ToTable("UserAccount");
            entity.HasNoKey();

            //entity.Property(e => e.Id)
            //    .HasMaxLength(50)
            //    .IsUnicode(false);

            //entity.Property(e => e.Username)
            //    .HasMaxLength(50)
            //    .IsUnicode(false);
            //entity.Property(e => e.Password)
            //    .HasMaxLength(50)
            //    .IsUnicode(false);
        });
    }
}

Upvotes: 0

Views: 335

Answers (1)

Fabio
Fabio

Reputation: 32445

If you don't want to load all columns of the user table you can return anonymous object with properties you need or create a class for the columns you need and return it.

var users = _context.UserAccounts
    .Where(a => a.UserName == UserName)
    .Select(a => new { a.Id, a.UserName, a.Password })
    .ToArray();

if (users.Length == 1)
{
    var user = users.First();
    if (passwordHasher.VerifyHashedPassword(null, hashedPassword, user.Password) == PasswordVerificationResult.Success)
    {
        // ... your magic
    }
}

Upvotes: 1

Related Questions