none
Using ExecuteStoreQuery method RRS feed

  • Question

  • Hi All,

    I have two tables in two different model (i.e. edmx files) i want to use join to get some data so as per my RnD uptill now there is no such way to do the join between two model, I dont want to use the stored procedure as well so i have decided to use ExecuteStoreQuery.

    I made a query and than execute it with this method and pass my POCO class (i.e. class register with the model in CSDL and SSDL). I also include some additional properties in this POCO that are not known the the Entity Framework so my query will work and maps that data but unfortunately my additional properties that are not register in edm are not mapped.

    Then I again Execute the some query by creating a fresh class not related to EDM in any manner and same properties as of POCO (first class) and it works fine.

    Can any one has idea what is wrong with it. ExecuteStoreQuery has some limitation to map the result in POCO. My properties are all primitives not complex one. 

    Your any answer will help. Thanks

    Saturday, June 9, 2012 2:23 PM

All replies

  • By right, it should map if the property name that you gave in POCO class and the column return from ExecuteStoreQuery are same.

    //Sample ExecuteStoreQuery
    
    var thislist = context.ExecuteStoreQuery<Catego>("Select *, (CategoryId*2) as total from Categories");

    //Custom class on Northwind Categories + additional property, total
    public class Catego
    {
        public int CategoryID { get; set; }
        public string CategoryName { get; set; }
        public string Description { get; set; }
        public byte[] Picture { get; set; }
        public int total { get; set; }
    }

    Hope this helps.


    • Edited by TinMgAye Saturday, June 9, 2012 4:13 PM
    • Proposed as answer by TinMgAye Wednesday, June 13, 2012 10:06 AM
    Saturday, June 9, 2012 4:07 PM
  •  
     
    Why don't you just use backdoor, execute the sproc, use a datareader,
    populate the POCO(s) and return them in a List<T>?
     
     
    Saturday, June 9, 2012 4:22 PM
  • Hello darnold,

    I cant use stored procedure because my company don't want to use it. So the remaining approaches I can consider is either make required VIEWS or use ExecuteStoreQuery method.

    I have already see the link you mention. But it cant map the desired properties in POCO but in classes other than the POCO it will. 

    Wednesday, June 13, 2012 8:41 AM
  • On 6/13/2012 4:41 AM, Raza Haider wrote:
    > Hello darnold,
    >
    > I cant use stored procedure because my company don't want to use it. So
    > the remaining approaches I can consider is either make required VIEWS or
    > use ExecuteStoreQuery method.
     
    So issue dynamic T-SQL like the link was showing you,  or use dynamic
    E-SQL, and use a datareader.
     
    >
    > I have already see the link you mention. But it cant map the desired
    > properties in POCO but in classes other than the POCO it will.
     
    That's not correct. A POCO is just another class/object that has public
    properties. Just like on the EF model if using an EF entity on the
    model, you can instantiate the object new and load the object manually
    property by property with using a datareader. You can do the same thing
    with a POCO and there is nothing stopping you from doing that.
     
    Wednesday, June 13, 2012 2:07 PM
  • Allen Unfortunately EF is stopping me from doing that. While using reader i can only query the tables that are in the model no matter whether it is inline query or some thing else. To query the tables that are not in the model i can use ExecuteStoreQuery and that gives me maaping issues in POCO...!! Did you tried ExecuteStoreQuery with POCOs...? With POCOs I mean to say the classes that are register with the EntityFramework i.e. use is context class of edmx file....!!

    Thank waiting for your reply...!!!?

    Wednesday, June 20, 2012 6:06 AM
  • On 6/20/2012 2:06 AM, Raza Haider wrote:
    > Allen Unfortunately EF is stopping me from doing that. While using
    > reader i can only query the tables that are in the model no matter
    > whether it is inline query or some thing else. To query the tables that
    > are not in the model i can use ExecuteStoreQuery and that gives me
    > maaping issues in POCO...!! Did you tried ExecuteStoreQuery with
    > POCOs...? With POCOs I mean to say the classes that are register with
    > the EntityFramework i.e. use is context class of edmx file....!!
    >
    > Thank waiting for your reply...!!!?
    >
     Yeah I used the backdoor I talked about. I used the entities off the
    model in the model first approach, instantiated objects new and
    populated them from a datareader.  And if you looked at that link I talk
    about using the backdoor, the only part that has anything to do with EF
    is the Entity connection. The rest of that stuff is straight-up ADO.NET
    SQL Command object, T-SQL and ADO.NET Datareader that has nothing to do
    with an EF anything.
     
    I did the same thing with Entity SQL, using the objects on the model and
    the ESQL datareader, which would have nothing to do with stopping me
    from using an entity on the model and entity's/object's public properties.
     
    If you are talking about the POCO in the link below as an example, then
    I can flat-out tell you that I can create a new Category, populate the
    public properties of Category, create Products, populate Products, load
    Products into Category and return Category with List<Product> that would
    have absolutely nothing to do with EF. It's just a class/object with
    public properties.
     
    Sometimes, one has to think outside the box, and come to the realization
    that EF is not a stops all and ends all solution. No ORM is that.
     
     
    Wednesday, June 20, 2012 7:52 PM
  • Darnold can you give me an example of how to query from a table not in the edmx using EF Database first approach..? I tried to query using entity command and it gives me an exception that my requried table was not in the model...!!!

    "TableName' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly.

    Thanx

    Thursday, June 21, 2012 10:35 AM
  • Hi Raza Haider,

    We can working with Entity Client directly, please refer to this link. By this way, we can write t-sql to operate the tables which are not contain in the edmx file.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, June 25, 2012 3:30 AM
    Moderator
  • Still unable to find the solution. Your specified LINQ mostly executes ESQL aganist EF with demands that the table must be in the context for query...!!! So seems that need to find the other way round...!!!
    Tuesday, July 3, 2012 6:17 AM