locked
Executing Native SQL query into EF scope to fetch a table result RRS feed

  • Question

  • Hi,

    I would like to execute a native SQL query using my EF context.

    To do that, I've tried to use context.ExecuteStoreCommand and context.ExecuteStoreQuery but I have a problem with the type needed by these functions.

    Imagine I want to execute a dynamic query (stored somewhere and it can do some joins) and I need to get values returned by this query.

    Which type I have to write ? how can I use this ?

    Do you have any clue about my problem please ?

    Thanks

    Steeve

    Wednesday, February 15, 2012 8:25 AM

Answers

  • Hello Steeve,

    you cannot use EF for executing dynamic SQL queries. The only advantage of using ExecuteStoreQuery is that EF will execute your SQL and automatically populate the type you define as a result. It either uses mapped entities or simple classes using properties with same names and types as columns in SQL result set. Because your query is dynamic you don't know in design time what columns result set contains and you cannot use this feature. Either use untyped DataSet and DataAdapter or DataReader to exeucte SQL queries with dynamic result set structure.

    Best regards,
    Ladislav

    • Marked as answer by Allen_MSDN Monday, February 20, 2012 2:22 AM
    Wednesday, February 15, 2012 11:09 AM

All replies

  • Hello Steeve,

    you cannot use EF for executing dynamic SQL queries. The only advantage of using ExecuteStoreQuery is that EF will execute your SQL and automatically populate the type you define as a result. It either uses mapped entities or simple classes using properties with same names and types as columns in SQL result set. Because your query is dynamic you don't know in design time what columns result set contains and you cannot use this feature. Either use untyped DataSet and DataAdapter or DataReader to exeucte SQL queries with dynamic result set structure.

    Best regards,
    Ladislav

    • Marked as answer by Allen_MSDN Monday, February 20, 2012 2:22 AM
    Wednesday, February 15, 2012 11:09 AM
  • David,

    I do believe I understand what you are talking about.  The first thing to do is to think form a c# programmer mind rather than a sql server query builder mindset.  So here is an example of what MAY be the answer to your question.

    Let's say I have a table of People that contains a Name, Address, Phone, and a ton of other stuff.  I also have a state table that translates the two letter code into a full state name.  The user wants to work only with the three items and the full state.

    public class MyClass
    {
    
        using (MyContext context = new MyContext(connectionString))
        {
           var people = from p in context.People
           join s on State in s.StateId == p.StateId
           where .....some condition exists .....
           select new PersonDisplay
               { p.Name, p.Address, p.Phone, s.StateName };
           return people;
        }
    
        ...
    }
    
    public class PersonDisplay
    {
       public string Name{get;set;}
       public string Address{get;set;}
       public string Phone{get;set;}
       public string DisplayStateName{get;set;}
    }

    My join syntax may not be accurate but you get the idea.  This is an example of taking data from two tables and creating a new class from the select statement.  I do recommend that if you want this passed around the network like any other data object, then you should inherit EntityObject, add the necessary methods and decorators, and make certain this is established with DataContract and DataMember attributes as well.

    Wednesday, February 15, 2012 4:31 PM