Dynamic SQL (sql: EXEC([query]) & Linq problem

[RabidBob]

Ok, I've solved my own problem while typing up this query so figured I'd post the answer here in case anyone else would find it useful. I've only just started using Linq last week so my solution may be sub-optimal, if there are any better ways to do this please post them!! But I couldn't find a "stock" answer on the web, so here it is. Hopefully there's a way to do what I'm doing here purely in Linq without using a stored procedure. Goal: To pass a table name and selection clause into a query and get the expected result. SQL stored procedure: - Note the "AS InvNumber" return name. The returned fields must match the fields in the wrapping class in the designer; I didn't find this immediately apparent as I was concentrating on trying to figure out how to deal with the return data from the query. In hindsight it was a "duh" moment. :doh:

CREATE PROCEDURE [dbo].[spGetValidExtractionInvoiceNumbers]
@QueryTable VARCHAR(256),
@EntryInvoiceMonth VARCHAR(32)
AS
DECLARE @QueryString VARCHAR(2048)
BEGIN
SET NOCOUNT ON;

SET @QueryString = 'SELECT DISTINCT InvoiceNumber AS InvNumber FROM ' 
	+ @QueryTable + 
	' WHERE EntryInvoiceMonth = ''' + @EntryInvoiceMonth + ''''
EXEC(@QueryString)

Initial code generated by the designer:

[Function(Name = "dbo.spGetValidExtractionInvoiceNumbers")]
public int getValidExtractionInvoiceNumbers([Parameter(Name = "QueryTable", DbType = "VarChar(256)")] string queryTable, [Parameter(Name = "EntryInvoiceMonth", DbType = "VarChar(32)")] string entryInvoiceMonth)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), queryTable, entryInvoiceMonth);
return ((int)(result.ReturnValue));
}

Altered code: I hacked the int return type to ISingleResult

[Function(Name="dbo.spGetValidExtractionInvoiceNumbers")]
public ISingleResult GetValidExtractionInvoiceNumbers([Parameter(Name="QueryTable", DbType="VarChar(256)")] string queryTable, [Parameter(Name="EntryInvoiceMonth", DbType="VarChar(32)")] string entryInvoiceMonth)
{
IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), queryTable, entryInvoiceMonth);
return ((ISingleResult)(result.ReturnValue));
}

Calling code:

InvoiceEntryDataContext oInvoiceEntryDC = new InvoiceEntryDataContext();
var oInvoiceNumbers = oInvoiceEntryDC.getValidExtractionInvoiceNumbers