How to query associations in Linq to Entity framework in .NET Ria Services

Question

I have just started with Linq and Linq to Entity Framewok. On top of that with the .NET Ria services. My problem is that I have 2 tables Folder and Item with a many to many relationsship using a third "connection" table FolderItem like this:
_{(source: InsomniacGeek.com)}

In the .NET RIA Service domain service, I want to create a method that returns all Items for a given FolderID.

In T-SQL , that would something like this:

SELECT * FROM Item i
INNER JOIN FolderItem fi ON fi.ItemID = i.ID
WHERE fi.FolderID = 123

My Linq knowledge is limited, but I want to do something like this:

public IQueryable<Item> GetItems(int folderID)
{
  return this.Context.Items.Where(it => it.FolderItem.ID == folderID);
}

This is not the correct syntax, it gives this error:

Cannot convert lambda expression to type 'string' because it is not a delegate type

What is the correct way of doing this (with associations) ?

Can I user the .Include("FolderItem") somehow?

Please, method syntax only.

PS. Here's how it would look like using a Query Expression:

  public IQueryable<Item> GetItemsByFolderID(int folderID)
  {
    return from it in this.Context.Items
           from fi in it.FolderItem
           where fi.Folder.ID == folderID
           select it;
  }

The qeustion is, how would it look like using the Method Based Query Syntax?

What is wrong with your suggested GetItems (except that the "=" should be "==")? Have you tried it? — Craig Stuntz, Sep 03 '09 at 13:15
I presume the s. in the Query Expression is also a typo and should be c.? At any rate, I still see nothing terribly wrong with GetItems as you wrote it, but I'll give you a different option below. — Craig Stuntz, Sep 03 '09 at 18:46

score 4 · Accepted Answer · edited Sep 04 '09 at 08:17

4

Your GetItems looks fine to me. You could also do:

public IQueryable<Item> GetItems(int folderID)
{
    return this.Context.FolderItems
                       .Where(fi => fi.ID == folderID)
                       .Select(fi => fi.Items);
}

Both should return the same thing.

edited Sep 04 '09 at 08:17

Magnus Johansson

28,010
19
106
164

answered Sep 03 '09 at 18:50

Craig Stuntz

125,891
12
252
273

Great. Just what I wanted. Thanks. This way of attacking the data from its parent entity, I didn't think of. – Magnus Johansson Sep 04 '09 at 08:18

score 2 · Answer 2 · answered Dec 07 '09 at 00:23

You can have the parent entity contain the child entities. There are 2 things you have to do to do this:

1) Update your domain query to include the folder items and items:

return from x in Context.FolderItems
                        .Include("FolderItem")
                        .Include("FolderItem.Item")
       where x.ID == folderID
       select x

2) Update the metadata file so that the RIA service knows to return the associations to the client:

[MetadataTypeAttribute(typeof(FolderMetadata))]
public partial class Folder
{
     internal sealed class FolderMetadata
     {
           ...
           [Include]
           public FolderItem FolderItem; 
     }
}

How to query associations in Linq to Entity framework in .NET Ria Services

2 Answers2