Posting Time value to Universe database from .NET Entity Framework

Question

I have a C# application that receives JSON data and writes a model back to Universe. In a number of models, I need to separate DATE and TIME fields and write them back individually when the model is created.

NET Framework V4.6.1, U2.Data V2.2.2 NuGet package, U2NDK V2.2.2, Universe V11.2.5

I created the model in Universe using RocketU2:

@ID               TYPE          LOC          CONV    ...   DATA TYPE
Id                D             0                          CHAR(30)
AdjustmentDate    D             1            D4-           DATE
AdjustmentTime    D             2            MTS           TIME

My first reaction was to write this in the controller:

AdjustmentTime = DateTime.Now.TimeOfDay;

And this in the C# app models:

public TimeSpan AdjustmentTime { get; set; }

This resulted in the error: "There is no store type corresponding to the EDM type 'Edm.Time' of primitive type 'Time'."

There were a number of questions regarding how to post time separately to a database. So I've tried those, and here is the result:

ProductAdjustmentController:

[Route("")]
        [HttpPost]
        public HttpResponseMessage PostProductAdjustment([FromBody] ProductAdjustmentCreate productAdjustmentCreate)
        {
            ResponseCollectionMember _response = new ResponseCollectionMember();

            try
            {

                var id = productAdjustmentCreate.ProductId + "*" + productAdjustmentCreate.AdjustmentDate.ToString() + "*" + productAdjustmentCreate.AdjustmentTime.ToString();

                var productAdjustmentEntity = new ProductAdjustmentEntity()
                {
                    Id = id,
                    AdjustmentDate = DateTime.Now.Date,
                    AdjustmentTime = DateTime.Now.TimeOfDay.Ticks,
                };

                _context.ProductAdjustments.Add(productAdjustmentEntity);
        }

ProductAdjustmentEntity model:

public class ProductAdjustmentEntity : FileBase<string>
    { 
        public string ProductId { get; set; }
        public DateTime? AdjustmentDate { get; set; }
        public long AdjustmentTime { get; set; }
        [NotMapped]
        public TimeSpan Time
        {
            get { return TimeSpan.FromTicks(AdjustmentTime); }
            set { AdjustmentTime = value.Ticks; }
        }
    }

ProductAdjustmentCreate model:

public class ProductAdjustmentCreate
    {
        [Required]
        public string ProductId { get; set; }
        public DateTime? AdjustmentDate { get; set; }
        public long AdjustmentTime { get; set; }
        [NotMapped]
        public TimeSpan Time
        {
            get { return TimeSpan.FromTicks(AdjustmentTime); }
            set { AdjustmentTime = value.Ticks; }
        }
    }

I get the same unsupported primitive type error for a different type: "There is no store type corresponding to the EDM type 'Edm.Int64' of primitive type 'Int64'."

I dug down into the U2.Data package and found that U2.Data.ClientTypes.Int64 and Time DO exist. The last thing I tried was creating a method that would cast my model creation values into the U2-accepted value:

public static U2Int64 ConvertToU2Int64(long time)
        {
            U2Int64 u2Time = time;
            return u2Time;
        }

But all this does is return null.

I just want this to work so I can test posting the model to Universe.

My questions for working through this process: Is there a simple way to do this? How would I get the primitive types to be recognized? How would I write a method to convert them that I could use throughout the app?

Answer 1

Generically, you are trying to find a datatype for a column (well, in RDBMS terms) that plays nice with the dotnet datatype.

Here is a MySql .. AS AN EXAMPLE.. of this kind of mapping.

https://www.devart.com/dotconnect/mysql/docs/DataTypeMapping.html

After you find the magic datatype..you have to tell EF to use it (instead of relying on defaults)

https://learn.microsoft.com/en-us/ef/core/modeling/entity-properties?tabs=data-annotations%2Cfluent-api%2Cwithout-nrt#column-data-types

public class Blog
{
    public int BlogId { get; set; }

    [Column(TypeName = "varchar(200)")]
    public string Url { get; set; }

    [Column(TypeName = "decimal(5, 2)")]
    public decimal Rating { get; set; }
}

in the above, the attribute

[Column(TypeName = "varchar(200)")]

is overwriting any default......

I don't have any Rocket DB installed, so I cannot try anything concretely.

But maybe

[Column(TypeName = "MD2")]

or

[Column(TypeName = "MR2")]

?

If you prefer "fluent" for your ORM mappings, it would be "HasColumnType".

https://www.learnentityframeworkcore.com/configuration/fluent-api/hascolumntype-method

Answer 2

I have a static class I use to convert time values into the Pick time structure which is simply the number of seconds since midnight. When you call TIME() in Universe it returns a double.

The hard part in truth is determining when midnight is based on what kind of DateTime value you are working with. This is why I have two different versions to use DateTime and DateTimeOffset.

I also have a third method that recombines them.

public static class Time
{
    public static double GetPickTimeFromDateTime(DateTime dt)
    {
        DateTime midnight = DateTime.Parse(dt.Date.ToString());
        TimeSpan ts = dt - midnight;
        return ts.TotalSeconds;
    }
    public static double GetPickTimeFromDateTimeOffset(DateTimeOffset dt)
    {
        DateTimeOffset midnight = DateTime.Parse(dt.Date.ToString());
        TimeSpan ts = dt - midnight;
        return ts.TotalSeconds;
    }
    public static DateTime GetDateTimeDateAndPickTime(DateTime date, double pickTime)
    {
        return date.AddSeconds(pickTime);
    }
}