Getting command from LINQ query

[Chuck P]

I want to get a DataReader from a Linq Query so I wrote this Extension Method

public static IDataReader QueryToDataReader(this
System.Data.Linq.DataContext ctx, object query)
{
IDbCommand command = ctx.GetCommand(query as IQueryable);
command.Connection = ctx.Connection;
command.Connection.Open();
IDataReader reader =
command.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}

Sometimes it pukes because the Query passed in is a Type
System.Collections.Generic.IEnumerable<Application>
not an IQueryable.

Query comes back as a IEnumerable when I call a sproc that returns after a
select statement, even though the return type is defined in the datacontext.

If my sproc returns a TABLE object, the Query comes back as a IQueryable.

I don't understand why I get IEnumerable sometimes and IQueryable others?
Is their a way to get my function to work in both cases?

 

[Wen Yuan Wang [MSFT]]

Hello Chuck,

Your extension method looks fine.

Query comes back as a IEnumerable when I call a sproc that returns after a
select statement, even though the return type is defined in the datacontext.
If my sproc returns a TABLE object, the Query comes back as a IQueryable.

We haven't heard that. It seems you can reproduce the issue, correct? Could
you to paste some code snippet about how did you define the query? I will
try it on my side.

If you have any more concern, please feel free let me know. We are glad to
assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Chuck P]

Create a table in a DB with 5 columns
Create a stored procedure that returns 2 columns
Create a strored function that returns a table with columns
Create dbml in VS
Open Server Explorer
Drag the sproc and function on to the table in the dbml


IQueryable<TableName> query = from a in ctx.SelectSproc()
select a;

IQueryable<TableName> query2 = from a in ctx.TableReturningFunc()
select a;

Hello Chuck,

Your extension method looks fine.

Query comes back as a IEnumerable when I call a sproc that returns after a
select statement, even though the return type is defined in the datacontext.
If my sproc returns a TABLE object, the Query comes back as a IQueryable.

We haven't heard that. It seems you can reproduce the issue, correct? Could
you to paste some code snippet about how did you define the query? I will
try it on my side.

If you have any more concern, please feel free let me know. We are glad to
assist you.

Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Wen Yuan Wang [MSFT]]

Hello Chuck,
Thanks for your reply.

I have reproduced it. The first query retrieves data from Stored Procedure.
But the second query retrieved data from Table-Valued Function.

The stored procedure is executing right away and the Linq to Objects Select
statement is operating on the results of the stored proc.
If you used a table valued function which is composable, you can use it in
a query with any of the any query syntax and it will return an IQueryable.
Therefore, query always comes back as an IEnumerable when calling a stored
procedure. And it returns value as IQueryable when calling a table valued
function.

IEnumerable type object can be cast to IQueryable type by
object.AsQueryable(). But this doesn't help on your senarno, becase
DataContext cannot get Expression Information from such casted IQueryable
object.

For example:
var query = from a in dcdc.StoredProcedure3()
select a;
IDbCommand command = dcdc.GetCommand(query.AsQueryable());
Console.Write(command.CommandText);
//Output: Select Null as [EMPTY]

Let me know if there is anything unclear. We are glad to assist you.
Have a great day,
Best regards,

Wen Yuan
Microsoft Online Community Support
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.