SpurData Blog

This post is really about the cons of Entity Framework. I know, I know, it's sacrilege to not be in love with EF and using it everywhere, and I feel I am taking some professional risk expressing these thoughts. But I actually find Entity Framework to be problematic in many ways, both technologically and philosophically, and I am frustrated that it is now the "standard" way to do data access in Microsoft land. To be the standard implies "generally suitable for most applications", which in my experience, Entity Framework is not.

Here's a script to generate alter column statements for all the audit fields in your database. In this case we are wanting to convert all the integer columns to varchar. select 'alter table ' + TABLE_NAME + ' alter column ' + COLUMN_NAME + ' varchar(50) null;' from INFORMATION_SCHEMA . COLUMNS where COLUMN_NAME in ( 'InsertedBy' , 'UpdatedBy' ) and DATA_TYPE = 'int' Quick review of Alter Table Alter Column available at SQL Server Planet: http://sqlserverplanet.com/ddl/alter-table-alter-column

I learned something about T-SQL variable scoping that I did not previously recognize. Within a loop, you can declare a variable. When you run the loop, things function fine - you don't get any "hey, you already declared that variable" errors. In my mind that means the variable must be locally scoped to the loop, and perhaps would get reset on each pass through the loop then.  But that's not quite the case. Try this and look at the output. It's only instantiating the variable on the first pass. Works the same on table variables, which is what I was actually curious about...

/* borrowed 20150717 by wills - from pszanto at http://stackoverflow.com/a/13249209/377058 - made @Debug a parameter */ ALTER PROCEDURE [ dbo ].[ usp_Admin_TruncateNonEmptyTable ] @ TableToTruncate varchar ( 64 ), @ Debug bit = 1 AS BEGIN SET NOCOUNT ON -- GLOBAL VARIABLES DECLARE @ i int --DECLARE @Debug bit DECLARE @ Recycle bit DECLARE @ Verbose bit DECLARE @ TableName varchar ( 80 ) DECLARE @ ColumnName varchar ( 80 ) DECLARE @ ReferencedTableName varchar ( 80 ) DECLARE @ ReferencedColumnName varchar ( 80 ) DECLARE @ ConstraintName varchar ( 250 ) DECLARE @ CreateStatement varchar ( max ) DECLARE @ DropStatement varchar ( max ) DECLARE @ TruncateStatement varchar ( max ) DECLARE @ CreateStatementTemp varchar ( max ) DECLARE @ DropStatementTemp varchar ( max ) DECLARE @ TruncateStatementTemp varchar ( max ) DECLARE @ Statement varchar ( max ) -- 1 = Will not execute statement...

I'll try to expand on this, but anyway, here's some examples about how you have to be careful with IN and NOT IN in T-SQL in terms of when results will be returned. If the list you're comparing to has nulls, watch out. If the item you're comparing has nulls, watch out. If the list could be empty, check for that. IN and NOT IN may behave differently than you expect in all these situations. Below are most of the iterations. --in select 'row' where 1 in ( 1 , 2 , 3 ) --returns select 'row' where 1 in ( select 1 union select 2 ) --returns select 'row' where 1 in ( select null union select 2 ) --does NOT return - cannot determine if 1 "in" null, maybe it is, who knows? select 'row' where 1 in ( select top 0 f1 from ( select 1 f1 union select 2 ) qry ) --NO return b/c the top 0 qry returns "nothing", and 1 is NOT "in" nothing ("in nothing" = false) --so...

Reminder to self: When you are exploring a new concept, or getting familiar with a new technology that you intend to implement,  write your own documentation about it. By this I mean going beyond "keeping notes", where you say "Tech X allows you to do a,b,c". Instead expand it with how YOU would use Tech X, or how you ARE using it. "We use Tech X at Widgets Unlimited to overcome issues 1,2,3. Standards: We always enable Option A because Option B proved unreliable across a WAN" etc. This doesn't have to be glamorous. It just has to be personal. Why, and why bother? The act of summarizing everything you've read/seen on a topic helps you synthesize and internalize it. Fuzzy parts become clear. You are forced to really think through the topic, and make it real for your life. You've just created a great documentation resource that will be personally twice as valuable as anything you'll find online or in a book.

Quick note if you've recently upgraded to Visual Studio 2012 or beyond, and you're trying to create new projects on TFS 2010: You must still use the Visual Studio 2010 Team Explorer for that specific bit. If you looked at the compatibility page at http://msdn.microsoft.com/en-us/library/dd997788.aspx ("Compatibility between Team Foundation clients and Team Foundation Server"), you might be tempted to skip the text at the top and just scroll down to the "TFS 2010" section that applies to you. But if you do that, you'll miss this important point within the general guidance: "To create a team project or manage process templates on an on-premises TFS, you must connect using the same version level of Visual Studio or Team Explorer. That is, to create a team project on TFS 2013, you must connect from Team Explorer 2013." When you use VS 2012 against TFS 2010, and try to create a project, you may get the following error: "TF30170: The plugin Mic...