All geek questions in one place

LINQ to SQL for tables between databases. Or View?

I have a message table and a user table. Both are in separate databases. The message table has a user ID that is used to join the User table to find things like userName.

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

Should I create a view in the database and use it instead? Will this work? What will happen to CRUD against this? For instance. if I delete the message - will it really not delete the user? I would suggest that this will cause an error.

What to do? I can not move tables to one database!

+3
source share
1 answer

The view will work if you provide access to each database to a configured user. You will need to use point-to-point 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: view (usually) read-only: do updates, etc. directly in linked 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
+3
source

Source: https://habr.com/ru/post/1742249/

More articles:


All Articles