CODEWITHSUNDEEP
c#asp.net-mvclinq-to-sql
Scott Selby
Scott Selby

Reputation: 9580

linq to sql query with 2 dbml files

I have a object of MyFriendFollowStatus , for each friend I need information from 2 different databases, so I wrote something like this

db1Context FFdb = new db1Context();
db2Context EEdb = new db2Context();

foreach (fbFriendsFollowStatus a in fbids)
{
    long ffID = FFdb.FFUsers.Where(x => x.FacebookID == a.fbid).FirstOrDefault().FFUserID;
    a.ffID = ffID;
    int status = EEdb.StatusTable(x => x.ffID == ffid).FirstOrDefault().Status;
    a.Status = status;
}

this works, but it doesnt really seem right calling 2 databases - once each for each user , is there something built in LinqToSql that helps with something like this? or sometype of join I can use using 2 different databases?

Upvotes: 0

Views: 470

Answers (1)

Jarek
Jarek

Reputation: 3379

Well, you can always limit your N+1 query problem to 3 queries - one to get users, one to get user's data form first database and one for the second database. Then connect all the results in memory - this will limit the connections to databases which should improve performance of your application.

I don't know if linq-to-sql or entity framework offers building model from different databases - this would pose some performance problems probably - like in includes or something, but I may simply not be aware of such features.

Sample code to do what you're trying to achieve would look something like that:

var facebookIds = fbFriendsFollowStatus.Select(a => a.fbid);
var FFUserIds= FFdb.FFUsers.Where(x => facebookIds.Contains(x.FacebookID)).Select(x => new { x.FacebookID, x.FFUserId)
var statuses = EEdb.StatusTable.Where(x => FFUserIds.Contains(x.ffID)).Select(x => new { x.ffID, x.Status})

And then some simple code to match results in memory - but that will be simple. Please note that this code is sample - if I've mismatched some ids or something, but idea should be clear.

Upvotes: 2

Related Questions