Binding to MS SQL using EntityFramework 6

[Adam]

hello,

I'm trying to bind scheduler to MS SQL database using EntityFramework 6 and Code First approach. I did it partly but I have a big problem with saving exceptions of recurring appointment.

First of all, I can't find any sample of such (it would seem) common scenario where recurring appointment's exceptional occurrences are stored in database using EntityFramework 6. I would be very grateful if someone could show me such an example because Telerik documentation sample doesn't work.

I have very simple code where I configure RadScheduler, SchedulerBindingDataSource and mapping between entities and scheduler fields. I'm pretty sure my Code First entity model and database tables and relations are correct. Despite this, I'm not able to store in database exceptions of recurring appointments. Recurring appointments with removed occurrences are properly stored and removed dates are saved in recurrence rule of appointment. But occurrences with exceptional time or duration are not saved at all.

Please, help me.

This is my appointment entity with self reference for exceptions:

01.[Table("App")]
02.public class App
03.{
04.    public App()
05.    {
06.        this.Resources = new HashSet<Res>();
07.        this.Exceptions = new HashSet<App>();
08.    }
09.
10.    [Key]
11.    [DatabaseGenerated(DatabaseGeneratedOption.None)]
12.    public Guid Id { get; set; }
13.
14.    [Required(AllowEmptyStrings = false)]
15.    [StringLength(255)]
16.    public string Summary { get; set; }
17.
18.    public DateTime Start { get; set; }
19.
20.    public DateTime End { get; set; }
21.
22.    public string RecurrenceRule { get; set; }
23.
24.    public bool Visible { get; set; }
25.
26.    [ForeignKey("MasterEvent")]
27.    public Guid? MasterEventID { get; set; }
28.
29.
30.    public virtual App MasterEvent { get; set; }
31.
32.    public virtual ICollection<App> Exceptions { get; set; }
33.
34.    public virtual ICollection<Res> Resources { get; set; }
35.}

 

and my configuration of relations:

01.protected override void OnModelCreating(DbModelBuilder modelBuilder)
02.{
03.    base.OnModelCreating(modelBuilder);
04.
05.    modelBuilder.Entity<App>()
06.        .HasMany(t => t.Resources)
07.        .WithMany(t => t.Apps)
08.        .Map(m => {
09.            m.ToTable("AppRes");
10.            m.MapLeftKey("AppId");
11.            m.MapRightKey("ResId");
12.        });
13.
14.    modelBuilder.Entity<App>()
15.        .HasOptional(t => t.MasterEvent)
16.        .WithMany(t => t.Exceptions)
17.        .HasForeignKey(t => t.MasterEventID)
18.        .WillCascadeOnDelete(true);
19.}

 

and mapping:

01.var appointmentMappingInfo = new AppointmentMappingInfo()
02.{
03.    UniqueId = "Id",
04.    Start = "Start",
05.    End = "End",
06.    Summary = "Summary",
07.    RecurrenceRule = "RecurrenceRule",
08.    Exceptions = "Exceptions",
09.    MasterEventId = "MasterEventID",
10.    Resources = "Resources",
11.    ResourceId = "Id",
12.    Visible = "Visible"
13.};

[Dess | Tech Support Engineer, Principal]

Hello, Adam,  

I have prepared a sample project with a RadScheduler containing recurring appointments to test the behavior with an exception event. I have followed the approach demonstrated here: https://docs.telerik.com/devtools/winforms/controls/scheduler/data-binding/binding-to-entityframework-and-telerik-data-access 

When you edit an occurrence, it will create an exception event. It is necessary to set the AppointmentMappingInfo.Exceptions field to the name of the relation: 

Mapping the AppointmentMappingInfo.RecurrenceRule property will ensure which field from the data base will match the respective appointment record. Thus, you will be able to store the recurrence rule for each separate appointment.

However, indeed the additional record for the exceptional event is not properly stored in the database and the next time you run the application it is missing.

I have logged it in our feedback portal by creating a public thread on your behalf. You can track its progress, subscribe for status changes and add your comments on the following link - feedback item.

I have also updated your Telerik points.

Currently, the possible solution that I can suggest is  either to use the sample approach in the second project (see the attachments here) without using EF or add the additional appointment via code.

Feel free to use this approach which suits your requirements best. Please excuse us for the inconvenience caused. We will do our best to introduce an appropriate solution accordingly.

Regards,
Dess | Tech Support Engineer, Sr.
Progress Telerik

[Adam]

hello,

thank you for feedback and suggestion.

I would like to note that in the case of EntityFramework 6 DbContext as data source for scheduler, there is no underlying typed DataSet object like in the classic approach. Thus (imho), there is no way to use relation name like "FK_Appointments_Appointments" because in EF 6 CodeFirst data model there is only available a navigation property making relation to associated entity. In my code sample I had to map navigation property "Resources" of appointment entity to scheduler field "Resources" to make resources relation work. The same should be in the case of Exceptions relation. In the case of resources relation, assigning database relation name like "FK_Appointments_Resources" to Resources field of mapping object when scheduler is bound to EF6 CodeFirst DbContext data source it doesn't work.

[Dess | Tech Support Engineer, Principal]

Hello, Adam, 

You are right. With Entity Framework it is necessary to use the navigation property, not the name of the relation. Here it is for the resources:

A similar approach should be followed for the Exception appointments:

We will update our documentation related to EF in order to avoid any further confusion: https://docs.telerik.com/devtools/winforms/controls/scheduler/data-binding/binding-to-entityframework-and-telerik-data-access 

We are currently working on this issue regarding the exception events and we will do our best to introduce a fix accordingly. Please make sure that you follow the previously referred public feedback item. Thus, you will get notified once the status of the item changes.

Should you have further questions please let me know.

Regards,
Dess | Tech Support Engineer, Sr.
Progress Telerik