Skip to content

Latest commit

 

History

History
158 lines (105 loc) · 7.3 KB

readme.md

File metadata and controls

158 lines (105 loc) · 7.3 KB
Raw

Entity Framework Core working with dates

Provides several useful examples for working with dates in Entity Framework Core 5, SQL-Server using C# with Windows Forms although the base code presented can be used in other project types.

More samples will be added over time.

Screen1

This solution provides:

  • Code sample to display a DateTimePicker in a DataGridView loaded using Entity Framework Core 5.
    • From the basic code shown, it's easy to provide add, edit, delete, filtering and searching which are not shown to keep things basic. Several other projects will be added to step through add, edit, delete, filtering and searching building up the initial code sample.
  • Code sample for obtaining data using an extension methof for dates between a specific date.

The code sample has been built off the following two repositories.

Requires

  • Microsoft Visual Studio 2019 or higher
  • .NET 5 or higher
  • C# 9 or higher
  • Microsoft Entity Framework Core 5.x
  • Microsoft SQL-Server (good to have SQL-Server Management Studio)

Create/populate database

Script to run is script.sql in the DataScripts folder off the root of the solution. Before running inspect the path the database will be created as different versions of SQL-Server may have a different location.

Connection to database

By default when reverse engineering a database via scaffolding the connection string is hard coded in the DbContext, in this code sample the connection string is read from the following json files, appsettings.json.

{
  "database": {
    "DatabaseServer": ".\\SQLEXPRESS",
    "Catalog": "DateTimeDatabase",
    "IntegratedSecurity": "true",
    "UsingLogging": "true"
  }
}

Code in Context class

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    if (!optionsBuilder.IsConfigured)
    {
        optionsBuilder.UseSqlServer(BuildConnection());
    }
}

private static string BuildConnection()
{

    var configuration = new ConfigurationBuilder()
        .AddJsonFile("appsettings.json", true, true)
        .Build();

    var sections = configuration.GetSection("database").GetChildren().ToList();

    return
        $"Data Source={sections[1].Value};" +
        $"Initial Catalog={sections[0].Value};" +
        $"Integrated Security={sections[2].Value}";

}

Extension methods for querying dates between

We must use IQueryable for deferred execution (Deferred execution means that the evaluation of an expression is delayed until its realized value is actually required). Or write the, in this case conventional perdicate directly in a Enumerable.Where Method. To keep business code clean and reusable these extension do the trick.


public static class DateExtensionHelpers
{

    public static IQueryable<Events> BetweenStartDate(this IQueryable<Events> events, DateTime startDate, DateTime endDate) 
        => events.Where(@event 
            => startDate <= @event.StartDate && @event.StartDate <= endDate);

    public static IQueryable<Events> BetweenEndDate(this IQueryable<Events> events, DateTime startDate, DateTime endDate)
        => events.Where(@event 
            => startDate <= @event.EndDate && @event.EndDate <= endDate);

    public static IQueryable<Birthdays> BirthDatesBetween(this IQueryable<Birthdays> events, DateTime startDate, DateTime endDate)
        => events.Where(@event
            => startDate <= @event.BirthDate && @event.BirthDate <= endDate);


}

To filter a table with birthdays in BirthdayForm BirthDatesBetween extension above is used.

public static Task<List<Birthdays>> GetBirthdaysList(DateTime startDate, DateTime endDate) 
    => Task.Run(async () 
        => await Context.Birthdays.BirthDatesBetween(startDate, endDate)
            .OrderBy(birthday => birthday.BirthDate).ToListAsync());

In BirthdayForm Shown event a date range is pre-defined. For a real application the range may be defined with two DateTimePickers or custom date selector.

var startDate = new DateTime(1953, 1, 2);
var endDate = new DateTime(1956, 9, 24);

_birthdaysList = new BindingList<Birthdays>(
    await DataOperations.GetBirthdaysList(startDate, endDate));

Comment If the hard coded dates are changed then validate the results in SSMS.

DataGridView with calendar column

In EventsForm, data is loaded into a BindingList, DataGridView columns are predefined in the grid designer. When working with dates in a DataGridView this using a custom column for dates is user friendly, if a time column is needed see the following repository DataGridView custom columns.

  • This is a read-only demonstration, see learn how to perform add, edit, delete see the following repository WinForms DataGridView with EF Core 5.
  • There is a DateTimePicker data bound to the data which implements INotifyPropertyChanged which when changing a date does not reflect in the DataGridView while changing the date in the DataGridView updates the DateTimePicker which means there is an issue with data binding with a DateTimePicker so to fix this the following code is needed.
private void BirthDateDateTimePickerOnValueChanged(object sender, EventArgs e)
{
    if (DataIsNotAccessible()) return;

    var current = (Person1)_bindingSource.Current;

    current.BirthDate = BirthDateDateTimePicker.Value;

}

Calclulating age

A common task is to determine the age of a person See under LanguageExtensions, Helper class which provides this ability to calculate age along with Age class under Classes folder.

Resources