Error during Join

8 posts, 0 answers
  1. Assad
    Assad avatar
    5 posts
    Member since:
    Mar 2009

    Posted 15 Jan 2010 Link to this post

    I have this method in which I am trying to join two objects.

    public Array GetFacility(IObjectScope scope, int ProviderId)  
            { 
                scope.TransactionProperties.AutomaticBegin = true;  
     
                var results = from fac in scope.Extent<Facility>()  
                              join pr in scope.Extent<ProviderFacility>()   
                              on fac.Id equals pr.Facility.Id  
                              where fac.IsDeleted == false && pr.IsDeleted == false && pr.Provider.Id == ProviderId  
                              select new { Name = String.Concat(fac.ProviderNumber, " ", fac.Name), Id = fac.Id };
                              
                  
                return  results.ToArray();  
            } 

    I am always getting the following error at "return  results.ToArray();  "
    "Unable to cast object of type 'OpenAccessRuntime.DataObjects.query.FieldNavNode' to type 'OpenAccessRuntime.DataObjects.query.FieldNode'."

    Any suggestions?
    (P.S: I am using version 2009.3.1218.1 of OpenAccess)
  2. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 20 Jan 2010 Link to this post

    Hello Assad,

    In order to avoid the error you could use the shared columns mechanism.  A column with a foreign key constraint in the database can be mapped to a simple type field like an integer as well as to a reference type field.
    Additional information about the shared columns and how to generate the ID fields for foreign keys can be found here and here.
    Now you should change the Linq query in the following way:
    var results = from fac in scope.Extent<Facility>()  
          join pr in scope.Extent<ProviderFacility>()   
          on fac.Id equals pr.FacilityId  
          where fac.IsDeleted == false && pr.IsDeleted == false && pr.Provider.Id == ProviderId  
          select new { Name = String.Concat(fac.ProviderNumber, " ", fac.Name), Id = fac.Id };

    I think that will help you.

    Kind regards,
    Damyan Bogoev
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  3. DevCraft banner
  4. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 05 Feb 2010 Link to this post

    I want to do something like this basic join of 2 tables that have a foreign key defined, however I only want a Right Join.

    I searched on how to do Right Joins but found nothing.  Is it possible to do something like this:

    var results = from fac in scope.Extent<Facility>()
    right join pr in scope.Extent<ProviderFacility>()  
    on fac.Id equals pr.FacilityId  
    select new { Name = String.Concat(fac.ProviderNumber, " ", fac.Name), Id = fac.Id };

    Because I want info from the ProviderFacility table, but I only want to display 1 record per Facility.  Thanks.

  5. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 08 Feb 2010 Link to this post

    Hi jon,

    You could achieve this by using a group join clause. You will have to switch the order of the tables in order to achieve the right join logically (only left joins are supported at the moment):

    var query = from pr in scope.Extent<ProviderFacility>()
            join fac in scope.Extent<Facility>() on pr.Id equals fac.Id into g
            from p in g.DefaultIfEmpty()
    select new { Name = String.Concat(fac.ProviderNumber, " ", fac.Name), Id = fac.Id };

    Hope that helps.

    Best wishes,
    Damyan Bogoev
    the Telerik team

    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
    Follow the status of features or bugs in PITS and vote for them to affect their priority.
  6. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 09 Feb 2010 Link to this post

    The group join to mimic the Right Join does not appear to be working.  Let's say I have 2 records in my animal table with a IdCage=101, and in my Cages table I have a single cage with an IdCage=101.  I only want the 1 cage row to be returned for my result set, but I would like to grab the value of one of the animals properties.  

    This is not working because it returns 2 rows in the result set:

    var result = from a in scope.Extent<Animal>()
    join c in scope.Extent<Cage>() on a.IdCage1 equals c.IdCage into ca
    from cAnimal in ca.DefaultIfEmpty()

  7. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 12 Feb 2010 Link to this post

    Hello jon,

    This is related with the fact that group joins are not fully supported in the current version of OpenAccess. You could try placing the Cage extent to be your outer extent and then join with the Animal extent as a workaround. You could also try achieving your goal without using joins. That means that you could have a collection on the Cage class of type Animal(all the animals that are part of a cage). Then you could ask for the DefaultIfEmpty() item on that collection for each Cage object.

    Sincerely yours,
    Zoran
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
  8. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 12 Feb 2010 Link to this post

    I wish I could avoid using joins, but unfortunately, a requirement of the project is that I support a many-to-many relationship between the animals and cages - so it starts to get tricky.  Do you know how this could be done without using joins when there is a many-to-many relationship?  That would be a big help for several of the queries I'm struggling with.
  9. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 18 Feb 2010 Link to this post

    Hello jon,

    Well you could have access to all animals related to a cage object as well as the opposite(access all cages related to an animal) if you perform an m:n mapping using the Reverse Mapping Wizard. This way a cage object would have an Animals property which would be a list of all associated animals. All animal objects on the other side, would have a collection of cage objects as well. Than you could make queries where you select only the cages for example and you would that way get all the animals required as well from the Animals collection. The join will be performed by OpenAccess in the SQL server in order to obtain the associated collection.

    Greetings,
    Zoran
    the Telerik team

    Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.
Back to Top
DevCraft banner