Entity Framework Core 8, table with a user-defined data type. UDT

Question

We are migrating a 4.5 large application which uses sql data reader to query the MS SQL database to .NET 8 EF core. The database has at least one user-defined column, code below.

There is currently 20 years of data in the database. We are migrating the application in stages. Am I able to have both .NET 4.5 and .NET 8 EF core read / write the same tables.

I have had a few attempts but going around in circles in terms of assembly version etc

Guidance on the approach is welcome :-)

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

namespace Signmanager.SqlServer.Types

{

/// <summary>

/// Represents a Currency UDT

/// </summary>

/// <remarks>

/// It is critical that this not be modified once released since any modifications

/// require that all columns that use this UDT will need to be DROPPED before

/// the new version is released.

/// </remarks>

[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native,

IsByteOrdered = true, ValidationMethodName = "ValidateCurrency")]

public struct currency : INullable

{

private SqlMoney amount;

private SqlDateTime transactionDate;

private SqlInt32 typeId;

private bool isNull;

/// <summary>

/// Returns the fully qualified type name of this instance.

/// </summary>

/// <returns>

/// A <see cref="T:System.String"/> containing a fully qualified type name.

/// </returns>

public override string ToString()

{

if (isNull == true)

return string.Empty;

else

// return string representation of CurrencyUdt including currency, amount & datetime

return string.Format("{0},{1},{2}", amount.ToString(), typeId.ToString(), transactionDate.ToString());

}

/// <summary>

/// Gets a value indicating whether this instance is null.

/// </summary>

/// <value><c>true</c> if this instance is null; otherwise, <c>false</c>.</value>

public bool IsNull

{

get

{

// Put your code here

return isNull;

}

}

/// <summary>

/// Gets the null.

/// </summary>

/// <value>The null.</value>

public static currency Null

{

get

{

currency h = new currency();

h.isNull = true;

return h;

}

}

/// <summary>

/// Parses the specified string.

/// </summary>

/// <param name="s">The s.</param>

/// <returns></returns>

public static currency Parse(SqlString s)

{

// will follow the same format as the .ToString() so that editing data in the table in

// Management Studio can be ...

if (s.IsNull)

return Null;

currency currencyUdt = new currency();

// string may contain 1, 2 or 3 parametres separated by commas e.g.:

// "45.67"

// "45.67, 3"

// "45.67,3,24/10/2007"

// "45.67,3,24/10/2007 3:44:51 AM"

// so first create an array based on the string passed in

string[] array = s.ToString().Split(',');

// used for parsing

int currencyTypeId;

float amount;

// the length of the array will now determine how many parameters were in the string

switch (array.Length)

{

case 1:

// 1 parameter will be amount in $AUD and UTCNow

// Amount

if (float.TryParse(array[0], out amount) == true)

currencyUdt.amount = SqlMoney.Parse(array[0]);

else

throw new InvalidOperationException("The first value in the comma separated string is the amount and must be able to be parsed to a float.");

// CurrencyTypeId

currencyUdt.typeId = 2; // currently $AUD

// TransactionDate

currencyUdt.transactionDate = DateTime.UtcNow;

break;

case 2:

// 2 paramters will be amount in currencyTypeId and UTCNow

if (float.TryParse(array[0], out amount) == true)

currencyUdt.amount = SqlMoney.Parse(array[0]);

else

throw new InvalidOperationException("The first value in the comma separated string is the amount and must be able to be parsed to a float.");

// CurrencyTypeId

if(int.TryParse(array[1], out currencyTypeId) == true)

currencyUdt.typeId = new SqlInt32(currencyTypeId);

else

throw new InvalidOperationException("The second value in the comma separated string is the currency type id and must be able to be parsed to an int.");

// TransactionDate

currencyUdt.transactionDate = DateTime.UtcNow;

break;

case 3:

// 3 paramters will be amount, transactionDate, typeId

// Amount

if (float.TryParse(array[0], out amount) == true)

currencyUdt.amount = SqlMoney.Parse(array[0]);

else

throw new InvalidOperationException("The first value in the comma separated string is the amount and must be able to be parsed to a float.");

// CurrencyTypeId

if(int.TryParse(array[1], out currencyTypeId) == true)

currencyUdt.typeId = new SqlInt32(currencyTypeId);

else

throw new InvalidOperationException("The second value in the comma separated string is the currency type id and must be able to be parsed to an int.") ;

// TransactionDate

DateTime transactionDate;

// parse the string into a datetime

if((DateTime.TryParse(array[2], out transactionDate) == false))

throw new InvalidOperationException("The third value in the comma separated string is the transaction date (utc) and must be in a format that can be parsed to a DateTime");

// set the transactiondate of the Udt

currencyUdt.transactionDate = new SqlDateTime(transactionDate);

break;

default:

// error if not 1, 2 or 3

throw new InvalidOperationException("The string to be parsed must be in the format 'Amount[,CurrencyTypeId[, TransactionUtcDate]]'.");

}

if (IsValid(currencyUdt) == false)

{

// error if not 1, 2 or 3

throw new InvalidOperationException("The string to be parsed must be in the format 'Amount[,CurrencyTypeId[, TransactionUtcDate]]'.");

}

return currencyUdt;

}

#region Properties

/// <summary>

/// Gets or sets the type id.

/// </summary>

/// <value>The type id.</value>

public SqlInt32 TypeId

{

get

{

return this.typeId;

}

set

{

SqlInt32 temp = this.typeId;

this.typeId = value;

if (!ValidateCurrency())

{

this.typeId = temp;

throw new ArgumentException("Invalid Currency value.");

}

}

}

/// <summary>

/// Gets or sets the amount.

/// </summary>

/// <value>The amount.</value>

public SqlMoney Amount

{

get

{

return this.amount;

}

set

{

SqlMoney temp = this.amount;

this.amount = value;

if (!ValidateCurrency())

{

this.amount = temp;

throw new ArgumentException("Invalid Amount value.");

}

}

}

/// <summary>

/// Gets or sets the transaction date.

/// </summary>

/// <value>The transaction date.</value>

public SqlDateTime TransactionDate

{

get

{

return this.transactionDate;

}

set

{

SqlDateTime temp = this.transactionDate;

this.transactionDate = value;

if (!ValidateCurrency())

{

this.transactionDate = temp;

throw new ArgumentException("Invalid DateTime value.");

}

}

}

#endregion

/// <summary>

/// Validates the currency.

/// </summary>

/// <returns></returns>

private bool ValidateCurrency()

{

return currency.IsValid(this);

}

/// <summary>

/// Validates the currency.

/// </summary>

/// <returns></returns>

private static bool IsValid(currency currency)

{

// determine whether the Udt is valid:

// typeId should be: >= 0

// amount should be: no restrictions

// transactionDate should be: date restrictions

if (currency.typeId < 0)

return false;

else

return true;

}

}

}

Answer 1

Hi,@Phil Alexander. Welcome to Microsoft Q&A. 

To read currency using Microsoft.EntityFrameworkCore.SqlServer in .Net 8, you could refer to the following method.

 

Assume the table structure is as follows

CREATE TABLE currency

(

    [TypeId] INT NOT NULL PRIMARY KEY,

    [Amount] MONEY NULL,

    [TransactionDate] DATETIME NULL

)

 

Change currency to reference type

public struct currency : INullable

To

public class currency : INullable

 

DbContext

using Microsoft.EntityFrameworkCore;

using Microsoft.EntityFrameworkCore.Storage.ValueConversion;

using System.Data.SqlTypes;

 

public partial class MyDbContext : DbContext

{

    public MyDbContext()

    {

    }

 

    public MyDbContext(DbContextOptions<MyDbContext> options)

        : base(options)

    {

    }

 

    public virtual DbSet<currency> Currencies { get; set; }

 

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

        => optionsBuilder.UseSqlServer("Your database connection string");

 

    protected override void OnModelCreating(ModelBuilder modelBuilder)

    {

        modelBuilder.Entity<currency>(entity =>

        {

            entity.ToTable("currency");

 

            entity.HasKey(e=>e.TypeId);

 

            entity.Property(e => e.TypeId).HasConversion(new ValueConverter<SqlInt32, int>(q =>q.Value, w =>new SqlInt32(w)));

 

            entity.Property(e => e.Amount).HasConversion(new ValueConverter<SqlMoney, decimal>(q => q.Value, w => new SqlMoney(w)));

 

            entity.Property(e => e.TransactionDate).HasConversion(new ValueConverter<SqlDateTime,DateTime>(q=>q.Value,w=>new SqlDateTime(w)));

        });

    }

}

 

Reading Data

MyDbContext myDbContext = new MyDbContext();

var list =  myDbContext.Currencies.ToList();

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.