I have been asked to investigate the usefulness of linq to sql for a reporting application we are building. Our reporting table is a sql server wide table with many thousands of columns of different types (String1-500, Int1-500 etc). It holds the results from dynamic reports constructed by the user.
We have a second table that maps a report field to a column on the reporting table with a type ordinal pair (i.e. column String1).
I know it should be possible to construct expression trees against a linq to sql dbml class that can return the report results. I would like to take this a bit further and return only the columns that map to fields and return a dynamic type as the result.
Also to avoid maintaining the huge reporting table class in the dbml.
Is it possible to run a dynamic linq query against a table not in the dbml, i.e. (not a linq to sql entity)?
I realise this is a bastardisation of L2S and I am not fond of it. I am considering using plain old ADO and returning an untype result set.
Yes it is possible to run LINQ to SQL against an entity not in the DBML.
There may be several ways of doing this but the simplest I can think of is calling DataContext.ExecuteQuery which will execute a SQL statement and return a result set.
If you know the type of result you can cast it into an object, otherwise you can return it as a list of Object types.