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

Answer 1

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.

Answer 2

 

<http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx>

 

Why don't you just use backdoor, execute the sproc, use a datareader,

populate the POCO(s) and return them in a List<T>?

 

 

Answer 3

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. 

Answer 4

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.

 

http://msdn.microsoft.com/en-us/library/bb738684.aspx#Y237

>

> 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.

 

Answer 5

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...!!!?

Answer 6

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.

 

<http://blogs.msdn.com/b/adonet/archive/2009/05/21/poco-in-the-entity-framework-part-1-the-experience.aspx>

 

Answer 7

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

Answer 8

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

Answer 9

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...!!!