Querying in Entity Framework Core
If you are new to EF-Core and you don't know how to convert your existing SQL queries to LINQ to entities queries, these exercises will help you to get started.
These samples are ported from PostgreSQL Exercises to EF-Core.
Simple SQL Queries
- Retrieve everything from a table
- Retrieve specific columns from a table
- Control which rows are retrieved
- Control which rows are retrieved - part 2
- Basic string searches
- Matching against multiple possible values
- Classify results into buckets
- Working with dates
- Removing duplicates, and ordering results
- Combining results from multiple queries
- Simple aggregation
- More aggregation
- Produce a list of facilities that are booked only in 2012
Joins and Sub-queries
- Retrieve the start times of members' bookings
- Work out the start times of bookings for tennis courts
- Produce a list of all members who have recommended another member
- Produce a list of all members, along with their recommender
- Produce a list of all members who have used a tennis court
- Produce a list of costly bookings
- Produce a list of all members, along with their recommender, using no joins.
- Produce a list of costly bookings, using a sub-query
- Produce a list of facilities which are not booked in 2013
Modifying data
- Insert some data into a table
- Insert multiple rows of data into a table
- Insert calculated data into a table
- Update some existing data
- Update multiple rows and columns at the same time
- Update a row based on the contents of another row
- Delete all bookings
- Delete a member from the cd.members table
- Delete based on a sub-query
Aggregation
- Count the number of facilities
- Count the number of expensive facilities
- Count the number of recommendations each member makes.
- List the total slots booked per facility
- List the total slots booked per facility in a given month
- List the total slots booked per facility per month
- Find the count of members who have made at least one booking
- List facilities with more than 1000 slots booked
- Find the total revenue of each facility
- Find facilities with a total revenue less than 1000
- Output the facility id that has the highest number of slots booked
- List the total slots booked per facility per month, part 2
- List the total hours booked per named facility
- List each member's first booking after September 1st 2012
- Produce a list of member names, with each row containing the total member count
- Produce a numbered list of members
- Output the facility id that has the highest number of slots booked, again
- Rank members by (rounded) hours used
- Find the top three revenue generating facilities
- Classify facilities by value
- Calculate the payback time for each facility
- Calculate a rolling average of total revenue
- Produce running total count of bookings for each year
- Bookings count per year and sales growth as compared with the previous year
Working with Timestamps
- Produce a timestamp for 1 a.m. on the 31st of August 2012
- Subtract timestamps from each other
- Generate a list of all the dates in October 2012
- Get the day of the month from a timestamp
- Work out the number of seconds between timestamps
- Work out the number of days in each month of 2012
- Work out the number of days remaining in the month
- Work out the end time of bookings
- Return a count of bookings for each month
- Work out the utilization percentage for each facility by month
- Find registered members in given months
- Produce quarterly count of booked items in 2012 and 2013
String Operations
- Format the names of members
- Find facilities by a name prefix
- Perform a case-insensitive search
- Find telephone numbers with parentheses
- Pad zip codes with leading zeroes
- Count the number of members whose surname starts with each letter of the alphabet
- Clean up telephone numbers