CODEWITHSUNDEEP
c#sqlasp.netsql-serverentity-framework
David
David

Reputation: 235

Entity Framework Raw SQL query selecting unknown columns (unknown return type)

I'm having trouble running raw sql against an entity dataset when I dont know the type that needs to be returned.

The scenario is that the page generates the sql on the fly, based on the options the user selects (Building both the 'Select' and 'Where' part of the statements) then tries to pull them using:

        string sSQL = "SELECT " + sSelect + " FROM dbo.Staff ";

        if (!string.IsNullOrWhiteSpace(sWhere)) { sSQL += "WHERE " + sWhere; }

        DAL.AcdmContext ds = new DAL.AcdmContext();
        var resultSet = ds.Database.SqlQuery(sSQL).ToList();

It seems to be that it can't use an anonymous type for the resultset. The problem is that because the select statement is generated on the fly, I can't generate a class or similar for the resultset beforehand - there's too many combinations.

I can pull a query of every column, or with one column by

        var resultSet = ds.Database.SqlQuery<string>(sSQL).ToList();

but I cant find a way to do it with any other number of columns - i've tried as <string[]> and IEnumerable<string>

My last thought is to pull the whole set of columns and filter it to what I need after the fact, but that just seems wasteful. Is there a better solution?

(Oh, and before anyone says, I know I should be using params in the query - I temporarily took them out to try and get this working)

Thanks!

Upvotes: 2

Views: 5429

Answers (1)

JoaoFSA
JoaoFSA

Reputation: 267

There maybe a possible solution to do the query the way your trying to do http://www.codeproject.com/Articles/206416/Use-dynamic-type-in-Entity-Framework-SqlQuery

But even if it works i seriously advise against it, building a query like that is horrible even if you use params. Much better to just map the entity normally and use LINQ to Entities to generate the query for you.

Yes it will bring columns you don't need but unless you are selecting many thousands or millions of records it probably wont have much of an impact on performance and it will much better (and much more maintainable) code and linq to entities will, if I'm not mistaken, take care security problems like Sql Injection

Upvotes: 1

Related Questions