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

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

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

    C#
    Copy Code
    public partial class Accounts
    {
        public int Id { get; set; }
        public string Status { get; set; }
        public string Assignee { get; set; }
        public string AccountId { get; set; }
        public string Address { get; set; }
        public string Categories { get; set; }
        public DateTime? UpdatedDate { get; set; }
        public string Sex { get; set; }
        public DateTime? CreatedDate { get; set; }
        public string UpdatedBy { get; set; }
        public string Name { get; set; }
        public int? RecordNumber { get; set; }
        public string CreatedBy { get; set; }
        public double? Age { get; set; }
        public DateTime? BirthDate { get; set; }
    } 

    The next code example defines KintoneContext class to access the Account datatable. This is done by overriding the OnConfiguring method, which invokes the UseKintone method of the passed DbContextOptionsBuilder object, to configure the context and establish a connection to the Kintone service.

    C#
    Copy Code
    public partial class KintoneContext : DbContext
        {
            public KintoneContext()
            {
                Database.AutoTransactionsEnabled = false;
            }
    
            public KintoneContext(DbContextOptions<KintoneContext> options)
                : base(options)
            {
                Database.AutoTransactionsEnabled = false;
            }
    
            public virtual DbSet<Accounts> Accounts { get; set; }
    
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                if (!optionsBuilder.IsConfigured)
                {
                    optionsBuilder.UseKintone("Username=yourusername;Password=**********;Url=https://*********");
                }
            }
    
            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<Accounts>(entity =>
                {
                    entity.Property(e => e.Id).HasColumnName("$id");
    
                    entity.Property(e => e.AccountId).IsRequired();
    
                    entity.Property(e => e.Assignee).ValueGeneratedOnAddOrUpdate();
    
                    entity.Property(e => e.Categories).ValueGeneratedOnAddOrUpdate();
    
                    entity.Property(e => e.CreatedBy)
                        .HasColumnName("Created_by")
                        .ValueGeneratedOnAddOrUpdate();
    
                    entity.Property(e => e.CreatedDate)
                        .HasColumnName("Created_date")
                        .ValueGeneratedOnAddOrUpdate();
    
                    entity.Property(e => e.RecordNumber)
                        .HasColumnName("Record_number")
                        .ValueGeneratedOnAddOrUpdate();
    
                    entity.Property(e => e.Sex).IsRequired();
    
                    entity.Property(e => e.Status).ValueGeneratedOnAddOrUpdate();
    
                    entity.Property(e => e.UpdatedBy)
                        .HasColumnName("Updated_by")
                        .ValueGeneratedOnAddOrUpdate();
    
                    entity.Property(e => e.UpdatedDate)
                        .HasColumnName("Updated_date")
                        .ValueGeneratedOnAddOrUpdate();
                });
            }
        }

    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.
    INSERT
    C#
    Copy Code
    public static void Insert(KintoneContext context)
    {
        Console.WriteLine("\nQuery Insert...");
        Accounts account = new Accounts
        {
            Name = "Account Insert " + DateTime.Now.Ticks.ToString(),
            AccountId = "116",
            Sex = "Male"
        };
    
        context.Accounts.Add(account);
    
        int result = context.SaveChanges();
        Console.WriteLine("Number row insert: " + result);
    }
    SELECT
    C#
    Copy Code
    public static void Select(KintoneContext context)
    {
        Console.WriteLine("Query all Accounts...");
        var records =
            from p in context.Accounts
            select p;
    
        foreach (var account in records)
        {
            Console.WriteLine("{0} - {1} - {2} - {3} - {4} - {5} - {6}",
                               account.RecordNumber, account.Name, account.Address, account.Sex, account.BirthDate, account.CreatedBy, account.CreatedDate);
        }
    }
    UPDATE
    C#
    Copy Code
    public static void Update(KintoneContext context)
    {
        Console.WriteLine("\nQuery Update...");
        var accounts = context.Accounts.Where(x => x.AccountId == "116");
    
        foreach (var account in accounts)
        {
            account.Age = 24;
            account.Address = "London, UK";
        }
    
        int result = context.SaveChanges();
    
        Console.WriteLine("Number row update: " + result);
    }
    DELETE
    C#
    Copy Code
        public static void Delete(KintoneContext context)
        {
            Console.WriteLine("\nQuery Delete...");
            var accounts = context.Accounts.Where(x => x.AccountId == "116");
    
            foreach (var account in accounts)
            {
                context.Accounts.Remove(account);
            }
    
            int result = context.SaveChanges();
    
            Console.WriteLine("Number row delete: " + 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.