Combine Linq2SQL with custom SQL

From a custom SQL query I'd like to get an IQueryable (I have my reasons):

Something like:

IQueryable<Client> query = Foo<Client>("SELECT * FROM Clients WHERE ...");

query.Where(e => e.Active==true).Skip(10).Take(10); //etc.

Is there any way to implement Foo?

I found ExecuteQuery<T>(...).AsQueryable()开发者_如何学Go, but that doesn't work as it loads all records.

Can you put this part of your query in a view?

SELECT * FROM Clients...

Then you can use LINQ Where and Skip/Take.

var results = db.MyViews.Where(e => e.Active == true).Skip(10).Take(10);

You could implement this but you'd have to parse the SQL text and look up the mapping to get the correct types (e.g., from Clients to Client). There's no built-in facility for automatically generating an IQueryable from SQL.

From my point of view, the best way would be to implement your own wrapper inheriting IQueryable for such cases. In GetEnumerator() you can implement a row-by-row results reading or simply return ExecuteQuery(...).GetEnumerator() from your own GetEnumerator() method.

I solved it using Dynamic Linq. http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx