Binding an entity to a new Sql DataType

Question

I use ASP.NET MVC5 and MS SQL 2008 and EntityFramework 6, in my application I have an Experiences class that will allow clients to add information about their experience before the period:

public class Experience { public int Id { get; set; } public string Title { get; set; } public string Company { get; set; } public string FromDate { get; set; } public string ToDate { get; set; } public string Description { get; set; } } 

FromDate format will be MMM yyyy, e.g. Jan 2009, Oct 2010, ... etc. the ToDate format will be either MMM yyyy or Present and must be greater than or equal to the FromDate value, for example:

• From = Jan 2010, To = Oct 2014 ==> Accepted
• From = Jan 2010, To = Present ==> Accepted
• From = Oct 2014, To = July 2014 ==> Rejected To To Date must be greater than the From date

I made the Period class as follows

 public class Period: IComparable { public Period() { this.Month="Present"; } public Period(string Month,int Year) { this.Month=Month; this.Year=Year; } public int CompareTo(object obj) { if(obj==null) return 1; Period period = obj as Period; if (period != null) { if(this.Month=="Present" && period.Month=="Present") return 0; else if(period.Month=="Present") return -1; else if(this.Month=="Present") return 1; else { DateTime date; DateTime periodDate; if(!DateTime.TryParse(string.Format("01 {0} {1}",Month,Year),out date)) throw new ArgumentException("Instance is not a valid Period!"); if(!DateTime.TryParse(string.Format("01 {0} {1}",period.Month,period.Year),out periodDate)) throw new ArgumentException("Object is not a valid Period!"); return date.Date.CompareTo(periodDate.Date); } } else throw new ArgumentException("Object is not a Period"); } public override int GetHashCode() { if(this==null) return 0; else { DateTime date; if(!DateTime.TryParse(string.Format("01 {0} {1}",Month,Year),out date)) throw new ArgumentException("Instance is not a valid Period!"); return date.GetHashCode(); } } public override bool Equals(object obj) { return this.CompareTo(obj)==0; } public static bool operator==(Period left, Period right) { return left.Equals(right); } public static bool operator!=(Period left, Period right) { return !(left==right); } public static bool operator<(Period left, Period right) { return left.CompareTo(right)<0; } public static bool operator>(Period left, Period right) { return left.CompareTo(right)>0; } public override string ToString() { if(string.IsNullOrWhiteSpace(Month)) return string.Empty; else if(Month=="Present") return Month; else return string.Format("{0} {1}",Month,Year); } public string Month{get; set;} public int Year{get; set;} } 

DEMO example

in sql i created the following data type

 create type Period from nvarchar(8) not null; 

Questions:

• How to determine period in sql?
• How can I tell EntityFramework to associate a period with a period of a data type in sql?

any idea would be appreciated

Answer 1

here's a workaround on how to do this

your experience class will be like this:

 public class Experience { public int Id { get; set; } public string Title { get; set; } public string Company { get; set; } public Period FromDate { get; set; } public Period ToDate { get; set; } public string Description { get; set; } } 

change the following in your period class to be [ComplexType]

 // change the folllowing [NotMapped] public string Month{get; set;} [NotMapped] public int Year{get; set;} // add this property to use it for EntityFramework mapping public string Date { get { return ToString(); } set { if (!string.IsNullOrEmpty(Date)) { if (Date == "Present") Month = "Present"; else { var split = Date.Split(' '); Month = split[0]; Year = Convert.ToInt32(split[1]); } } } } 

in your mapping you do the following

  modelBuilder.Entity<Experience>() .Property(t => t.FromDate.Date) .IsRequired() .HasMaxLength(8); modelBuilder.Entity<Experience>() .Property(t => t.ToDate.Date) .IsRequired() .HasMaxLength(8); 

so that the entity infrastructure creates fields as a period data type, you can do this 1- enable-migration 2- add-migration first // this will create a dbmigration file that has a database structure and tables 3- in the Up function at the end you write the following, using Sql (@ "") ;, here I will put sql instructions for you

 create function CheckPeriod(@period nvarchar(8)) -- this function will make sure that Period is valid returns bit as begin declare @month nvarchar(3) if ( @period is null ) return 0 if( @period = 'Present') return 1; set @month=substring(@period,0,3) if(@month in ('Jan','Feb','Mar','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')) return 1; return isdate(@period); end create function CheckValidity(@right as nvarchar(8),@left as nvarchar(8)) -- this function will compare FromDate to ToDate and return valid if ToDate>FromDate returns bit as begin if(@right='Present') return 0; if(@left='Present' and cast(@right as date)>=getdate()) return 0; if(@left='Present') return 1; if(cast(@left as date)>cast(@right as date)) return 1; return 0; end exec sp_addtype 'Period','nvarchar(8)','NOT NULL' alter table Experiences alter column FromDate Period alter table Experiences alter column ToDate Period alter table Experiences add constraint FromDateIsPeriod check ( dbo.CheckPeriod(FromDate)=1) alter table Experiences add constraint ToDateIsPeriod check ( dbo.CheckPeriod(ToDate)=1) alter table Experiences add constraint PeriodValidity check ( dbo.CheckValidity(FromDate,ToDate)=1) 

and in the Down function, if you want you to write code to drop functions and change table fields to return them as nvarchar (8) and finally delete the Period data type

hope this helps you