Matching DataContext With DbContext

Posted by Sharpcnet under ADO.NET on 10/30/2013 | Points: 10 | Views : 5049 | Status : [Member] | Replies : 5
I have generated an edmx file using entity data model. Would like to use the 'GetCommand' method, which is not in dbContext but in DataContext. So,someone suggested this 'alternate'..

When compiling, there is an error at the sqlcommand line
"This method supports the LINQ to Entities infrastructure and is not intended to be used directly from your code."
 public DataTable GetAllRecords()
{
using (DataContext ctx = new DataContext(MedianDB.Connection))
{
DataSet ds = new DataSet();
DataTable dt = new DataTable();

var query = (from j in MedianDB.tblCountries
orderby j.CountryName ascending
select j);
SqlCommand cmd = (SqlCommand)ctx.GetCommand(query); //error here
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
return dt;
}
}
Is there any chance I could make this work. Im using .Net framework 4.0, C#




Responses

Posted by: Bandi on: 10/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Refer this link:
using System.Data.Common;
using System.Data.Entity;
using TestingEntityFramework.Core;

namespace TestingEntityFramework.Data
{
public class DataContext : DbContext
{
public DbSet Users { get; set; }
}
}

Reference:
http://iamnotmyself.com/2011/12/18/test-driven-evolutionary-design-with-entity-framework/

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Hi,
I think you are not following correct approach..
http://stackoverflow.com/questions/4586834/returning-a-datatable-using-entityframe-executestorequery

Why do you wish to get DataTable as return type.. Can you tell us the purpose of getting DataTable as return type... We will provide some alternatives if possible

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 10/30/2013 [Member] Starter | Points: 25

Up
0
Down
I'm binding the result set to a gridview. Having it in a datatable enables the sort option

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 10/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Write one class for converting IList to DataTable
    public static class Sample

{
public static DataTable ToDataTable<T>(this IList<T> data)
{
PropertyDescriptorCollection props =
TypeDescriptor.GetProperties(typeof(T));
DataTable table = new DataTable();
for (int i = 0; i < props.Count; i++)
{
PropertyDescriptor prop = props[i];
table.Columns.Add(prop.Name); //, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);

}
object[] values = new object[props.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = props[i].GetValue(item);
}
table.Rows.Add(values);
}
return table;
}
}


//.aspx.cs Page

Here studyEntities is configured in the web.config file for edmx reference

using (studyEntities sdContext = new studyEntities())
{
var selectQuery = (from j in sdContext.JOBS.AsEnumerable() orderby j.MIN_SALARY ascending select j).ToList<JOB>();


//Call Sample.ToDataTable<JOB> and return DataTable
DataTable dt = Sample.ToDataTable<JOB>(selectQuery);
fetchGridView.DataSource = dt;
fetchGridView.DataBind();
}


fetchGridView is the name Grid View

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sharpcnet on: 10/30/2013 [Member] Starter | Points: 25

Up
0
Down
Thank you. What If I join multiple tables. Would this play good?

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response