Entity Framework query with sql subquery

Question

In short: can I use a literal SQL subquery somehow in the entity model without using database views?

Context: I have a bunch of ADO.NET queries that result in C# objects. These objects correspond directly to the query shape; i.e. I can do ObjectContext.Translate to extract them from the SqlDataReader. There are lots of these queries, many quite complex, and some of which use features the entity framework doesn't support (while loops, CTE's, hierarchyids, etc.) - converting these (legacy) queries to LINQ isn't feasible.

However, I'd really like to be able wrap these results and add some custom filtering on the C# side of things: things like sorting, filtering, paging etc. I could convert each and every query into a view (or stored procedure) and map these, but that's a hassle and a maintenance nightmare - but in principle EF can be used with "opaque" SQL queries by that route.

Can I somehow use subqueries written in SQL with the entity model? An ObjectContext.Translate that returns an IQueryable rather than an IEnumerable would be ideal, but not necessary: the vast majority of queries are compile-time constants, so some form of pre-processing is possible.

Edit: I'm looking for something returning an IQueryable so I can add filters/sorting client side but have them executed on the DB (as usual). I'm using Entity Framework code-first.

podiluska · Accepted Answer · 2012-08-06 14:41:17Z

3

You can set the DefiningQuery property of an EntitySet to some literal SQL. These are more or less equivalent to a SQL view. Does that solve your issue?

http://msdn.microsoft.com/en-us/library/cc982038.aspx

answered Aug 6, 2012 at 14:41

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

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

2 Comments

Eamon Nerbonne Over a year ago

That looks good! However, I notice I neglected to mention I'm already using code-first and you can't mix code first with model-first in the same assembly; so this solution looks hard to use, unfortunately.

Eamon Nerbonne Over a year ago

Marking as answer: it's probably the best possible solution, even though it's far from ideal. Thanks for the tip!

Shyju · Accepted Answer · 2012-08-06 14:48:34Z

1

If your SQL query is giving a result with the same structure as of the entity class, you can use the DbContext.SqlQuery

 var customer=context.Database.
         SqlQuery<Customer>("SELECT ID,NAME from CUSTOMER WHERE TYPE IN 
                     (SELECT TYPEID FROM IMPORTANT_TYPE)");

Assuming context is your DBContext class object

answered Aug 6, 2012 at 14:48

Shyju

220k106 gold badges421 silver badges499 bronze badges

1 Comment

Eamon Nerbonne Over a year ago

No, this won't work: SqlQuery returns an IEnumerable; so if I try to compose the result with further filtering, I'll be doing that client-side (and generally be retrieving LOTS of excess data and thus being slow).

Collectives™ on Stack Overflow

Entity Framework query with sql subquery

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related