Linq stored procedure with dynamic results

Question

So I'm extremely new to Linq in .Net 3.5 and have a question. I use to use a custom class that would handle the following results from a store procedure:

• Set 1: ID Name Age
• Set 2: ID Address City
• Set 3: ID Product Price

With my custom class, I would have received back from the database a single DataSet with 3 DataTables inside of it with columns based on what was returned from the DB.

My question is how to I achive this with LINQ? I'm going to need to hit the database 1 time and return multiple sets with different types of data in it.

Also, how would I use LINQ to return a dynamic amount of sets depending on the parameters (could get 1 set back, could get N amount back)?

I've looked at this article, but didn't find anything explaining multiple sets (just a single set that could be dynamic or a single scalar value and a single set).

Any articles/comments will help.

Thanks

Answer 1

I'm not very familiar with LINQ myself but here is MSDN's site on LINQ Samples that might be able to help you out.

Answer 2

EDIT: I apologize, I somehow missed the title where you mentioned you wanted help using LINQ with Stored Procedures, my below answer does not address that at all and unfortunately I haven't had the need to use sprocs with LINQ so I'm unsure if my below answer will help.

LINQ to SQL is able hydrate multiple sets of data into a object graph while hitting the database once. However, I don't think LINQ is going to achieve what you ultimately want -- which as far as I can tell is a completely dynamic set of data that is defined outside of the query itself. Perhaps I am misunderstanding the question, maybe it would help if you provide some sample code that your existing application is using?

Here is a quick example of how I could hydrate a anonymous type with a single database call, maybe it will help:

var query = from p in db.Products
            select new
                       {
                           Product = p,
                           NumberOfOrders = p.Orders.Count(),
                           LastOrderDate = p.Orders.OrderByDescending().Take(1).Select(o => o.OrderDate),
                           Orders = p.Orders
                       };

Answer 3

I believe this is what you're looking for

Linq to SQL Stored Procedures with Multiple Results - IMultipleResults