Source Code

Entity Framework 5 Code First Relationships

Preface

When I started this series about Entity Framework, I needed a good metaphor for the database relationships. I decided on a library, but soon realized my examples were becoming contrived. I was “fitting facts to suit theories.”* And yet I liked the library story.

Imagination floated in--as she often does--and in that kind yet firm tone said, “Why constrain yourself to reality? Why not invent?”

I listened, and after assembling all the material, wrote the light story of of a boy, Edward Farley, and his desire to learn. Following Netflix’s example with House of Cards, I’m releasing all the parts at once.

What follows is an opinionated (in the positive sense) set of examples for creating EF 5 Code First Relationships. It’s for me, the way I like to do them, and if it helps you, wonderful.

There are some oblique database references, and more than a few links to people and things that interest me. I’ve tried hard to be error-free, but that is a path of folly.

Finally, many thanks to my wife, Stephanie, for proof-reading these posts.

*Sherlock Holmes: 'It is a capital mistake to theorize before one has data. Insensibly one begins to twist facts to suit theories, instead of theories to suit facts.'

Netflix House of Cards
Folly
Stephanie Kaye Turner

This used to be done with sp_change_users_login, but that procedure is being removed in favor of alter user.

--http://ask.sqlservercentral.com/questions/3173/identifying-orphaned-users.html
--http://stackoverflow.com/a/2874493
--Modified by me to use correct search for orphans
DECLARE @Username VARCHAR(100),
        @cmd      VARCHAR(100)

DECLARE userlogin_cursor CURSOR FAST_FORWARD FOR
SELECT UserName = dp.name
--, UserSID = dp.sid
FROM sys.database_principals dp
WHERE dp.type = 'S'
    AND (dp.sid is not null AND dp.sid <> 0x0)
    AND (LEN(dp.sid) <= 16)
    AND SUSER_SNAME(dp.sid) is null
ORDER BY dp.name
FOR READ ONLY

OPEN userlogin_cursor
FETCH NEXT FROM userlogin_cursor INTO @Username

WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @cmd = 'ALTER USER [' + @username + '] WITH LOGIN = [' + @username + ']'
      EXECUTE(@cmd)
      FETCH NEXT FROM userlogin_cursor INTO @Username
  END

CLOSE userlogin_cursor
DEALLOCATE userlogin_cursor 

I'm preparing a series on Entity Framework, and discovered something yesterday I never knew. In MS SQL Server, you can't successfully model a true one-to-one relationship, where both sides are required. I say "successfully," because you can configure the tables, but they won't work. This is because SQL won't allow deferred constraint resolution, which is fine by me.

The insert statements, below, will fail. Note you have to remove one of the foreign key constraints before you can drop the tables!

create table One (
 OneId int not null identity primary key,
 Name varchar(10) null
)

create table Two (
 OneId int not null primary key references One(OneId),
 Name varchar(10)
)

alter table One add constraint fk1 foreign key (OneId) references Two(OneId)

insert One(Name) values('asdf')
insert Two(Name) values('qwer')

alter table One drop constraint fk1
drop table Two
drop table One