Database

Installation

I used Entity Framework Core (SQLite ver) for this project. To install this extension use the given command in NuGet terminal:

Install-Package Microsoft.EntityFrameworkCore.Sqlite -ProjectName YourProjectName

ERD Diagram

ERD diagram of database:

Code First approach

For the project I went for commonly used Code-First approach.

According to ERD diagram, Research table has one-to-many relationships with Article, Monograph, Presentation and Report tables.

Source code of Research class is shown below:

public sealed class Researcher
{
    public int Id { get; set; }

    public string LastName { get; set; }
    public string FirstName { get; set; }
    public string MiddleName { get; set; }
    public int DepartmentNumber { get; set; }
    public int Age { get; set; }
    public string AcademicDegree { get; set; }
    public string Position { get; set; }

    public ICollection<Article> Articles { get; set; }
    public ICollection<Monograph> Monographs { get; set; }
    public ICollection<Presentation> Presentations { get; set; }
    public ICollection<Report> Reports { get; set; }

    public Researcher()
    {
        Articles = new HashSet<Article>();
        Monographs = new HashSet<Monograph>();
        Presentations = new HashSet<Presentation>();
        Reports = new HashSet<Report>();
    }
}

Researcher table is connected to Report by one-to-many relationship so we put foreign key ResearchId and its object Research in Report class:

public class Report
{
    public int Id { get; set; }

    public string Name { get; set; }
    public int RegisterNumber { get; set; }
    public int ReleaseYear { get; set; }
    public int PageCount { get; set; }

    public int? ResearcherId { get; set; }
    public Researcher Researcher { get; set; }
}

DbContext set up

Let's have a look at ResDbContext class:

Open ResDbContext class

public class ResDbContext : DbContext
{
    public DbSet<Article> Articles { get; set; }
    public DbSet<Monograph> Monographs { get; set; }
    public DbSet<Presentation> Presentations { get; set; }
    public DbSet<Report> Reports { get; set; }
    public DbSet<Researcher> Researchers { get; set; }

    public ResDbContext()
    {
        Batteries.Init();
        Database.EnsureCreated();
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // configures one-to-many relationship
        modelBuilder.Entity<Researcher>()
            .HasMany(g => g.Articles)
            .WithOne(s => s.Researcher)
            .HasForeignKey(e => e.ResearcherId)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<Researcher>()
            .HasMany(g => g.Monographs)
            .WithOne(s => s.Researcher)
            .HasForeignKey(e => e.ResearcherId)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<Researcher>()
            .HasMany(g => g.Presentations)
            .WithOne(s => s.Researcher)
            .HasForeignKey(e => e.ResearcherId)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<Researcher>()
            .HasMany(g => g.Reports)
            .WithOne(s => s.Researcher)
            .HasForeignKey(e => e.ResearcherId)
            .OnDelete(DeleteBehavior.Cascade);

    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = "Researchers.db" };
        var connectionString = connectionStringBuilder.ToString();
        var connection = new SqliteConnection(connectionString);
        optionsBuilder.UseSqlite(connection);
    }
}

DbSet<> entity sets are used to perform CRUD operations with data.

public DbSet<Article> Articles { get; set; }
public DbSet<Monograph> Monographs { get; set; }
public DbSet<Presentation> Presentations { get; set; }
public DbSet<Report> Reports { get; set; }
public DbSet<Researcher> Researchers { get; set; }

Let's have a look on DbContent constructor:

public ResDbContext()
{
    Batteries.Init();
    Database.EnsureCreated();
}

Batteries.Init() method initializes SQLitePCL library.

In the case of receiving Exception System.Exception: 'You need to call SQLitePCL.raw.SetProvider(). If you are using a bundle package, this is done by calling SQLitePCL.Batteries.Init(). Check if you installed Microsoft.EntityFrameworkCore.Sqlite package but not Microsoft.EntityFrameworkCore and check dependency with SQLitePCL.raw. If this dependency not found try to download Micosoft.Data.Sqlite.

Database.EnsureCreated is a new method of EFC which ensures that the database for the context exists. If it exists, no action is taken. If it does not exist then the database and all its schema are created.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // configures one-to-many relationship
    modelBuilder.Entity<Researcher>()
        .HasMany(g => g.Articles)
        .WithOne(s => s.Researcher)
        .HasForeignKey(e => e.ResearcherId)
        .OnDelete(DeleteBehavior.Cascade);

}

OnModelCreating defines model configuration with Fluent API. As shown on the example above, for the Research entity one-to-many relationship with Article is defined by HasMany() method. For the complete relationship WithOne() method should be used with HasMany() according to Has/With pattern. HasForeignKey() sets foreign key for ResearchId in Article entity. To define the delete behavior OnDelete() method is used.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = "Researchers.db" };
    var connectionString = connectionStringBuilder.ToString();
    var connection = new SqliteConnection(connectionString);
    optionsBuilder.UseSqlite(connection);
}

The OnConfiguring() method allows us to select and configure the data source to be used with a context using DbContextOptionsBuilder. In the code above I initialize SqliteConnectionStringBuilder with Researchers.db (db file will be stored in the same folder as executable) as DataSource.