Reputation: 11528
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(), but that doesn't work as it loads all records.
Upvotes: 0
Views: 332
Answers (4)
Reputation: 11528
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
Upvotes: 1
Reputation: 171589
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);
Upvotes: 2
Reputation: 2788
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.
Upvotes: 1
Reputation: 100057
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.
Upvotes: 1