MVC Entity Framework Random SQL Queries that don't map to db objects and may return multiple datasets.

Question

ASP.NET (NOT CORE) MVC

1. I'm storing a list of random queries in a table.
2. NONE of the queries match any database object, like Customers, Orders, etc.
3. Some queries may return more than one dataset.
4. Some may be update or delete queries

I need to execute ANY random query with any number of parameters, and return the results in a page.

so this might be a query...

select Firstname, lastName from customers where State=@state

or this...

select T.ID, T.Transactiondate, T.amount, C.FullName, C.City, C.State
from transactions T
join customers C on C.ID=T.CustomerID
where
T.transactionDate between @d1 and @d2

or this (multiple answers)

select * from customers;
select * from orders;

So the code I've seen so far won't compile, as this...

			List<string> parms = Common.ExtractSQLParams(query.SQL);
			List<object> paramset = new List<object>();
			foreach (string parm in parms)
			{
				string paramname = string.Format("@{0}", parm);
				string paramval = Request.Form[parm];
				paramset.Add(new SqlParameter(paramname, (object)paramval));
			}
			DbSqlQuery qry = db.Database.SqlQuery(query.SQL, paramset.ToArray());  // ERROR HERE

gives

Error CS1503 Argument 1: cannot convert from 'string' to 'System.Type' Error CS1503 Argument 2: cannot convert from 'object[]' to 'string'

I know I could do something like this with SQLDataReader, but with EF? Can this be done?

Answer 1

I know I could do something like this with SQLDataReader, but with EF?

EF/LINQ does not populate multiple separate objects. It will populate navigation properties though.

Just use ADO.NET since you already know how to handle this in ADO.NET.

Answer 2

the goal of EF was to return strongly typed objects from queries. if you want dynamic query results use a DataTable or DataReader via adodb.