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

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
– ErikEJ

2020-06-24 14:23:33 +00:00
Commented Jun 24, 2020 at 14:23 — ErikEJ
– ErikEJ, Commented Jun 24, 2020 at 14:23

Vivek Nuna · Accepted Answer · 2020-06-24 13:45:09Z

1

You can use the code below.

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

answered Jun 24, 2020 at 13:45

Vivek Nuna

30.6k34 gold badges162 silver badges259 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Guru Stron Over a year ago

As i understand OP does not have ReportModel (or your ModelClass) set up in his context.

Vignesh VS Over a year ago

I tried this already. Actually, 'ReportModel' is a custom class and it is not in the context.

Vivek Nuna Over a year ago

this can help you stackoverflow.com/a/42932812/6527049

Rohan Rao · Accepted Answer · 2020-06-24 14:40:16Z

1

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.

answered Jun 24, 2020 at 14:40

Rohan Rao

2,6413 gold badges24 silver badges40 bronze badges

Comments

mj1313 · Accepted Answer · 2020-06-25 06:56:59Z

1

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

answered Jun 25, 2020 at 6:56

mj1313

8,5172 gold badges17 silver badges34 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Linked

Related