Geek asks and answers

How can I return column length variables via LINQ to SQL

I had a search on the internet and I can not find anything that directly matches what I need ....

I have a stored procedure that returns a fixed set of columns (let them say ColumnA, ColumnB and ColumnC), and then additionally returns an undefined number / label for additional columns (possibly ColumnD, ColumnE and ColumnF or ColumnF, ColumnP, ColumnT and ColumnW).

The dynamic part of the request is determined by the Id parameter for proc.

Unfortunately, I do not control the scheme, and it is poorly designed to add a very large number of unrelated columns to the end (and they continue to add them), instead of having an extra table to store it efficiently.

As a result, I cannot return a specific set of columns at compile time, so LINQ to SQL cannot determine the return type of the stored procedure.

Do I have any other options or ways around this? Here is an example of what I'm doing below. Can I rewrite it to work with LINQ to SQL? I am writing an application with C # and SQL Server 2008 if this helps.

Thank.

CREATE PROCEDURE [Foo].[GetBar]
(
    @Id INT,
    @FooVar VARCHAR(50),
    @BarVar DATE
)

AS 
BEGIN

CREATE TABLE #TempTbl 
(     
    [ColumnName] VARCHAR(50)
)

INSERT #TempTbl EXEC [Foo].GetColumnNames @Id
DECLARE @ColumnNameList NVARCHAR(max) 
SET @ColumnNameList = '' 
SELECT @ColumnNameList = COALESCE(@ColumnNameList + '],[', '') + ColumnName FROM #TempTbl
DROP TABLE #TempTbl
SELECT @ColumnNameList = @ColumnNameList + ']'
SELECT @ColumnNameList = SUBSTRING(@ColumnNameList, 3, LEN(@ColumnNameList) )

DECLARE @FixedColumns VARCHAR(200)
DECLARE @SelectQuery NVARCHAR(max) 
DECLARE @WhereQuery VARCHAR (100)
DECLARE @FullQuery NVARCHAR(max) 
DECLARE @ParamaterDef nvarchar (100)
DECLARE @Foo VARCHAR(50)
DECLARE @Bar DATE

SET @FixedColumns = N'[ColumnA], [ColumnB], [ColumnC], '
SET @SelectQuery = N'SELECT ' + @FixedColumns + @ColumnNameList + N' FROM [Foo].[FooBar] '
SET @WhereQuery = N'WHERE [Foo] = @Foo AND [Bar] = @Bar'
SET @FullQuery = @SelectQuery + @WhereQuery   
SET @ParamaterDef = N'@Foo VARCHAR(50), @Bar DATE'

EXECUTE sp_executesql @FullQuery, @ParamaterDef, @Foo = @FooVar, @Bar = @BarVar 

END
+5
source share
1 answer

Can't you use SQL Data Adapterthat fills DataSet? Then you can use LINQfor the DataSet and complete all your operations LINQ:

DataTable yourDataTable = ds.Tables["TableName"];

var query =
   from yourDataTable in yourDataTable.AsEnumerable()
   select new
   {
      NewField = yourDataTable.Field<int>("ColumnName"),
      NewField2 = yourDataTable.Field<string>("ColumnName2"),
   };

MSDN (LINQ to DataSet)

MSDN (single table queries using LINQ to DataSet

+6
source

More articles:


All Articles