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

Question

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?

Christian Specht · Accepted Answer · 2011-12-09 00:11:51Z

2

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.

answered Dec 9, 2011 at 0:11

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pete Lunenfeld Over a year ago

Oh my God! This may be the coolest thing ever. I can return a JSON payload of any generic set of data in two lines of code. Wow! var presult = Massive.DB.Current.Query("SELECT * FROM myTable WHERE myField = @0", "SomeData"); Console.WriteLine(JsonConvert.SerializeObject(presult));

Adam Rackis · Accepted Answer · 2011-12-09 00:07:21Z

0

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.

edited Dec 9, 2011 at 0:07

answered Dec 9, 2011 at 0:02

Adam Rackis

83.5k57 gold badges281 silver badges402 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related