Combine Linq2SQL with custom SQL.

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

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);

Answer 4

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