c# linq to sql with a stored procedure containing dynamic sql

Question

Hi,

I have a stored procedure that contains dynamic sql to return a result set. The query runs fine, but I can't use it via Linq to sql. It appears that the Data Class designer deems that the query only contains one column (Location) rather than the correct number.

I'm guessing its because the dynamic sql means that the columns are not available at design time. The query is this:

set @query = 'select ' + char(13) +
  'case when a.location is not null then upper(left(a.location, 1)) + right(a.location, len(a.location) - 1) else ''All'' end as Location' + char(13) + @strsql +
  'group by ' + char(13) +
  'rollup(location)' + char(13)

where @strsql is a string containing the rest of the query that references the rest of the columns.

Does anyone know of a way to overcome this limitation (aside from not using dynamic sql of course)?

Thanks

Answer 1

Temporarily replace the sql in your stored procedure with a non-dynamic statement, which has the proper number of columns. Add it to you DBML file & save (regenerating the C# code). Replace your original SQL into the stored proc.

Unless you delete & re-add the stored proc to the DBML file, VisualStudio should never look at the stored procedure itself again.

/*
set @query = 'select ' + char(13) + 'case when a.location is not null then 
    upper(left(a.location, 1)) + right(a.location, len(a.location) - 1) 
    else ''All'' end as Location' + char(13) + @strsql + 'group by ' 
    + char(13) + 'rollup(location)' + char(13)  
 */
 select 'a' as Location, 'b' as othercol, 'c' as col3 

This of course assumes that the number of columns in strsql is fixed, with consisient names. IF not, then you are hitting into a limitation of C#, not Linq.