Error during Join

[Assad]

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)

[Damyan Bogoev]

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.

[jon]

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.

[Damyan Bogoev]

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.

[jon]

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()

[Zoran]

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.

[jon]

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.

[Zoran]

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.