DataConnector | ComponentOne
ADO.NET provider for CSV / Entity Framework
In This Topic
    Entity Framework
    In This Topic

    The ADO.NET Provider for CSV supports Entity Framework which requires the C1.EntityFrameworkCore.CSV package to be installed. In this section, we have used the model first approach to build an Entity Framework model that maps data tables to classes for simpler access to CSV.

    The following code defines the SampleCSV class to map the SampleCSV datatable.

    C#
    Copy Code 
    public partial class SampleCSV
{
    public int Id { get; set; }
    public int Year { get; set; }
    public string Industry_code { get; set; }
    public string Industry_name { get; set; }
    public string Rme_size_grp { get; set; }
    public string Variable { get; set; }
    public int Value { get; set; }
    public string Unit { get; set; }
}

    Now, DocumentContext class has been defined to access the SampleCSV datatable and establish a connection to the CSV service by overriding the OnConfiguring method. This method invokes the UseCSV method of the DbContextOptionsBuilder class to configure the context and establish a connection with the CSV service.

    C#
    Copy Code 
    public partial class CSVContext: DbContext
{
    public CSVContext()
    {
        Database.AutoTransactionsEnabled = false;
    }

    public CSVContext(DbContextOptions<CSVContext> options)
        : base(options)
    {
        Database.AutoTransactionsEnabled = false;
    }

    public virtual DbSet<SampleCSV> SampleCSV { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseCSV("Uri='sampleCSV.csv'");
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<SampleCSV>(entity =>
        {
            entity.HasNoKey();
            entity.ToTable("sampleCSV");
            entity.Property(e => e.Id).HasColumnName("id");
            entity.Property(e => e.Year).HasColumnName("year");
            entity.Property(e => e.Industry_code).HasColumnName("industry_code");
            entity.Property(e => e.Industry_name).HasColumnName("industry_name");
            entity.Property(e => e.Rme_size_grp).HasColumnName("rme_size_grp");
            entity.Property(e => e.Variable).HasColumnName("variable");
            entity.Property(e => e.Value).HasColumnName("value");
            entity.Property(e => e.Unit).HasColumnName("unit");
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

    You can now use the LINQ queries to perform different data operations to the mapped classes as demonstrated in the code below.

    Note: For LINQ queries,  "using System.Linq" must be declared in the code.
    C#
    Copy Code 
    static void SelectExample() 
{
    Console.WriteLine("Query all values...");

    using(var db = new CSVContext()) 
    {
        var values = from p in db.SampleCSV select p;

        foreach(var value in values) 
        {
            Console.WriteLine($"{value.Id} - {value.Industry_code} - {value.Industry_name} - {value.Rme_size_grp} - {value.Variable} - {value.Value} -");
        }
    }
}

static void CUDCSVFile() 
{
    Console.WriteLine("\nCUD CSV file...");

    using(var context = new CSVContext()) 
    {
        var sample = new SampleCSV();
        sample.Id = 101;
        sample.Year = 2022;
        sample.Industry_code = "B";
        sample.Industry_name = "Telecommunications";
        sample.Rme_size_grp= "a_1";
        sample.Variable = "Salaries and wages paid";
        sample.Value = 234;
        sample.Unit= "DOLLARS";
        context.SampleCSV.Add(sample);
        int result = context.SaveChanges();
        Console.WriteLine("Number of row inserted: " + result);
    }

    using(var context = new CSVContext()) 
    {
        var sample = context.SampleCSV.Where(x => x.Year.Equals(2011)).FirstOrDefault();
        if (sample != null) 
        {
            sample.Year = 2022;
            int result = context.SaveChanges();
            Console.WriteLine("Number of row updated: " + result);
        }
    }

    using(var context = new CSVContext()) 
    {
        context.SampleCSV.Remove(context.SampleCSV.Where(x => x.Year.Equals(2011)).FirstOrDefault());
        var result = context.SaveChanges();
        Console.WriteLine("Number of row deleted: " + result);
  }
}
    Note: The Scaffolding feature supports the user to create the model and dbcontext when you create a model in the Entity Framework for all dataconnectors.