From many to many requests from the OData feed

Question

I am trying to use odata feed from windows phone 7 silverlight client. Basically this is the attitude of many to many relationships between groups and users with the UserGroup table between them. When a user logs in, I need to query the groups to which she belongs 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 tried everything I know to get this result, but I keep getting one error after another no matter what I do. If you do not have two queries that I do not want to do, because it will be too dirty, is there any solution?

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

From a long night of research, I found that odata has a limit on the lack of queries such as "Any" and "All", so my query would not be possible at this time. What I also found from here is that my implementation of poco classes can be slightly modified to represent many-to-many relationships without explicitly defining a class in the middle, which will potentially help me solve this navigation problem. I am still working on this because I am having problems defining the navigation properties, but as soon as I have a solution, I will put it here to help other unhappy travelers who are going along this path.

Answer 1

So, here is how I was eventually implemented for my odata service. First, I had to redo the data, excluding the GroupUser normalization table and defining the appropriate assembly for 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 infrastructure some insight into how I want my model to be defined, thereby overriding the usual implementation. The easiest way, in my opinion, to understand the above statement is to read it as an expression as follows:

A user object can have many (has many) MemberOfGroups, each of which can exist "with many" GroupMembers members (hence many of many), the foreignkey field for the left table (Group) should be called GroupId and for the right table (User) should be called UserId, and they must be mapped to the "GroupUsers" table.

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

With this, you can easily request your odata service, as if both collections were from one to many relationships of the corresponding parent. Hope this helps someone.

Answer 2

Any / all support has been added to the OData protocol recently. Here are two blog posts with some info on Any / All in OData:

Support for everyone and everyone

More than anyone and everyone

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

Answer 3

A workaround for the any / all problem is to use the website to perform any / all server-side filtering functions. With a return value set as requested, it will allow you to use standard date conventions along with your custom filtering features in the request.

i.e. calling the request after you create your website will work as follows.

 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. }