How I can use an already existing table in SQL Server with ApplicationDbContext.cs

Question

I am using Blazor, EF Core, NET 6, SQL Server.

I created one database and a table in SQL Server LocalDB (DB: MyCompany, Table: SalaryTax_2023)

SalaryTax_2023 has the tax amount of money according to each salary.

And I set up an ApplicationDbContext.cs and Program.cs, appsettings.json and all (ApplicationDbContext doesn't yet have any DbSet).

I created a method to get the salarytax with user input variables:

result = await dbContext
                    .SalaryTax_2023
                    .Where(row => row.min <= salary && salary < row.max)
                    .Select(row => new TaxResult { TaxAmount = row[columnIndex] })
                    .FirstOrDefaultAsync();

But I can't access SalaryTax_2023. It might I tried to access to a database without DbSet.

As far as I know, I can use it because its table already had schema and data itself. How can I access for this case?

I have learned that migration & update-database command is for the synchronization between EF Core and Blazor.

I hope I could get the clue or answer how I can solve this problem.

Answer 1

You can execute raw SQL queries to interact with the table directly. This allows you to execute SQL queries and map the results to C# objects. Check this out:

using var dbContext = new ApplicationDbContext();

var salary = 50000; // The salary you want to query for
var query = $"SELECT * FROM SalaryTax_2023 WHERE min <= {salary} AND max > {salary}";
var result = await 
dbContext.SalaryTax_2023.FromSqlRaw(query).FirstOrDefaultAsync();

or, you can create a db set:

public DbSet<SalaryTax_2023> SalaryTax_2023 { get; set; }

This DbSet doesn't require migrations since it represents an existing table

var result = await dbContext
.SalaryTax_2023
.Where(row => row.min <= salary && salary < row.max)
.Select(row => new TaxResult { TaxAmount = row[columnIndex] })
.FirstOrDefaultAsync();

Hope this helps