Q. Is it possible to have a Datetime field as a Key to a table in asp.net-mvc-core?

Question

If so, (which I now doubt), please supply details. My test is detailed here, I really need some help.

The server date format is dd/MM/yyyy.

Definition for table Test:

CalendarDate    datetime2(7)    Key
Name            nvarchar(50)
BirthDate       datetime2(7)

Data 2018-04-15 00:00:00.0000000 Joe 2018-04-15 00:00:00.0000000

Test.cs

 public partial class Test
    {
    public DateTime CalendarDate { get; set; }
    public string Name { get; set; }
    public DateTime BirthDate { get; set; }
    }

Context.cs

  modelBuilder.Entity<Test>(entity =>
        {
            entity.HasKey(e => new { e.CalendarDate });
            entity.Property(e => e.CalendarDate).HasColumnType("datetime2(7)");

            entity.Property(e => e.Name).HasMaxLength(50);
            entity.Property(e => e.BirthDate).HasColumnType("datetime2(7)");
        });

Scaffolded controller and views were then created. Note: No other formatting has been added.

Note: CalendarDate (the key) has been added to Index View for clarity.

Index Display:

Index

CalendarDate Name BirthDate

15/04/2018 12:00:00 AM Joe 15/04/2018 12:00:00 AM Edit |Details|Delete

Note in Index View

<a asp-action="Edit" asp-route-id="@item.CalendarDate">Edit</a>

does not find record but

@Html.ActionLink("Edit", "Edit", new { CalendarDate = item.CalendarDate })

does if id is changed to CalendarDate in controller.

Edit Display:

CalendarDate 04/15/2018 00:00:00 Name Joe BirthDate 2018-04-15T00:00:00.000

Note: CalendarDate (the key) has been added to Edit View for clarity.

Also note: Format displays as MM/dd/yyyy. Update fails.

Create works. If I create 01/01/2018 and then Edit, the Edit update works.

IE only works if day and month are equal.

Does anyone have a sample of a working function where the Key is a Datetime field please?

Yas Ikeda · Answer 1 · 2018-11-17T21:50:42.643

0

This is failing not because the id is DateTime, but because the id contains Slash Characters (/). Even when they are encoded, they are decoded and recognized as slash characters in URL, whereas the encoding is effective in query string, which is the part after ? in URL.

Therefore, <a asp-action="Edit" asp-route-id="@item.CalendarDate">Edit</a> generates URL like /Model/Edit/2%2F3%2F2018%2012%3A00%3A00%20AM, and it is actually recognized as /Model/Edit/2/3/2018 12:00:00 AM, which web app cannot find any match in the route map.

On the other hand, @Html.ActionLink("Edit", "Edit", new { CalendarDate = item.CalendarDate }) worked because it generates URL like Model/Edit?CalendarDate=03%2F03%2F2000%2001%3A00%3A00. In this case, it is routed to Model/Edit and the controller gets CalendarDate value.

One solution is to get rid of /{id?} from the following line in Startup.cs.

app.UseMvc(routes =>
{
    routes.MapRoute(
    name: "default",
    template: "{controller=Home}/{action=Index}/{id?}");
});

so, it'll look like

app.UseMvc(routes =>
{
    routes.MapRoute(
    name: "default",
    template: "{controller=Home}/{action=Index}");
});

then, you'll see the URL for Edit link will be generated like /Model/Edit?id=2%2F3%2F2018%2012%3A00%3A00%20AM, where the datetime value is in query string and not decoded, so that it is correctly passed to the controller.

If you don't want to change the routing functionality, another solution is to modify all the code like below

<a asp-action="Edit" asp-route-id="@item.CalendarDate">Edit</a>

to

<a asp-action="Edit" asp-route-id="@item.CalendarDate.ToString("o")">Edit</a>

.ToString("o") converts datetime value in ISO 8601 format. The format does not use slash characters, so that there's no problem to have it in URL.

Fortunately, where to modify is not many. Only three places in Index.cshtml and one in Details.cshtml. You can replace with a regular expression (@[^.]+\.CalendarDate) with replacement $1.ToString("o").

You can reduce the number of changes with this way comparing the way with @Html.ActionLink("Edit", "Edit", new { CalendarDate = item.CalendarDate }) because this solution does not require any change in controller.

edited Nov 17 '18 at 21:50

answered Nov 17 '18 at 21:37

Yas Ikeda

973
1
9
16

Edit works well. Routing and Edit now work ok. – Rod Nov 20 '18 at 01:28
I added this question for a simple table because I was not getting any response on my real problem. This was to do with composite keys (one of which was a datetime). I have a few tables in a design first app with this condition. Have applied your fix to them and all now ok with one further cleanup on a nice to have basis. I will Answer my original questions when I solve this. – Rod Nov 20 '18 at 01:35
On the Edit Displays, the CalendarDate displays as 2018-04-15T00:00:00.0000000 It is part of a key so I don't want to edit that field but it would be nice if I could display it as 15/04/2018. Am using Have tried but no change. Any thoughts ? – Rod Nov 20 '18 at 01:42
It seems `` works good for me. more details are required to solve that issue in Edit page. However, you don't need to solve this issue. As you mentioned, the field does not need to be edited, so that I think you can just keep the input tag as hidden, which is like ``, and have another for display, which is like `@Model. CalendarDate.ToString("dd/MM/yyyy")`. then, you can format and style it in whatever way easily. – Yas Ikeda Nov 20 '18 at 07:35
@Rod I found your question about your original problem, and noticed that you have `DisplayFormat` attribute. In this case, it seems `asp-format` in input tag in view code seems to be ignored. I wonder if you made some change in the `DisplayFormat` attribute in the model class that is something like `[DisplayFormat(DataFormatString = "{0:o}"`. isn't it the case? – Yas Ikeda Nov 20 '18 at 15:35
@Rod, by the way, please mark this answer as best answer if you would think it is qualified for that. thanks. – Yas Ikeda Nov 21 '18 at 03:04
Thanks Yas, tried [DisplayFormat(DataFormatString = "{0:o}". - no good, Index Display shows iso format and no change to Edit. Tried @Model. CalendarDate.ToString("dd/MM/yyyy") It works but because it is not an input, the lack of class makes the background a bit so-so. Eventually found this works: so now I am happy and will post an answer. – Rod Nov 22 '18 at 01:13

score 0 · Accepted Answer · answered Nov 22 '18 at 01:23

Thanks to Yas Ikeda for the following: The problem is to do with '/' in the urls. On the Index View I have changed the link from:

<a asp-action="Edit" asp-route-id="@item.CalendarDate">Edit</a>

to

<a asp-action="Edit" asp-route-id="@item.CalendarDate.ToString("o")">Edit</a>

This fixes the routing problem and the Edit now works ok.

Also refer to following for composite key problem/fixes.

Design First Datetime Key. My scaffolded controller is finding MM/dd/yyyy instead of dd/MM/yyyy

and

MVC 5 Date Format is displaying as MM/dd/yyyy in Edit when I want dd/MM/yyyy

Q. Is it possible to have a Datetime field as a Key to a table in asp.net-mvc-core?

2 Answers2