locked
Many to Many with Payload RRS feed

  • Question


  • I'm developing an application to track customization to another application.  This other application has about 10,000 code objects I want to track (dll's, crystal reports and SQL objects), and probably 50+ versions (every service pack\hot fix is considered a version).

    In my database I have a simple many to many relationship with a payload:
    CodeObject table - each row is a seperate dll, report or sql object
    Version table - each row is a seperate version
    CodeObjectVersion table - links the other 2 tables together in a many to many relationship and contains information about what changed in that version

    I'm using ADO.Net Data Service and Entity Framework to access the data.

    In my code I need to look up a specific Version, then pull all of the CodeObjects for that Version.  Later I need to search through the list of CodeObjects for a specific CodeObject.

    Is there a way to get from a Version object to a list of CodeObjects?  Or do I just need to create a seperate SQL View and generate an entity out of that?

    I know I can get what I want with straight Linq to Entities using the following:

    var CodeObjs = from TryStuff.CodeObject c in db.CodeObject 
                   where c.CodeObjectVersion.Any(cov => cov.Version.VersionID == ver.VersionID) 
                   select c; 

    But when I try the same through ADO.Net Data Services I get a message that "the 'any' method is not supported.

    Sunday, February 15, 2009 9:35 PM

Answers

  • There isn't a good way to do this. But you can get all the CodeObjects entity associated with a version entity, without loading the full CodeObjectVersion collection from database, since astoria supports filtering on singleton/reference navigation properties.

    var codeObjectVersions = from TryStuff.CodeObjectVersion ov in db.CodeObjectVersions
                                             where ov.Version.VersionID == ver.VersionID
                                             // you can add the filter clause here for CodeObject too if you want
                                             select ov;

    This gives you all the CodeObjectVersions associated with a given version, filtered by whatever criteria you have for CodeObject. Now you can enumerate the CodeObjectVersions collection and take CodeObject from there and do whatever you want.

    This is not great, but hopefully should work for you.

    Thanks
    Pratik


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 24, 2009 8:57 PM
    Moderator

All replies

  • Hi Jeremy,

    If I udnderstand this correctly , your schema looks like this :

    CodeObject table - each row is a seperate dll, report or sql object
    Version table - each row is a seperate version
    CodeObjectVersion table - links the other 2 tables together in a many to many relationship and contains information about what changed in that version

    When the EF sees the mapping table , it converts the relation between the entities into a Many-to-Many as you are seeing right now .
    Given a Version , you can get all the code objects related to that version object using the LoadPropery call
    ex :
    contextInstance.LoadProperty(versionObjectInstance , "<CodeObjectCollectionName>");

    In the same way , Given a Codeobject  , you can get all the version related to that version object using the LoadPropery call
    ex :
    contextInstance.LoadProperty(codeObjectInstance , "<versionObjectCollectionName>");

    Phani Raj Astoria
    Wednesday, February 18, 2009 10:06 PM
    Moderator
  • Close.  I think that's how the EF generates the model if the intermediary table does not have any data in it.  

    When the EF sees those tables it creates an entity for each of the three tables.  I have a Version entitiy, CodeOBjectVersion entity and a CodeObject entity.  Both the Version entity and the CodeObject entity have the property CodeObjectVersions which is are collections of CodeObjectVersion entities.

    The diagram looks something like this:

    Version 1--* CodeObjectVersion *--1 CodeObject

    So, I have the version object and I need a collection of CodeObjects I can iterate through, and when I get that collection, I need to be able to filter it based on a property on the CodeObject entity (preferably before downloading the full CodeObjectVersion collection from the database).




    Thursday, February 19, 2009 5:25 PM
  • I've hit the same problem.  Did you find an answer?
    Monday, March 23, 2009 9:14 AM
  • There isn't a good way to do this. But you can get all the CodeObjects entity associated with a version entity, without loading the full CodeObjectVersion collection from database, since astoria supports filtering on singleton/reference navigation properties.

    var codeObjectVersions = from TryStuff.CodeObjectVersion ov in db.CodeObjectVersions
                                             where ov.Version.VersionID == ver.VersionID
                                             // you can add the filter clause here for CodeObject too if you want
                                             select ov;

    This gives you all the CodeObjectVersions associated with a given version, filtered by whatever criteria you have for CodeObject. Now you can enumerate the CodeObjectVersions collection and take CodeObject from there and do whatever you want.

    This is not great, but hopefully should work for you.

    Thanks
    Pratik


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, March 24, 2009 8:57 PM
    Moderator