Asked by:
Using ExecuteStoreQuery method

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.Saturday, June 9, 2012 4:07 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 dynamicE-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 publicproperties. Just like on the EF model if using an EF entity on themodel, you can instantiate the object new and load the object manuallyproperty by property with using a datareader. You can do the same thingwith a POCO and there is nothing stopping you from doing that.
- Marked as answer by Allen_MSDN Monday, June 18, 2012 2:33 AM
- Unmarked as answer by Allen_MSDN Thursday, June 21, 2012 2:39 AM
- Proposed as answer by Allen_MSDN Thursday, June 21, 2012 2:39 AM
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 themodel in the model first approach, instantiated objects new andpopulated them from a datareader. And if you looked at that link I talkabout using the backdoor, the only part that has anything to do with EFis the Entity connection. The rest of that stuff is straight-up ADO.NETSQL Command object, T-SQL and ADO.NET Datareader that has nothing to dowith an EF anything.I did the same thing with Entity SQL, using the objects on the model andthe ESQL datareader, which would have nothing to do with stopping mefrom 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, thenI can flat-out tell you that I can create a new Category, populate thepublic properties of Category, create Products, populate Products, loadProducts into Category and return Category with List<Product> that wouldhave absolutely nothing to do with EF. It's just a class/object withpublic properties.Sometimes, one has to think outside the box, and come to the realizationthat EF is not a stops all and ends all solution. No ORM is that.
- Proposed as answer by Allen_MSDN Thursday, June 21, 2012 2:39 AM
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 -
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