1

I have a native sql query which changes dynamically based on user selection.
Code

var sql = "select * from " + temp + ";";
var templist = db.Database.SqlQuery<>(sql).ToList();

The temp variable contains table name which is obtained based on user selection. how can i fetch the records without using entity class in between.sqlquery<>?. Is there any other way to fetch records?.

Note: Dynamic tables are not available in entity model.It is only available in db

Vikas Sawant
  • 43
  • 1
  • 10
  • I think you'll have to specify *something* but you could consider using `SqlQuery` if that's appropriate. – Jeroen Vannevel Jun 11 '15 at 01:44
  • 1
    Your task doesn't need EF, you should use ExecuteReader from ADO.NET. – rnofenko Jun 11 '15 at 01:51
  • At some point you have to know which columns you got...dynamic is a good suggestion, but is there no way to avoid having to concatenate a query? I would maintain a mapping of the keys to entities, then use the mapping to get my dbset. – Jason W Jun 11 '15 at 02:10
  • And let's say for a second that you get this to work? How were you planning on using `templist` if you have no idea what you are getting back? If you have a good answer to that question, you may get a more accurate suggestion. – sstan Jun 11 '15 at 02:11
  • @sstan i would use the result to databind a datagrid or would use a viewbag and use a foreach and display the records – Vikas Sawant Jun 11 '15 at 02:52
  • 1
    It's the "display the records" part that @sstan is questioning. How in your view will you know what columns to display? A better approach might be to strongly type your view to an interface that is implemented by POCO classes corresponding to your different selections and have your data access layer of the app use SqlQuery to the POCO class based on selection. Finally have controller pass POCO class to view.Then develop DisplayTemplates for each of the POCO classes and call @Html.DisplayFor(m => m) from strongly-typed view with interface model. – Jason W Jun 11 '15 at 04:16
  • @ jason W, will definitely try your approach, but can we use a datagrid and display the records. i.e the way we use in windows form using oledb collection and then using FILL(). – Vikas Sawant Jun 12 '15 at 04:30

1 Answers1

0

Only substitution classes

class A1 {public int Id {get; set;}}   
class A2 {public string Name {get; set;}}  
...

var templist = db.Database.SqlQuery<A1>(sql_dinamic).ToList(); 
or 
var templist = db.Database.SqlQuery<A2>(sql_dinamic).ToList();

Read the class factory!

ILya Kuzmin
  • 85
  • 1
  • 1
  • 9