I need help with Query please

Question

Hi all,
I have 3 data tables 2 connection tables, and I can't find a way to do the following query.

Table1 : Items (ID, Value)
Table2 : Owner(ID, Value)
Table3 : Project(ID, Value)
Table4 : Item2Owner(itemID, ownerID)
Table5 : Owner2Project(ownerID, projectID)

I'm trying to get items by project id.
The problem is that I can't access tables located at "level 2" using the LINQ to SQL.

I tried to do the following query:
var x = (from c in context.Project.Include("Owner.Items")
   where c.ID == 'someID'
   select c.Owner.Items).ToList();

I also tried this query:
var x = (from c in context.Owner.Include("Items").Include("Project")
   where c.Project.ID == 'someID'
   select c.Items).ToList();

But both of them are not illegal.
Any ideas?

Thanks,
Ravid

---

Ravid Arbel

Answer 1

Are the relationships defined in the data context (dbml)?

If so you can just query like this for example:

I have an orders table and a items table. Orders has a one to many relationship with items.

Orders(Id, blah, blah blah)

Items(Id, OrderId, blah, blah, blah)

If the relationship is defined I can query like so:

using (context db = new context())
{
   var q = (from i in db.Items
                where i.Orders.Id == @someOrderId
                select i);
}

Hope this helps I will try now and see if I can relate to your specific question!

Answer 2

I don't know how the schema is setup but shouldn't the project have a one to many relationship with items.

Maybe a scheme change like this would suffice:

Owner(Id, blah)
Project(Id, ownerId, blah)
Items(Id, projectId, value, blah)

So the owner would have a one to many relationship with project and project will have a one to many relationship with items.

Drag and drop the tables to the dbml designer (barring that you have defined the relationships in sql server) the relationships should automatically be shown.

And its just a matter of what I stated earlier:

using (context db = new context())
{
   var q = (from i in items
                where i.Project.Id == @someProjectId
                select i);
}

Answer 3

Hi d13mr3m1x,

Thanks for your response. I think that my explanation was not specific enough. I'll try once again.
I have 3 entities in my DB:  Keywords, Search engines and Users.
And 2 relations:
  a. Keyword to SE relation. (kwd_id, se_id)
  b. Relation from table a (Kwd2SE) to users.

I'm trying to build a query that returns list of keywords for user id.
First I need to get the Kwd2SEs that is related to a given user_id, and then retrieve the list of keywords from that.

Hope I made my point this time. :)
Thanks,
Ravid

---

Ravid Arbel