LINQ to SQL query, getting a bit lost.

[Nightcrawler]

I have the following tables that I have dragged into a .dbml file
( have only included the keys for simplicity).

Users have a one to many relationship on Items keyed on UserId =
UserId.
Users have a one to many relationship on UserFriend keyed on UserId =
UserId and UserId = FriendId

CREATE TABLE [dbo].[Items](
[ItemId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL

CREATE TABLE [dbo].[Users](
[UserId] [uniqueidentifier] NOT NULL DEFAULT (newid())

CREATE TABLE [dbo].[UserFriend](
[UserId] [uniqueidentifier] NOT NULL,
[FriendId] [uniqueidentifier] NOT NULL

This query runs fine and returns all items belonging to a certain user

var q = from q in db.Items
where q.UserId == new
Guid(Request.QueryString["UserId"].ToString())
select q;

The point where I get lost is when I want to retrieve all Items
belonging to users who have a certain user as a friend (FriendId =
Request.QueryString["UserId"].ToString()). I want it to look like
something like this (the below is incorrect but give syou an idea of
what I am trying to do):

var q = from q in db.Items
where q.User.UserFriends.FriendId == new
Guid(Request.QueryString["UserId"].ToString())
select q;

Since the User to UserFriend is a one to many relationship the above
would not work and therefore q.User.UserFriends.FriendId does not
exists as an option. Instead the options are (Select<>, Join<>, etc,
etc.).

Can someone please point me in the right direction?

Thank you!

 

[Jeroen Mostert]

I have the following tables that I have dragged into a .dbml file
( have only included the keys for simplicity).

Users have a one to many relationship on Items keyed on UserId =
UserId.
Users have a one to many relationship on UserFriend keyed on UserId =
UserId and UserId = FriendId

CREATE TABLE [dbo].[Items](
[ItemId] [uniqueidentifier] NOT NULL,
[UserId] [uniqueidentifier] NOT NULL

CREATE TABLE [dbo].[Users](
[UserId] [uniqueidentifier] NOT NULL DEFAULT (newid())

CREATE TABLE [dbo].[UserFriend](
[UserId] [uniqueidentifier] NOT NULL,
[FriendId] [uniqueidentifier] NOT NULL

This query runs fine and returns all items belonging to a certain user

var q = from q in db.Items
where q.UserId == new
Guid(Request.QueryString["UserId"].ToString())
select q;

The point where I get lost is when I want to retrieve all Items
belonging to users who have a certain user as a friend (FriendId =
Request.QueryString["UserId"].ToString()).

Use joins:

var q =
from user in db.Users
join userFriend in db.UserFriends on user.UserId equals userFriend.UserId
join item in db.Items on user.UserId equals item.UserId
where userFriend.FriendId == new Guid(Request.QueryString["UserId"])
select item
;

There are other ways of doing it, of course, depending on what's intuitive
and the distribution of your data.

See also http://msdn.microsoft.com/vcsharp/aa336746.

 

[Nightcrawler]

This works great!

Thank you!