CODEWITHSUNDEEP
c#linq-to-sqlentity-framework-4.1
Pete Lunenfeld
Pete Lunenfeld

Reputation: 1717

How to convert ADO.net generic query to Entity Framework or Linq

What is the best pattern to follow using newer EF/Linq to generate dynamic queries and return a generic data set?

My existing environment relies heavily on older ADO.net dynamically created SQL queries returned as Datasets. Using the pattern of: -- sql and parameter details omitted on purpose --

SqlCommand cmd = new SqlCommand();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

This allows us to return custom queries and return them generically to the user as a grid of data. We can execute any dynamically created SQL and get back different sets of data with different columns, data types, etc.

We are NOT using this for O/R mapping, but for read only queries.

Will it be appropriate to migrate to EF/Linq for this, or would it be best to continue using ADO.net even though it seems like the old technology?

Upvotes: 3

Views: 1791

Answers (2)

Christian Specht
Christian Specht

Reputation: 36451

Does it have to be EF/LINQ?

How about using something like Rob Conery's Massive for your read-only queries?
What it does in a nutshell: it executes SQL queries and returns lists of dynamic objects.

Quote from the link:

var result = tbl.Query("SELECT * FROM Categories");

[...]

What you get back is IEnumerable < ExpandoObject > - meaning that it's malleable and exciting. It will take the shape of whatever you return in your query, and it will have properties and so on.

Upvotes: 2

Adam Rackis
Adam Rackis

Reputation: 83366

While EntityFramework does have a CreateSourceQuery<T> method that accepts arbitrary sql, its results must be known at compile time, and be specified via a generic parameter. As a result, this approach likely wouldn't work for you.

There is a dynamic extension library that lets you specify arbitrary sql against either a linq-to-sql or entity framework context, but if you were going to switch to linq just to pass it dynamic sql, I'd say it's not worth it.

Stick to simple data sets.

Upvotes: 0

Related Questions