Many to many query from OData feed

Question

I'm trying to consume an odata feed from a windows phone 7 silverlight client. Basically is a Many to many data relationship between Groups and Users with a UserGroup table between them. When a User logs in, I need to query for the groups she belongs to using her UserId. My Data classes are as follows

[DataServiceKey("Id")]
public class Group
{
    public Guid Id { get; set; }
    public string GroupTag { get; set; }
    public DateTime DateCreated { get; set; }

    [ForeignKey("GroupOwner")]
    public Guid? GroupOwnerId { get; set; }
    public virtual Person GroupOwner { get; set; }

    public bool IsActive { get; set; }

    public virtual ICollection<GroupUser> GroupUsers { get; set; }
}

[DataServiceKey("Id")]
public class GroupUser
{
    public Guid Id { get; set; }

    [ForeignKey("Group")]
    public Guid GroupId { get; set; }
    public virtual Group Group { get; set; }

    [ForeignKey("Person")]
    public Guid PersonId { get; set; }
    public virtual Person Person { get; set; }
    public bool IsActive { get; set; }
}
 [DataServiceKey("Id")]
public class User
{
    public Guid Id { get; set; }
    public string Username { get; set; }
    public string  Password { get; set; }
    public virtual ICollection<GroupUser> UserGroups { get; set; }
    public virtual ICollection<Group> MyGroups { get; set; }
}

I have tried everything I know to get this result but I keep getting one error after the other no matter what I do. Short of having 2 queries which I don't want to do because it would be too messy, is there any solution?

------Update------

From the long night of research, I have discovered that odata has the limitation of not supporting 'Any' and 'All' type queries, so my query would not be possible at this time. What I also discovered from here that my implementation of the poco classes could be slightly modified to represent a many to many relationship without explicitly defining the class in the middle which would potentially help me solve this navigation problem. I am still in the process of working it out as I am having issues defining the navigation properties but as soon as I have a solution, I'll put it up here so it can help some other hapless traveler who comes down this path.

Answer 1

So here is how I eventually implemented for my odata service. First I had to remodel the data eliminating the normalization table GroupUser and defining the related collection on the objects themselves:

[DataServiceKey("Id")]
public class Group
{
    public Guid Id { get; set; }
    public string GroupTag { get; set; }
    public DateTime DateCreated { get; set; }

    [ForeignKey("GroupOwner")]
    public Guid? GroupOwnerId { get; set; }
    public virtual Person GroupOwner { get; set; }

    public bool IsActive { get; set; }

    //his represents the many-to-many relationship
    public virtual ICollection<User> GroupMembers { get; set; }
}


[DataServiceKey("Id")]
public class User
{
  public Guid Id { get; set; }
  public string Username { get; set; }
  public string  Password { get; set; }

  //This represents the many-to-many relationship
  public virtual ICollection<Group> MembersOfGroups { get; set; }

  //This represents the 'GroupOwner' ForeignKey relationship
  public virtual ICollection<Group> MyGroups { get; set; }
}

In the datacontext class, I then override the OnModelCreating method as follows

  protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>().HasMany(p => p.MemberOfGroups).
            WithMany(c => c.GroupMembers)
            .Map(t=>t.MapLeftKey("GroupId")
                .MapRightKey("UserId")
                .ToTable("GroupUsers"));

    }

This basically gives the entity framework some insight into how I want my model defined, thus overriding the conventional implementation. The easiest way in my opinion to understand the above statement is to read it as a statement as follows:

The User entity can have many (has many) MemberOfGroups, each of which can exist 'with many' GroupMembers (hence the many to many relationship), the foreignkey field for the left table (Group) should be called GroupId and for the right table (User) should be called UserId and these should be Mapped to a table called "GroupUsers".

If you take a look at the data tables generated by entity framework, you will find a third table called GroupUsers with two columns : UserId and GroupId as combined primary key and each referencing a foreign key relationship with the corresponding entity table.

With this, you can easily query your odata service as if both collections were one to many relationships of the corresponding parent entity. I hope this helps someone.

Answer 2

Any/All support was added to the OData protocol recently. Here are two blog posts with some information on Any/All in OData:

Support for Any and All

Even More Any and All

The .NET implementation of OData, the WCF Data Services, also has Any/All support. Of course, in order to take advantage of this, the server that you are interacting with must be supporting Any/All.

Answer 3

A workaround for the any/all issue is to use a webget to perform the any/all filtering functionality on the server side. With the return value set as a queryable it will allow you to use the standard odata conventions alongside your custom filtering functionality in the query call.

i.e. the query call after you build your web get would work like this.

http://myServer:8000/MyService.svc/MyCustomFilteringWebGet?FilteringData=User/groups&$inlinecount=allpages

[WebGet]
public IQueryable<User> MyCustomFilteringWebGet(string FilteringData = null)
{
  return //Return any/all filtered data here. 
}