2

I am using ASP.NET MVC with EF 5.0. I am trying to create a dynamic query and execute it using EF.

What I tried

var sConnection = ((SqlConnection)DbContext.Database.Connection);
sConnection.Open();

Thought I will take the EF connection and use that and execute the query and get result in DataTable.

But I saw other option of 

DbContext.Database.SqlQuery    //But I need to have Entity Type to get the results
DbContext.Database.ExecuteSqlCommand

Is there a way I can execute and get results to DataTable without using my way of getting connection object ?

Any other alternative and best approach wil be useful,

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Peru
  • 2,871
  • 5
  • 37
  • 66
  • 1
    **NO**. The **whole point** of using an ORM (object-relational mapper) like Entity Framework is that you **don't want** to get back just untyped rows and columns from the database in a `DataTable` - instead, you want to get **a .NET object** (or a collection thereof). – marc_s May 17 '13 at 10:11
  • 1
    @marc_s In my application all other scenarios am mapping to Entities but one dynamic search i cant map to entities as the data comes all over database.So thought this would help! in case i need to map to DataTable what is the best approach ? – Peru May 17 '13 at 10:14
  • It should be possible to retrieve the data you need using the navigation properties of the EF entities. If you post an outline of your DB structure and the models, you'll get guidance on how to go about it. – Vimal Stan May 17 '13 at 10:27
  • 1
    Thing is my search may include my entire database 40 tables or even 1 table as searched by user. So thought DataTable will help! yes i can go for ADO.NET but this is the only place am not mapping to Entities. So want to know best approach for my question and posted here ! – Peru May 17 '13 at 10:32
  • In my personal opinion, this is a horribly bad design. I wouldn't do anything like that. You need to know what you're searching for, so you ought to be able to map it to a clear .NET entity as the result type – marc_s May 17 '13 at 11:00
  • Agree @marc_s.i know what am searching for it may be 200 fields or 1 field.So i don want to create a model on that.so tat for this approach But is there a performance impact apart from Design ? – Peru May 17 '13 at 11:05
  • [This answer](http://stackoverflow.com/questions/14733047/entity-framework-code-first-configure-mapping-for-sqlquery) deals with store procedures but the principle is the same. You should be able to map the results of your query to a POCO following the same steps – Vimal Stan May 17 '13 at 11:06

0 Answers0