LINQ to SQL for tables across databases. Or View?

Question

I have a Message table and a User table. Both are in separate databases. There is a userID in the Message table that is used to join to the User table to find things like userName.

How can I create this in LINQ to SQL? I can't seem to do a cross database join.

Should I create a View in the database and use that instead? Will that work? What will happen to CRUD against it? E.g. if I delete a message - surely it won't delete the user? I'd imagine it would throw an error.

What to do? I can't move the tables into the same database!

Answer 1

A view will work, if you have granted access to both database to the configured user. You'll need to use the 2-dot notation. This will only work BTW if both databases are on the same server.

create view vwUserMessages as 
select * from db1.dbo.Users as users 
inner join db2.dbo.Messages as msg on msg.UserID = users.id

For CRUD: a view is (usualy) only for reading: do updates etc directly to the related tables, or use a stored procedure:

create proc pdeleteUserMessages (@UserID int) as

begin trans

delete db2.dbo.Messages where userid = @UserID
delete db1.dbo.Users where id = @UserID

commit trans

go