none
I need help with Query please RRS feed

  • 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
    Monday, November 2, 2009 6:00 PM

Answers

  • 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);
    }
    • Marked as answer by Zhipeng Lee Monday, November 9, 2009 1:37 AM
    Monday, November 2, 2009 7:05 PM

All replies

  • 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!
    • Edited by d13mr3m1x Monday, November 2, 2009 6:59 PM
    Monday, November 2, 2009 6:47 PM
  • 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);
    }
    • Marked as answer by Zhipeng Lee Monday, November 9, 2009 1:37 AM
    Monday, November 2, 2009 7:05 PM
  • 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
    Monday, November 2, 2009 8:16 PM