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.
P.S.
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.
DataSetentities
"Try not to become a man of success but rather a man of value." / Albert Einstein
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?
"Try not to become a man of success but rather a man of value." / Albert Einstein
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:
Dango23
Member
34 Points
113 Posts
How to convert entity collections to a dataset?
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.
P.S.
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.
DataSet entities
PeteNet
All-Star
81342 Points
11398 Posts
Re: How to convert entity collections to a dataset?
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 select new { 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()[0]; } 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 (rec, null); } dtReturn.Rows.Add(dr); } return dtReturn; }Peter
Dango23
Member
34 Points
113 Posts
Re: How to convert entity collections to a dataset?
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.
Thanks again!
PeteNet
All-Star
81342 Points
11398 Posts
Re: How to convert entity collections to a dataset?
May 19, 2010 05:28 PM|LINK
I've used a join, if you might notice.
Peter
Dango23
Member
34 Points
113 Posts
Re: How to convert entity collections to a dataset?
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?
PeteNet
All-Star
81342 Points
11398 Posts
Re: How to convert entity collections to a dataset?
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: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
And another reference: http://blogs.msdn.com/swiss_dpe_team/archive/2008/02/04/linq-to-sql-returning-multiple-result-sets.aspx
Linq to Entities stored Procedures with multiple results to datatable
Peter
Dango23
Member
34 Points
113 Posts
Re: How to convert entity collections to a dataset?
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".
Wencui Qian ...
All-Star
56784 Points
5796 Posts
Microsoft
Re: How to convert entity collections to a dataset?
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:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/threads/
Thanks.
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework
mohammadalho...
Member
3 Points
1 Post
Re: How to convert entity collections to a dataset?
Mar 22, 2011 02:27 PM|LINK
Try this link it is exactly what you need.
http://www.codeproject.com/Tips/171006/Convert-LINQ-to-Entity-Result-to-a-DataTable.aspx
entities