execute custom sql with entity framework4

Question

i have to receive workorders from the database, specific for the user.

The filter for all users is different, so one must do all order shipped to UK, ont all to 5 other countries, one does all the high value things, one does al the order which containt > 10 items etc.

So, what i came up with this idea (shoot at it if you have a better one!)

i create a view for each user, and all the views return the same data, but the filter is different.

In ado.net i would do something like this:

string sql = "select * from vwWorkOrders" + userName;
[rest of the ado.net here]

but now i'm using ef4, and i was wondering what is the equivalent of this kind of code.

that is the name of the user, so if i am logged on, i would like to select all data from vwWorkOrdersMichel and if you're logged on, i would like to select al; data from vwWorkOrdersSaeed — Michel
– Michel, Commented Nov 15, 2010 at 14:49
please do not create a view for each user or my eyes will bleed — Richard Friend
– Richard Friend, Commented Nov 15, 2010 at 15:03
@Richard, as i said: shoot at it if you have a better idea. Reading your sarcastic remark gives me the idea you have a way better idea, so if you would like to share it? — Michel
– Michel, Commented Nov 15, 2010 at 15:15

Devart · Accepted Answer · 2010-11-15 14:39:21Z

9

You can use the ExecuteStoreQuery method like in the following example:

context.ExecuteStoreQuery<vwWorkOrder>(sql);

This method allows you to execute storage SQL and obtain strongly-typed results.
In case you need to pass some parameters, just pass necessary ObjectParameter instances in the call of ExecuteStoreQuery.

answered Nov 15, 2010 at 14:39

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michel Over a year ago

i think this is excactly what i want, i'll try it right away!

egoodberry · Accepted Answer · 2010-11-15 14:54:18Z

3

You can use eSQL, which allows for a little more flexibility in your queries. You might download LinqPad to play around with it beforehand.

// whereClause is a string
string query = string.Format("select * from ObjectContext.vwWorkOrders where {0}", whereClause);

Then just use the EntityConnection and EntityCommand classes to execute your command and loop over the results in a manner not altogether different from ADO.NET.

EDIT: I just saw your comment; I thought that you had a typo in your example. A more suitable code snippet for what you are trying to achieve:

// userName is a string, ie "Michel"
string query = string.Format("select * from ObjectContext.vwWorkOrders{0}", userName);

edited Nov 15, 2010 at 14:54

answered Nov 15, 2010 at 14:38

egoodberry

2,7762 gold badges26 silver badges29 bronze badges

Collectives™ on Stack Overflow

execute custom sql with entity framework4

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related