LINQ to SQL(.dbml): how do ExecuteQuery method return DataTable?

Question

var products = db.ExecuteQuery<Product>(
  "SELECT ProductID, ProductName " +
  "FROM Products " +
  "WHERE Discontinued = 0 " +
  "ORDER BY ProductName;"
);

above query works fine but is there any way db.ExecuteQuery<Product> returns DataTable Product

instead of var products?

The **whole point** of using Linq-to-SQL is that get **proper, nice objects** (`Product`) instead of just the row/column based `DataTable` approach...... so no - there's no way to turn Linq-to-SQL back into returning row/columns - it's been designed from the ground up to **avoid this** — marc_s, Nov 17 '12 at 14:20
I agree, but we got to a point where 98% is used like that but the 2% of the project needs to run dynamically created SP that are not defined in the DBML, which return dynamic coloumns of various types. Is there really no way to use the DBML context to execute a simple query with unstructured data. I hate datatables- but sometimes people just want it that way. — Piotr Kula, Jan 30 '13 at 09:51

score 4 · Accepted Answer · answered Nov 17 '12 at 14:27

You shouldn't do this. Why then bothering yourself with LINQ-To-SQL and just use normal ADO methods to get a normal datatable object. This isn't what LINQ-to-SQL is for. You should think in terms of objects.

But, if you need to do this any way: ExecuteQuery returns:a collection of IEnumerable<T>. Then you can convert this list to a datatable. You can use the following function to convert it to a datatable:

Convert List/IEnumerable to DataTable/DataView

LINQ to SQL(.dbml): how do ExecuteQuery method return DataTable?

1 Answers1