How to write raw sql query with custom model in entity framework core

Define a class that represents the structure of the result set from your SQL query. Make sure the properties in this class match the columns returned by your query.

public class CustomModel
{
    public int Id { get; set; }
    public string Name { get; set; }
    // Add other properties as needed
}

2.Execute Raw SQL Query: 

Use FromSqlRaw or FromSqlInterpolated to execute your raw SQL query. The result will be mapped to your custom model. 

var customModels = context.CustomModels.FromSqlRaw("SELECT Id, Name FROM TableName WHERE Condition").ToList(); 

Replace TableName and Condition with your actual table name and condition. Mapping Result to 3.Custom Model: 

If the columns in your SQL query do not exactly match the properties in your custom model, you can use the Column attribute to map them explicitly.

public class CustomModel
{
    [Column("ColumnFromSQL")]
    public int Id { get; set; }

    [Column("AnotherColumnFromSQL")]
    public string Name { get; set; }
}

Update the Column attributes with the actual column names from your own SQL query. 

If your own query involves parameters, use the FromSqlInterpolated method to pass parameters safely.

string condition = "Condition";
var customModels = context.CustomModels
    .FromSqlInterpolated($"SELECT Id, Name FROM YourTable WHERE Column = {condition}")
    .ToList();

Ensure that you use parameterized queries to prevent SQL injection. Remember to handle the SQL query and results carefully, especially if they involve user input, to prevent security issues like SQL injection. 

Also, consider the performance implications of raw SQL queries compared to using Entity Framework's LINQ queries.