c# - Writing SQL queries without table access full -
tl;dr i'm using entityframework 5.0 oracle , need query table 2 columns using index nvl of 2 columns.
details after hours of attempts... i'll try organize possible.
the desired sql query should be:
select t.code, nvl(t.local, t.global) description shows t t.code = 123 so problem? if want use context.shows.parts.sqlquery(query) must return whole row(*), table access full, must return desired columns.
the next thing(actually there lot of tries before following...) i've tried gives close results using null-coalescing operator(??) :
context.shows.where(x => x.code == 123) .select(x => new { x.code, description = x.local ?? x.global); but sql it's using complicated using case & when , not using index on code, nvl(local, global) critical!
my next step using database.sqlquery
context.database.sqlquery<tuple<int, string>>("the raw-sqlquery above"); but error tuple must not abstract , must have default ctor(it doesn't).
final step dislike creating class has 2 properites(code, description), now... works great, don't want write class each query that.
ideas?
this no-solution answer.
i think whatever try, can't that. if define own mutable generic tuple, failed since name of property must match name of column:
sqlquery(string, object[]): creates raw sql query return elements of given generic type. type can type has properties match names of columns returned query, or can simple primitive type.
i think best can creating own generic method querying database via classic command , executereader pattern. untested, idea:
public static ienumerable<tuple<t>> sqlquery<t>(this dbcontext context, string sql) { using(var connection = new sqlconnection(context.database.connection.connectionstring)) using (var command = new sqlcommand(sql, connection)) { var reader = command.executereader(); while (reader.nextresult()) { yield return new tuple<t>((t)reader[0]); } } } public static ienumerable<tuple<t1, t2>> sqlquery<t1, t2>(this dbcontext context, string sql) { using (var connection = new sqlconnection(context.database.connection.connectionstring)) using (var command = new sqlcommand(sql, connection)) { var reader = command.executereader(); while (reader.nextresult()) { yield return new tuple<t1, t2>((t1)reader[0], (t2)reader[1]); } } } 
