Returning DataTable or DataSet from EntityFramework-CodePudding

I have a number of Functions in a Data Access Layer that are currently using ADO.Net, but I'm investigating how to rewrite these to use EntityFramework in stead.

Since most of these functions return DataTable objects, it would be nice to be able to keep returning DataTable (at least for a period). Let's say that one of the functions look like this:

public async Task<DataTable> GetGoblinsAsync(CancellationToken cancelToken)
{
    using (var conn = GetConnection())
    {
    await conn.OpenAsync(cancelToken).ConfigureAwait(false);
    var command =  new SqlCommand("select * from [SomeGoblinTable]", conn); 
    using (SqlDataReader reader = await command.ExecuteReaderAsync(cancelToken).ConfigureAwait(false))
    {
        DataTable dt = new DataTable();
        dt.Load(reader);
        return dt;
    }
    }
}

CodePudding user response：

To rewrite this function to use Entity Framework, you can start by adding a reference to the Entity Framework NuGet package and using the DbContext class to connect to your database.

Here's how you could rewrite the GetGoblinsAsync function using Entity Framework:

using (var context = new MyDbContext())
{
    // Use the DbSet<T> for the entity type you want to retrieve
    var goblins = context.Goblins.ToListAsync(cancelToken);
    return ToDataTable(goblins);
}

Note that this will return a list of entity objects instead of a DataTable. To convert the list of entities to a DataTable, you can use a helper function like this:

`public DataTable ToDataTable<T>(List<T> items)
{
    DataTable dataTable = new DataTable(typeof(T).Name);

    //Get all the properties
    PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in Props)
    {
        //Setting column names as Property names
        dataTable.Columns.Add(prop.Name);
    }
    foreach (T item in items)
    {
        var values = new object[Props.Length];
        for (int i = 0; i < Props.Length; i  )
        {
            //inserting property values to datatable rows
            values[i] = Props[i].GetValue(item, null);
        }
        dataTable.Rows.Add(values);
    }
    //put a breakpoint here and check datatable
    return dataTable;
}`

This will allow you to convert the list of entity objects to a DataTable and return it from the GetGoblinsAsync function as before.

You can then use Entity Framework to query and retrieve data from the database in your other functions as well. For example, you can use the Where method to filter the results, the Include method to eager-load related entities, and the AsNoTracking method to improve performance by disabling entity tracking.

I hope this helps! Let me know if you have any questions.

CodePudding user response：

Using FastMember (nuget) you can do:

public static class EnumerableExtensions
{
    public static DataTable ToDataTable<T>(this IEnumerable<T> source)
    {
        var dt = new DataTable();
        using (var reader = ObjectReader.Create(source))
            dt.Load(reader);
        return dt;
    }
}

CodePudding user response：

You can use EF Core on .NET Framework 4.7.2 and up with EF Core 3.1. Start by reading how EF works on the official site https://www.entityframeworktutorial.net/basics/how-entity-framework-works.aspx

Basically add EF Core NuGet packages you need in data layer:

Microsoft.EntityFrameworkCore.Design
Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools

Build ApplicationDbContext

using Microsoft.EntityFrameworkCore;

namespace TestCoreApplication.Data
{
    public partial class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext()
        {
        }

        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
            : base(options)
        {
        }      

        //Add db sets here
        public virtual DbSet<Goblin> Goblins{ get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("ConnectionString");
                //Move the connection string to appSettings.json or to other safe places 
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //You have work here if you have specific model relations
            OnModelCreatingPartial(modelBuilder);
        }


    }
}

Add ApplicationDbContext to the dependency container in Program/StartUp

services.AddDbContext<ApplicationDbContext>(
                options => options.UseSqlServer("ConectionString"));
//Move the connection string to appSettings.json or to other safe places

Use it to return your Data as GataType you need

//call context in service
private readonly ApplicationDbContext context;
public YourService(ApplicationDbContext context)
{
   this.context = context;
}

public async Task<List<Goblin>> GetGoblinsAsync()
{
    //List of all goblins
    var goblins = await this.context.Goblins.ToListAsync();

    //List of goblins by where conditions
    var goblins = await this.context.Goblins.Where(x => x.Id == "id" && x.Name = "name").ToListAsync();

}