How to write dynamic SQL Queries in Entity Framework?

Question

I have a page where the user can input a SQL query. This query can be against any table in my database. I need to execute this query against the corresponding table and show the result in my view. How to do this?

For eg: user can input Select * from ABC or select max(price) from items.

I tried:

var results = DbContext.Database.SqlQuery<string>(query).ToList();

But this throws an error:

The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types.

Whatever the result I should be able to pass it to the view and display it.

Please help.

var blogs = context.YourModel.SqlQuery("SELECT * FROM dbo.YourTable").ToList(); — Mustafa ASAN
– Mustafa ASAN, Commented Sep 22, 2016 at 11:36
I cannot specify 'myModel' because I am not aware of what the user might input as the query. So whatever be the model I need to get it executed. — Shafeek
– Shafeek, Commented Sep 22, 2016 at 11:40
Letting users execute any SQL directly against the db is probably a very bad idea by the way. — Wurd
– Wurd, Commented Sep 22, 2016 at 12:55

Robert Harvey · Accepted Answer · 2016-12-30 21:26:49Z

1

The error message states the problem exactly:

The data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types.

To correct the problem, you can either submit a SQL string that returns a single string column from the database, or supply a type parameter that allows EF to map multiple columns.

For example:

SELECT someStringColumn FROM ABC

OR

var results = DbContext.Database.SqlQuery<MyDto>(query).ToList();

Where MyDTO looks something like this:

class MyDto
{
    public int AddressID { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Zip { get; set; }
}

answered Dec 30, 2016 at 21:26

Robert Harvey

182k48 gold badges349 silver badges516 bronze badges

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

Comments

srini · Accepted Answer · 2016-09-22 12:05:37Z

0

you can use

 SqlQuery<dynamic>"

this will resolve the error but you will only be able to get the count of the result returned. So you can just verify the query has returned some data. But still will need to know the type of the returned data.

It is a risk of providing the user to input query to database.

edited Sep 22, 2016 at 12:05

answered Sep 22, 2016 at 11:54

srini

1784 bronze badges

Collectives™ on Stack Overflow

How to write dynamic SQL Queries in Entity Framework?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related