DevDiary

HOME

PROJECTS

EXTRA

LINKS

Database Schema (Entity Framework)

One-To-Many and Many-To-Many

There are five tables in the database; Authors, Books, Genres, Fictions and BookGenres.

I designed this database to include a number of "one-to-many" relationships and one "many-to-many" relationship.

The many-to-many relationship is between Books and Genres because a book can have many genres and a genre can be assoiciated with a number of books.

BookGenres is the bridge entity for the many-to-many relationship.

The screenshots below show the class files for a few of the tables.

Model (Book)

Model (Genre)

Model (Author)

Data Annotations

The [Required] data annotations is used to make the "name" property not nullable.

The [StringLength(100)] data annotation makes the data type in the SQL table "nvarchar(100)".

Without these annotations string properties are automatically set to null and string lengths are set to max.

Context Class

Context Class

The context class is used to retrieve enties from the database, persist new and changed entites to the database and remove entites from the database.

It inherits from the DbContext class and contains two DbSet properties. One for the "Book" entity and one for the "BookGenre" entity.

This means that I can write queries for these two entities.

This custom database initialiser inheits from an EF database initializer class. If the model changes then the database is dropped and recreated.

This "seed" code adds 7 authors, 9 books, 5 genres and 2 ficitons (ficiton and non-fiction).

NOTE: The DOBs are not correct.

Database Initializer

Seeding Database (Author)

Author Table (7 records)

Genre Table (5 records)

Book Table (9 records)

BookGenre Table (14 records)

SQL Query

BookGenre Table (SQL Query)

This Query results in a table with three columns.

Author Name, Book Title and Genre.

There are three INNER JOINS.

The table below show the result of the query and the other screenshot shows the same query but filtering for all books that belong to the genre "General"

BookGenre Table (SQL Query)

BookGenre Table (SQL Query), Filtering

LINQ Query: All Books in DB

Books in Database (9 Records)

LINQ Query: Filter by Author Name

Books in Database (2 Records)

LINQ Query: Filter by Contains("The")

Books in Database (4 Records)

LINQ Query: Filter by Genre (Literary)

Books in Database (3 Records)

SQL Query: Filter by Genre (Literary)

Books in Database (3 Records)

The LINQ query above generates this SQL query.