Entity Framework with User Table Value Type

Question

I have the following scenario:

• An entity called Degree that has a complex type Period
• I want to use the feature in sql User-Defined Table type

The entities code

public class Degree {
    public string Institution {get;set;}
    public string Major {get; set;}
    public Country Country {get; set;}
    public Period StartOn {get; set;}
    public Period EndOn {get; set;}    
}

public class Period {
    public int Year {get; set;}
    public int Month {get; set;}
    public bool IsPresent {get; set;}
}

The DbContext code

public class Context: DbContext
{
    public DbSet<Degree> Degrees {get; set;}

    public override void OnModelCreating(DbModelBuilder modelBuilder)
    {        
        modelBuilder.Entity<Degree>()
                    .Property(t => t.StartOn).HasColumnType("udt_period");
    }
}

** Error **

Error CS0453 The type 'Period' must be a non-nullable value type in order to use it as parameter 'T' in the generic type or method 'StructuralTypeConfiguration.Property(Expression>)'

How to let the entity framework to create the user defined table type and to use StartOn and EndOn as udt_period

Any help is highly appreciated

Answer 1

You cannot define a column in database table as User-Defined Table Type, usually its used as parameters for stored procedures.

You can define a User-Defined Data Type from the SQL server side as varchar(8) for example and you can use it in your solution.

The value of StartOn might be as 09/2014 or Sep 2014 and the value of EndOn might be as 11/2015 or Nov 2015 or Present , if you want to use 09/2014 format, then make the datatype as varchar(7) instead of varchar(8)

In order to apply the above recommendation, your code should be modified in the following way:

public class Degree {
    public string Institution {get;set;}
    public string Major {get; set;}
    public Country Country {get; set;}
    public string StartOn {get; set;}
    public string EndOn {get; set;}    
}

If you are using Code First approach, You can still create the user-defined data type from your application side if you are using Migrations, in the Seed method.

protected override void Seed(Context context)
{
    // create the type 
    var sql = "if type_id('udt_period') is null create type udt_period from varchar(8) not null";
    context.Database.ExecuteSqlCommand(sql);

    // you can alter the table column type
    var alter = "alter table Degrees alter column {0} udt_period not null"
    context.Database.ExecuteSqlCommand(string.Format(alter,"StartOn"));
    context.Database.ExecuteSqlCommand(string.Format(alter,"EndOn"));
}

Hope this will help you