How to execute SQL query in Entity Framework Core without modifying context class

Question

I am working on a ASP.net Core project. In that project, I need to execute an SQL string query and retrieve the result to a custom model. I tried to retrieve the query result using context.Database.SqlQuery<ReportModel>. But Database.SqlQuery is not supported in ASP.net Core. Please find my code given below:

        public List<ReportModel> GetReportDetails(string customQuery)
        {
            try
            {
                List<ReportModel> listReportModel = new List<ReportModel>();
                var queryResult = context.Database.SqlQuery<ReportModel>(customQuery);
                foreach (var item in queryResult)
                {
                    listReportModel.Add(item);
                }
                return listReportModel;
            }
            catch (Exception ex)
            {
                throw;
            }    
        }

I also tried with the following answer https://stackoverflow.com/questions/35631903/raw-sql-query-without-dbset-entity-framework-core But, using this solution I need to update my context class. I can't update my context class.

Is there any other way to execute the SQL string query in ASP.net Core 3.1? Any help would be appreciable. Thank You!

Look at my answer further down in then linked question – ErikEJ Jun 24 '20 at 14:23 — ErikEJ, Jun 24 '20 at 14:23

score 1 · Answer 1 · answered Jun 24 '20 at 13:45

1

You can use the code below.

var dataFromDB = context.ModelClass.FromSqlRaw("spName {0}", parameter).ToList();

answered Jun 24 '20 at 13:45

Vivek Nuna

25,472
25
109
197

As i understand OP does not have `ReportModel` (or your `ModelClass`) set up in his context. – Guru Stron Jun 24 '20 at 14:03
I tried this already. Actually, 'ReportModel' is a custom class and it is not in the context. – Vignesh VS Jun 24 '20 at 14:17
this can help you https://stackoverflow.com/a/42932812/6527049 – Vivek Nuna Jun 24 '20 at 14:37

score 1 · Answer 2 · answered Jun 24 '20 at 14:40

In your comment, you said that you are getting an error:

Cannot create a DbSet for 'ReportModel' because this type is not included in the model for the context

The error simply is because the class you're using here ReportModel has apparently not been added to your context. You must write this:

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

When you write

var dataFromDB = context.ReportModel.FromSqlRaw("spName {0}", parameter).ToList();

you are querying it indirectly on the table. Hence It is not possible to execute query without modifying the context class. Context class becomes the helper to query your data from database.

score 1 · Answer 3 · answered Jun 25 '20 at 06:56

You can use ADO.NET via the Context.Database property.

https://www.learnentityframeworkcore.com/raw-sql#leveraging-ado.net-via-the-context.database-property

And here is a concrete implementation.

https://github.com/dotnet/efcore/issues/2344#issuecomment-172641417

How to execute SQL query in Entity Framework Core without modifying context class

3 Answers3