Run sql queries in net5 - winform

Question

how can run sql queries in c# win-form and net5, i wana run sql query something like this :

SELECT CAST(FinanceDate AS DATE) AS DATE, SUM(FinancePayment) AS Payment, FinanceType FROM Finances GROUP BY CAST(FinanceDate AS DATE), FinanceType

but i have a problem, in this case and net5 i have two method FromSqlInterpolated(),FromSqlRow() when i use this methods, i have an Exception : The required column 'ID' was not present in the results of a 'FromSql' operation.

code:

var finance = await dbContext.Finances.FromSqlInterpolated($"SELECT CAST(FinanceDate AS DATE) AS DATE, SUM(FinancePayment) AS Payment, FinanceType FROM Finances GROUP BY CAST(FinanceDate AS DATE), FinanceType").ToListAsync();

what can i do?

Answer 1

use DbCommand to execute queries:

mydbcommand.ExecuteReader( "SELECT CAST(FinanceDate AS DATE) AS DATE, SUM(FinancePayment) AS Payment, FinanceType FROM Finances GROUP BY CAST(FinanceDate AS DATE), FinanceType" );

see https://learn.microsoft.com/en-us/dotnet/api/system.data.common.dbcommand.executereader

Answer 2

Used [NotMapped] attribute with ID.

The NotMappedattribute can be applied to properties of an entity class for which we do not want to create corresponding columns in the database.

see this link for more details : The required column was not present in the results of a 'FromSql' operation

Don't forget to include using System.ComponentModel.DataAnnotations.Schema