Dropping FullText in SQL Server

Many months ago I had watched a Tech Ed demo by Bob Beauchemin, an active blogger over at SQL Skills on how to setup FileTable in SQL Server 2012. Links to Part 1 - Filestreams and filetables in SQL Server 2012 and to Part 2 - Integrating SQL Server Filetables, Property Search, and FTS/Semantc Search.

Before going any further I want to ensure 100% credit to Bob's wonderful keen insight on FileTable, FileStream and SQL Server.

The issue at hand:
Oddly enough I had a warning when altering my FileTable in SQL Server 2012 today. Normally it is a simple process of two commands:

Unable to modify table. Table contains at least one fulltext index. 

Ok so when all else fails use T-SQL

DROP FULLTEXT INDEX ON MYDB..MyFileTable
GO
DROP FULLTEXT CATALOG MyFileTableFullTextCatalog
GO

Ran the commands and got the same error. This happened because I shared an Index pre-made by SQL Server when I built the FileTable which Bob explains in more detail in his post.

stream_id unique index.

No big deal...I saw that one coming at least. Apparently when SSMS is making changes to a table it creates a temp table and places a session lock on the table. Fair enough. Rebooted the instance ran the commands above and problem solved.

The Reason:
This is to get around a defect with Entity Framework not having compatibility with HeiarchyId as a  SQL Data Type. The EF team stated in their blog that EF6 probably won't have support for this data type either. EF will support a read only view though, say something like this:

USE [DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[MyFileTable]
AS
----
SELECT
MyFT.stream_id,
--Transform and omit path locator and path locator parent.
FileTableRootPath() + MyFT.file_stream.GetFileNamespacePath() AS FileNamespacePath, 
MyFT.name, 
-- Omit these two fields to strings
--MyFT.path_locator,
--MyFT.parent_path_locator, 
-- Remaining normal fields.
MyFT.file_type, MyFT.cached_file_size, MyFT.creation_time, MyFT.last_write_time, 
MyFT.last_access_time, MyFT.is_directory, MyFT.is_offline, MyFT.is_hidden, MyFT.is_readonly, MyFT.is_archive, MyFT.is_system, MyFT.is_temporary
FROM MyFileTableNameInDbo AS MyFT
----
GO

For the moment this will work. I will expand on this after I give it more thought. Perhaps it would be better to use a AFTER UPDATE Trigger to a real USER_TABLE with just the stream_id (PK), the path_locator as a string via FileNamespacePath in the script above. Finally I will include the name of the file. This way I can use foreign key constraints (which views do not support).

This new "real" table can only be updated by this trigger. The downside is that the EF will not support the additional features offered by FileTable (yet!)

Sources:

• [1]Using filetable with full-text? Lose the redundant index
• [2]Entity Framework 6

Part1: Migrating from EF 4.0 to EF5.0

Warning! Multiple blog post article. I would advise not proceeding until all the articles are done.

Since it is a patch Tuesday for me I figured it would be a great time to upgrade my EF instance from 4.0 to 5.0. Please note these are my own musings.

Prerequisites: Install .Net 4.5 on Windows 7 available at Microsoft's website: here. Patch your rig with latest windows updates Watch progress bars (optional) Drink red bull (recommended) Verified all files are checked into TFS Made full backup of SQL Server (Both the TFS DB and the website DB).

While I was waiting I decided to look for a new visual style for VS2010 since the monitors on my desk were always using a light background leading to headaches and eye strain. I ended up settling on "The Dark Side of Visual Studio" which you can download yourself: here. Full credit to InfinitiesLoop for creating this awesome theme.

32 minutes later... More rebooting and another 16 minutes of patching.

Installing Visual Studio 2012 which requires almost no explanation (just lots of moving dots while it installs). Swell.

If your using a TFS open visual studio 2010, copy your settings and port them to 2012. Your local folder mapping should remain the same.

Start upgrading your project properties and select 4.5 as your framework target. In your web.config go to the compilation element targetFramework=”4.0″ and replace it with 4.5.
I also added httpRuntime targetFramework="4.5" right under system.web and before compilation.


Now for the entity framework. More than likely once you did this a nice warning popped up:

Warning: Old school doesn't like the new school kids.

Lets add the new 5.x .tt to generate our new functionality so once we are done we can get rid of the old one. You backed up your TFS before this right?

Rename your current 4.x with an "Old"  suffix in case things don't work out.

Update the $edmxInputFile$ variable with the name of your edmx model.

In my case I used Z10.edmx

Next perform the same steps on XXX.Context.tt. Below our two sample screenshots I saw in my application.

Sweet! I have all of my new DbSet<T> objects. 

Due to my design I had implemented my custom logic via partial classes in a separate file (called ModelExtensions.cs). With this methodology the template engine can regenerate as much as it sees fit without killing my extensions and thus I can remove the Old Template with out feeling guilty (you did make a backup right?). Scalpel.

This is the critical part. In my striving effort to make this upgrade I want to start making sure that I will implement an actual repository. Before I hadn't bothered setting one up since it was a small application I had made for my personal use. Now it is time to get my refactor on.