473,324 Members | 2,370 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

LINQ to SQL query, getting a bit lost.

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!
Aug 4 '08 #1
2 1718
Nightcrawler wrote:
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.

--
J.
Aug 4 '08 #2
This works great!

Thank you!
Aug 11 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: =?Utf-8?B?VmFuZXNzYQ==?= | last post by:
I want to develop a datagridview allowing column selection and filtering, like this: http://www.codeproject.com/KB/grid/GridFilter.aspx This example is using dataset. My question is if: a) I...
9
by: =?Utf-8?B?RXZlcnQ=?= | last post by:
In my (Windows Forms) project I am using strongly typed datatables. Now I am trying to convert my 'rowfilter/group by' query logic to Linq. Most queries I can easily convert, but some I am having...
5
by: =?Utf-8?B?cmF1bGF2aQ==?= | last post by:
linq on objects... want to find if the object exist in its collection if I have a loop searching in the collection foreach (myClass r in collections) { if (r.field01 == type && r.field02...
0
by: =?Utf-8?B?SHlwZXJjb2Rlcg==?= | last post by:
I'm encountering some strange behavior after deploying a ASP.net 3.5 website to production, i'm unable to reproduce these in my dev environment. This error seems to occur very randomly but it's...
14
by: thj | last post by:
Hi, I was wondering what you guys are using and why? LINQ to SQL or NHibernate? Thanks in advance, Tommy
13
by: Dan Tallent | last post by:
I have a query which I would like to convert to LINQ. I am having problems finding good examples or references that cover more complex queries. Here is the SQL command I would like to rewrite...
20
by: raylopez99 | last post by:
Inspired by Chapter 8 of Albahari's excellent C#3.0 in a Nutshell (this book is amazing, you must get it if you have to buy but one C# book) as well as Appendix A of Jon Skeet's book, I am going...
21
by: hrishy | last post by:
Hi Will LINQ be ported to Python ? regards Hrishy
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.