This is a migrated thread and some comments may be shown as answers.

Error during Join

7 Answers 76 Views
LINQ (LINQ specific questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Assad
Top achievements
Rank 1
Assad asked on 15 Jan 2010, 08:40 PM
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)

7 Answers, 1 is accepted

Sort by
0
Damyan Bogoev
Telerik team
answered on 20 Jan 2010, 07:01 PM
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.
0
jon
Top achievements
Rank 1
answered on 05 Feb 2010, 07:21 PM

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.

0
Damyan Bogoev
Telerik team
answered on 08 Feb 2010, 07:52 PM
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.
0
jon
Top achievements
Rank 1
answered on 09 Feb 2010, 07:12 PM

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

0
Zoran
Telerik team
answered on 12 Feb 2010, 05:12 PM
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.
0
jon
Top achievements
Rank 1
answered on 12 Feb 2010, 05:30 PM
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.
0
Zoran
Telerik team
answered on 18 Feb 2010, 10:07 AM
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.
Tags
LINQ (LINQ specific questions)
Asked by
Assad
Top achievements
Rank 1
Answers by
Damyan Bogoev
Telerik team
jon
Top achievements
Rank 1
Zoran
Telerik team
Share this question
or