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

Question

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

Answer 1

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