C# Entity Framework text SQL query wrapper

Question

I have a problem similar to this: How to retrieve multiple columns from non-entity type sql query?

I need to implement the method string[,] DirectQuery(string sqlText, string[] param) which is basically a C# equivalent of SQL Server Management Studio.

The user is supposed to enter a SQL query as string text (+ string parameters to avoid SQL injection) and receive back a string matrix containing the outcome of the query.

Internally, I'm using Entity Framework.

Here's my implementation:

public string[,] DirectQuery(string sqlQuery, string[] param)
{
        //discover how many fields are specified in the select clause
        string ip = sqlQuery.ToLower().Split(new string[] { "from" }, StringSplitOptions.None)[0];
        int cols = ip.Count(y => y == ',') + 1;

        //execute the query
        DbRawSqlQuery<string> res = param != null ? _context.Database.SqlQuery<string>(sqlQuery, param) : _context.Database.SqlQuery<string>(sqlQuery);

        //wrap everything in a matrix to return
        return res.ToArray().Array2Matrix(res.ToArray().Length /cols, cols);
    }

where

public static T[,] Array2Matrix<T>(this T[] flat, int rows, int cols) where T : class

is my custom method that turns flat arrays into rows x cols matrices.

If in the select clause users specify a single attribute, that works fine, but in case of 2+ fields needed the execution of DirectQuery fires the runtime exception dbrawsqlquery he data reader has more than one field. Multiple fields are not valid for EDM primitive or enumeration types. That's completely reasonable, but since the query can be whatever I can't create a custom class to wrap every possible outcome.

What do you suggest?

If you really want to allow users to enter raw SQL queries and then execute them on their behalf, EF is probably not the right tool for this. You should check out something like Dapper.NET (which powers this website, for instance) that easily allows execution of raw SQL and turns the results into nice .NET objects to work with — marc_s
– marc_s, Commented Oct 20, 2017 at 9:21
thanks a lot, it is working quite well. But I'm still encountering some issues with the parameters. Users specify a certain number of parameters inside their queries, naming them as they prefer (@p0, @customerId, @foo, ...), and pass their respective values in a string[]. Dapper fires exception if I pass to the Query method directly the params string[]; on the contrary, it requires a structure like new { p0 = "17R13DT_GP_02_MP2", ... }. Is there a more compact way to do so? — jjhorn
– jjhorn, Commented Oct 20, 2017 at 10:07

GPW · Accepted Answer · 2017-10-20 09:30:37Z

0

The problem is that you're using a method - DbRawSqlQuery which must be told what type to expect, and you're telling it to expect just a string, so it has no idea what to do with more than one returned column.

Maybe it would work if you specified string[] or IEnumerable<string> or something? Alternatively you could define a series of objects with 1, 2, 3, 4 etc values and detect the number of items at runtime and use the correct class... but that seems absurd.

Really though, I'd suggest NOT using EF as someone suggested above. Find something which can return dynmamic objects, OR just use ADO.Net directly.

answered Oct 20, 2017 at 9:30

GPW

2,6362 gold badges13 silver badges22 bronze badges

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

2 Comments

jjhorn Over a year ago

is it possible to create runtime objects with 1, 2, 3, 4 etc values using Reflection?

GPW Over a year ago

Possible? maybe. but by creating the type at runtime, you'd then only be able to access the properties at runtime too, so you'd basically throw away any compile-time type checking abilities... what you'd basically have created is an array/list/table type thing that uses reflection to access its members so would no doubt be horribly inefficient compared to just using an array. I'd seriously not try to reinvent the wheel - just use the types that are already there & go straight to ADO.Net and use a DataTable.

Collectives™ on Stack Overflow

C# Entity Framework text SQL query wrapper

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related