CODEWITHSUNDEEP
c#.netsqlentity-frameworkef-code-first
Eamon Nerbonne
Eamon Nerbonne

Reputation: 48066

Entity Framework query with *sql* subquery

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.

Upvotes: 2

Views: 1668

Answers (2)

Shyju
Shyju

Reputation: 218732

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

Upvotes: 1

podiluska
podiluska

Reputation: 51494

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

Upvotes: 3

Related Questions