Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Mar 22, 2011 02:27 PM by mohammadalhoss
May 19, 2010 03:30 PM|LINK
Is it possible?
I'm having trouble using entities: I still want to use entities on my "DAL" layer, but in "BLL" layer I want to return not a collection of entities but just a Dataset with all the table I got back from Stored Procedure. How can I do that?
The presention layer don't know anything about entities, if I can get just a dataset from "BLL", it will be very easy to work with it as it was before. Only the DAL and BLL layers will be changing.
Does entities support stored procedure that is returning more than one table, I mean more than one entity?! If not, This is another BIG problem Microsoft didn't think about.
I must tell you the truth: I am really disappointed about using Entities. There is no logical support for Stored Procedure and too many problems when you want to have N-Tiers. It is like Microsoft have done only half of the work needed so this
will be really usefull.
May 19, 2010 05:08 PM|LINK
You'd have to use a method based on Reflection to create the DataTable for you.
Here's an example, from the Northwind sample database:
protected void Page_Load(object sender, EventArgs e)
NorthwindEntities2 db = new NorthwindEntities2();
int orderid = 10248;
var query = from o in db.Orders
join od in db.Order_Details on o.OrderID equals od.OrderID
where o.OrderID == orderid
OrderID = o.OrderID,
OrderDate = o.OrderDate,
ShipAddress = o.ShipAddress,
Quantity = od.Quantity,
UnitPrice = od.UnitPrice
DataTable dt = LINQToDataTable(query);
public DataTable LINQToDataTable<T>(IEnumerable<T> varlist)
DataTable dtReturn = new DataTable();
// column names
PropertyInfo oProps = null;
if (varlist == null) return dtReturn;
foreach (T rec in varlist)
// Use reflection to get property names, to create table, Only first time, others will follow
if (oProps == null)
oProps = ((Type)rec.GetType()).GetProperties();
foreach (PropertyInfo pi in oProps)
Type colType = pi.PropertyType;
if ((colType.IsGenericType) && (colType.GetGenericTypeDefinition() == typeof(Nullable<>)))
colType = colType.GetGenericArguments();
dtReturn.Columns.Add(new DataColumn(pi.Name, colType));
DataRow dr = dtReturn.NewRow();
foreach (PropertyInfo pi in oProps)
dr[pi.Name] = pi.GetValue(rec, null) == null ? DBNull.Value : pi.GetValue
May 19, 2010 05:20 PM|LINK
Thank you All-Star, this seems to work if the quary is returning only one table (one entity).
What if I am having Stored Procdure that will return more than one entity? Is this possuble at all?
I onle saw SP that can be linked to one entity only.
May 19, 2010 05:28 PM|LINK
this seems to work if the quary is returning only one table (one entity).
I've used a join, if you might notice.
May 19, 2010 05:39 PM|LINK
What I meant is this ssanriu:
"Select X from Table1
Select Y from Table 2"
This is a simply SP that returns 2 table (not join between them).
Now here is the problem. When I Import function (stored Procedure) to the model I need to select what collection is being returning: None, Scalar, Entities.
But entities give me the option to choose onle one entity (which represent only one table). What with the second table?
May 19, 2010 06:05 PM|LINK
alright, we have two questions, even though the primary question is on the thread title:
1. How to convert entity collections to a dataset:
I believe I provided a working sample and you're indicating it works.
2. Possible to have multiple sequences out of sprocs?
Look into IMultipleResults interface. Check this blog post:
And another reference:
Linq to Entities
stored Procedures with multiple results
May 19, 2010 10:05 PM|LINK
Peter Thanks again, Unfortunately it is not suitable for LINQ to Entities :-/
I'm looking maybe for a same solution like in LINQ to SQL that will be working also for Entities.
I see this can only be done in code ant not in the model like other "Import Function".
May 24, 2010 05:17 AM|LINK
As I know, in LINQ to SQL, you have to modify the generated source code as IMultipleResults if you want to return multiple results in a stored proceudre. Then, you can consider to convert them to datatables. However, I'm not sure about LINQ to Entities.
I suggest you to post this question here to get more help:
Mar 22, 2011 02:27 PM|LINK
Try this link it is exactly what you need.