C# EF Core Procedure use with Class

Question

I tried to use EF Core to execute a procedure, so I define a class as ACT, then use ACT class to catch procedure output, but I got this error:

Return : The required column 'A02' was not present in the results of a 'FromSql' operation.

I already know class fields and procedure have match field, but I have many procedures to use, I do I have to create a new class for each procedure ?

ACT.cs

namespace E-shop.Shared
{
    public class ACT
    {
        [Key]
        public string A01 { get; set; } = string.Empty;
        public string S01 { get; set; } = string.Empty;
        public string A02 { get; set; } = string.Empty;
        public string A03 { get; set; } = string.Empty;
        public string A04 { get; set; } = string.Empty;
        public string A05 { get; set; } = string.Empty;
        public int A06 { get; set; }
        public int A07 { get; set; }
        public int A08 { get; set; }
        public DateTime A09 { get; set; }
        public int A10 { get; set; }
        public int? A11 { get; set; }
        public int? A12 { get; set; }
        public int? A13 { get; set; }
    }
}

Procedure

ALTER PROCEDURE [dbo].[usp_Sel_SAcc1] 
    (@A01 Varchar(50))
AS
    SELECT A01 
    FROM ACT 
    WHERE A01 = @A01

The method

public void CheckAccount (string Email)
{
    string ProcdureName = "usp_Sel_SAcc1";

    var parameter = new SqlParameter("@A01", Email);

    var user = Model1_context.ACT
        .FromSqlInterpolated($"EXECUTE {ProcdureName} {parameter}")
        .ToList();

    Console.WriteLine(user.ToString());
} 

=======Update 2023/02/13 =========

Thanks every one reply!

I think I need to describe the problem in more detail!!

I got many procedure in our-project Sql, like picture at under.

Every procedure select data is not the same, But it most of use ACT Class, ACT Class is the class corresponding to the database table.

And that procedure most of them also use this table, So I wanna find a way to just use one class to catch many procedure return.

procedures example:

SACC3

ALTER Procedure [dbo].[usp_Sel_SAcc3]

    (
        @A01 Varchar(50)
      
    )

AS
SELECT ACT.A04,ACT.A05,ACT.A07,ACT.A09,ACT.A03,Store.S02,Store.S09,Store.S10,Store.S13,ACT.A06,ACT.S01 FROM ACT INNER JOIN Store ON ACT.S01 =Store.S01 WHERE ACT.A01 = @A01

SAcc7

ALTER Procedure [dbo].[usp_Sel_SAcc7]

(
    @S01 Char(8),
    @A01 Varchar(50)
)

AS
SELECT A03,A04,A05 FROM ACT WHERE S01 = @S01 and A01=@A01

Answer 1

Why would you have a stored procedure that returns one column from a table, selecting by that same column? If it is more a case of selecting a record using the AO1 value then the Stored Proc would be more like:

CREATE PROCEDURE [dbo].[usp_SelectBySAcc1] 
    (@A01 Varchar(50))
AS
    SELECT A01, A02, A03, S01, ... /* fill in all applicable fields */ 
    FROM ACT 
    WHERE A01 = @A01

Then EF can populate your desired entity from the result set coming back from the stored proc. With .Net Core if any of those string columns are null-able, you will want to declare the fields as string? to avoid compiler warnings.

Your code will also likely not work as you expect. ToList() intends to load a collection of matching entities. If you are loading an entity by Key and expect just one row (or zero rows if not found) then use Single or SingleOrDefault.

var user = Model1_context.ACT
    .FromSqlInterpolated($"EXECUTE {ProcdureName} {parameter}")
    .Single();

Ultimately the point of EF is to not need to write SQL or stored procedures. Getting an ACT record by A01 is as simple as:

var user = Model1_context.ACT
    .Single(x => x.A01 == Email);

EF will write the SQL needed to fetch the record. If you just want to check if a record exists:

var userExists = Model1_context.ACT
    .Any(x => x.A01 == Email);