Entity Framework - get multiple results from an SQL statement

Question

With EF I can return a collection of objects like so

entities.Customers.ToArray();

And I can include other tables, so I can effectively get 2 result sets back in the one query

entities.Customers.Include("Invoice").ToArray();

or if I have some custom SQL I can achive a similar result:

SqlDataReader reader = GetReaderFromSomewhere("SELECT * FROM Customer");
entities.Translate<Customer>(reader).ToArray();

But how do I get multiple results back from my own SQL? What I was thinking was something like this

SqlDataReader reader = GetReaderFromSomewhere("SELECT Customer.Name AS CustomerName, Invoice.Number AS InvoiceNumber FROM Customer JOIN Invoice ON Customer.ID = Invoice.CustomerID");
entities.Translate<Customer>(reader).Include<Invoice>().ToArray();

In the above example I have prefixed all the returned data with the table name so that the Translate method can know which columns belong to which tables. I'm presuming the Tranlate method does not support this but EF must do something similar when the include method is called. So my question is, how can I get the functionality of Include when using Translate?

Eranga · Accepted Answer · 2011-11-30 04:25:26Z

1

AFAIK you can not do that manually. What EF does is, it dynamically generate a class based on the LINQ query(and the Includes) that can materialize entities. Basically this class knows which columns will be mapped to which properties.

However you can use a micro ORM like Dapper which can do Multi Mapping. But this will only work for querying. So change tracking and CUD operations will not be available.

answered Nov 30, 2011 at 4:25

Eranga

32.5k5 gold badges100 silver badges98 bronze badges

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

1 Comment

MikeKulls Over a year ago

So the only solution is to write something yourself? Not the answer I wanted of course but that is an answer and allows me to move forward. I might look at writing something. It doesn't look like it would be too complicated (famous last words? :-) Thanks for the reply, I will mark it as the answer if a solution doesn't appear in a day or so.

Collectives™ on Stack Overflow

Entity Framework - get multiple results from an SQL statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related