Monthly Archives: November 2015

Entity Framework, TimeSpan & SQL Server Time

Data Access

In the “hey, you cant know everything” category….

A client asked me a question today about mapping TimeSpan to the Time data type in SQL Server.

I didn’t know there was a Time data type in SQL Server. I’m well aware of the Date data type we got in SS2008, but I skipped right past that other new type.

Since I never worked with a Time data type, I never had occasion to use it with EF. So when my client said something about timespan, I thought “that can’t be right”. Timespan is about intervals, not a point in time. Then he said something about the DateTime.TimeOfDay property which returns a timespan and I was all

bsi

So….

step 1:create a table in a SQL Server Database that has a Date field and a Time field.

image

step 2: reverse engineer to code first (I used the EF6 designer)

  public partial class SeparatedDateTime
    {        
        public int Id { get; set; }

        [StringLength(50)]
        public string somestring { get; set; }

        [Column(TypeName = "date")]
        public DateTime justDate { get; set; }

        public TimeSpan justTime { get; set; }
    }
ok so there it is: TimeSpan
step 3: write some code to test it out:
    private static void InsertSeparatedDateTime() {

      using (var context = new DateTimeTester()) {
        context.SeparatedDateTimes.Add(CreateSepDT());
        context.Database.Log = Console.WriteLine;
        context.SaveChanges();
      }
    }

    private static SeparatedDateTime CreateSepDT() {
      var sepDT = new SeparatedDateTime();
      sepDT.somestring = "A Test";
      sepDT.justDate = DateTime.Now.Date;
      sepDT.justTime = DateTime.Now.TimeOfDay;
      return sepDT;
    }
step 4: debug. 
check sepDT.justTime value and it looks like this : it is the milliseconds since midnight in many flavors
image
The log shows me that EF has transformed that value into a time value for the insert:
image
And surely it does the opposite when reading that data back out of the database.
Some caveats:
  • 24 hr time only
  • Presumes you calculated that timespan value correctly
  • All caveats associated with UTC
 
 
 
 
 

Recent Data Points Columns: Aurelia, Azure DocumentDB, Scriptcs, EF6, EF7, ASP.NET 5

Data Access, , , ,

Data Points – Aurelia Meets DocumentDB:
A Matchmaker’s Journey
(Part 1)

*Part 2 will be in Dec 2015 issue

Nov 2015

After exploring both DocumentDB and the new Aurelia framework, Julie Lerman decided it was time to use them together, but that proved more difficult than expected. Learn how she finally found the path to the correct solution.

Read article

Data Points – Revisiting JavaScript Data Binding — Now with Aurelia

Sep 2015

Julie Lerman has long been a fan of the Knockout JavaScript framework and its data binding talents. Here she explores the new Aurelia framework, which is quickly gaining momentum with Web developers, and finds that it offers a lot to like for data-oriented programmers.

Read article

Explore Entity Framework Behavior at the Command Line with Scriptcs

Jul 2015

Julie Lerman learns how to explore Entity Framework behavior interactively using Scriptcs.

Read article

An Overview of Microsoft Azure DocumentDB

Jun 2015

Julie Lerman takes a first look at the Microsoft entry into the document database field—Azure DocumentDB.

Read article

The EF6, EF7 and
ASP.NET 5 Soup

May 2015

Julie Lerman discusses the difference between the next version of .NET and what ASP.NET 5 apps will run on, then explains how Entity Framework 6 and Entity Framework 7 fit into the mix.

Read article

EF6 Code First Migrations for Multiple Models

Apr 2015

The new support for Code First Migrations in Entity Framework 6 makes it easier to store data for multiple models in a single database. But that support may not be what you imagine. Julie Lerman explains what this feature does and doesn’t do, and how to use it.

Read article

Looking Ahead to
Entity Framework 7

Jan 2015

In this first look at EF7, Julie Lerman discusses what EF7 will bring to developers, the motivations behind decisions being made about the framework, and what this version means to existing apps that use EF6 or earlier.

Read article

EF7 EnsureCreated vs. Migrate Methods

Data Access,

I had a “demo gods” problem recently…of course I hit the jackpot combo during a conference presentation though never while practicing.

Remember that EF7 is not EF6 with new features. So behavior can change and I didn’t check this one.

In EF6 if you have migrations and either let DbInitialization create the database or use Database.Create(), the database will get created along with the migrations in the MigrationHistory table.

With EF7 if you use EnsureCreated to create the database at runtime, as I had in some of my tests where I literally wanted to check behavior in the database, the migration history table DOES NOT GET CREATED.

This messed me up because I was demoing a migrations feature after having run one of these tests. With the migration history table wiped out by my test, the update-database command attempted to start with the very first migration, not the one I had just added in my demo. This caused a conflict because that migration was attempting to create a table that already existed in the database.

I couldn’t figure out what the problem was on the fly, so I moved on, with assurances to the attendees that I’d done a lot of work with migrations and new they worked as expected. And that a) it was a beta b) I would run it by the team and c) “you can trust me”.

So, blaming it on “beta” was not fair and it was not right. The blame lay between the chair and the keyboard, as they say. I hadn’t even run that test in my conference session yet.  But I had re-run all fo the tests one last time right before I started the session, which put the database in this bad state.

Here, from one of EF7 issues raised on Github is Rowan Miller’s explanation:

EnsureCreated totally bypasses migrations and just creates the schema for you, you can’t mix this with migrations. EnsureCreated is designed for testing or rapid prototyping where you are ok with dropping and re-creating the database each time. If you are using migrations and want to have them automatically applied on app start, then you can use context.Database.Migrate() instead.

So I’ve gone back to my tests and made sure the ones that use the database make use of the Migrate command.

My anticipated uses:

  • EnsureCreated for on the fly DB creation on devices (e.g. phones)
  • EnsureCreated for integration testing when I’m pointing to a unique testing-only database.
  • Migrate for integration testing when my tests involve a test database that uses migrations. I think this will be rare and this is what I wanted for my conference demo.
  • Migrate for, as Rowan suggests, Migrating a db on app startup. This could be for example, client apps (ala WPF) or web sites you’ve deployed to Azure.

Keep in mind that these demos where Migrate made more sense, are demos I am using to help people understand how EF7 works, not to demonstrate how to build tests when employing Entity Framework in your application. So some of the things I am doing are not necessarily how I would use tests when building software.