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

    The ADO.NET Provider for Snowflake supports Entity Framework which requires C1.EntityFrameworkCore.Snowflake package to be installed. This article demonstrates the model-first approach to building an Entity Framework model that maps data tables to classes for simpler access to Snowflake.

     

    In the following example, we used CallCenter.cs file to map the CallCenter Datatable. SnowflakeContext class has been defined to access the CallCenter Datatable and establish a connection to the Snowflake service by overriding the OnConfiguring method. This method invokes the UseSnowflake method of the DbContextOptionsBuilder class to configure the context and establish a connection with the Snowflake service. To see the code for the SnowflakeContext class, please refer to the SnowflakeContext.cs file.

     

    The LINQ queries can be used to perform different data operations on the mapped classes as demonstrated in the code below.

    C#
    Copy Code 
    public partial class CallCenter
    {
        public int CcCallCenterSk { get; set; }
        public string CcCallCenterId { get; set; } = null!;
        public decimal? CcRecStartDate { get; set; }
        public decimal? CcRecEndDate { get; set; }
        public int? CcClosedDateSk { get; set; }
        public int? CcOpenDateSk { get; set; }
        public string? CcName { get; set; }
        public string? CcClass { get; set; }
        public int? CcEmployees { get; set; }
        public int? CcSqFt { get; set; }
        public string? CcHours { get; set; }
        public string? CcManager { get; set; }
        public int? CcMktId { get; set; }
        public string? CcMktClass { get; set; }
        public string? CcMktDesc { get; set; }
        public string? CcMarketManager { get; set; }
        public int? CcDivision { get; set; }
        public string? CcDivisionName { get; set; }
        public int? CcCompany { get; set; }
        public string? CcCompanyName { get; set; }
        public string? CcStreetNumber { get; set; }
        public string? CcStreetName { get; set; }
        public string? CcStreetType { get; set; }
        public string? CcSuiteNumber { get; set; }
        public string? CcCity { get; set; }
        public string? CcCounty { get; set; }
        public string? CcState { get; set; }
        public string? CcZip { get; set; }
        public string? CcCountry { get; set; }
        public decimal? CcGmtOffset { get; set; }
        public decimal? CcTaxPercentage { get; set; }
    }

     

    The next code example defines SnowflakeContext class to access the Products datatable and establish a connection to the Snowflake service by overriding the OnConfiguring method. This method invokes the UseSnowflake method of the DbContextOptionsBuilder class to configure the context and establish a connection with the Snowflake service.

    C#
    Copy Code 
    public partial class MainContext : DbContext
    {
        public MainContext()
        {
            Database.AutoTransactionsEnabled = false;
        }
        public MainContext(DbContextOptions<MainContext> options)
            : base(options)
        {
            Database.AutoTransactionsEnabled = false;
        }
        public virtual DbSet<CallCenter> CallCenters { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSnowflake("Url=****;Account=****;Warehouse=****;Role=****;Database=****;Schema=****;OAuthTokenEndpoint=****;OAuthClientId=****;OAuthClientSecret=****;OAuthAccessToken=****;OAuthRefreshToken=****");
            }
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CallCenter>(entity =>
            {
                entity.HasNoKey();
                entity.ToTable("CALL_CENTER");
                entity.Property(e => e.CcCallCenterId)
                    .IsRequired()
                    .HasColumnName("CC_CALL_CENTER_ID");
                entity.Property(e => e.CcCallCenterSk).HasColumnName("CC_CALL_CENTER_SK");
                entity.Property(e => e.CcCity).HasColumnName("CC_CITY");
                entity.Property(e => e.CcClass).HasColumnName("CC_CLASS");
                entity.Property(e => e.CcClosedDateSk).HasColumnName("CC_CLOSED_DATE_SK");
                entity.Property(e => e.CcCompany).HasColumnName("CC_COMPANY");
                entity.Property(e => e.CcCompanyName).HasColumnName("CC_COMPANY_NAME");
                entity.Property(e => e.CcCountry).HasColumnName("CC_COUNTRY");
                entity.Property(e => e.CcCounty).HasColumnName("CC_COUNTY");
                entity.Property(e => e.CcDivision).HasColumnName("CC_DIVISION");
                entity.Property(e => e.CcDivisionName).HasColumnName("CC_DIVISION_NAME");
                entity.Property(e => e.CcEmployees).HasColumnName("CC_EMPLOYEES");
                entity.Property(e => e.CcGmtOffset).HasColumnName("CC_GMT_OFFSET");
                entity.Property(e => e.CcHours).HasColumnName("CC_HOURS");
                entity.Property(e => e.CcManager).HasColumnName("CC_MANAGER");
                entity.Property(e => e.CcMarketManager).HasColumnName("CC_MARKET_MANAGER");
                entity.Property(e => e.CcMktClass).HasColumnName("CC_MKT_CLASS");
                entity.Property(e => e.CcMktDesc).HasColumnName("CC_MKT_DESC");
                entity.Property(e => e.CcMktId).HasColumnName("CC_MKT_ID");
                entity.Property(e => e.CcName).HasColumnName("CC_NAME");
                entity.Property(e => e.CcOpenDateSk).HasColumnName("CC_OPEN_DATE_SK");
                entity.Property(e => e.CcRecEndDate).HasColumnName("CC_REC_END_DATE");
                entity.Property(e => e.CcRecStartDate).HasColumnName("CC_REC_START_DATE");
                entity.Property(e => e.CcSqFt).HasColumnName("CC_SQ_FT");
                entity.Property(e => e.CcState).HasColumnName("CC_STATE");
                entity.Property(e => e.CcStreetName).HasColumnName("CC_STREET_NAME");
                entity.Property(e => e.CcStreetNumber).HasColumnName("CC_STREET_NUMBER");
                entity.Property(e => e.CcStreetType).HasColumnName("CC_STREET_TYPE");
                entity.Property(e => e.CcSuiteNumber).HasColumnName("CC_SUITE_NUMBER");
                entity.Property(e => e.CcTaxPercentage).HasColumnName("CC_TAX_PERCENTAGE");
                entity.Property(e => e.CcZip).HasColumnName("CC_ZIP");
            });
            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.

    For LINQ queries,  "using System.Linq" must be declared in the code.

    C#
    Copy Code 
    public void Select(MainContext context)
{
    Console.WriteLine("Query all Call Centers...");
    var records = from p in context.CallCenters select p;
    foreach (var center in records)
    {
      Console.WriteLine($"{center.CcCallCenterSk} - {center.CcName} - {center.CcClass} - {center.CcCountry}");
    }
}

    The Scaffolding feature supports the user to create the model and dbcontext when you create a model in the Entity Framework for all dataconnectors.