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

Results from Multiple Tables

32 Answers 544 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.
jon
Top achievements
Rank 1
jon asked on 19 May 2009, 09:13 PM
We are using Reverse Mapping and LINQ, and we are having trouble finding an examples of how to return results from multiple database tables. 

I have a "Strains" table and an "Animals" table, and I need data from both tables to be displayed in our ASPNet Gridview.  The tables are joined by a StrainID.  Please help when you can as we are on a timeline.  Thanks.

32 Answers, 1 is accepted

Sort by
0
Alexander
Telerik team
answered on 20 May 2009, 11:44 AM
Hello jon,

I assume that you have Strain and Animal classes generated. The relation between them will let you reach one object from the other related object. You will probably have a Strain property in the Animal class or something similar, so you can use AnimalObject.Strain to access the related Strain object. Having that in mind, you can retrieve the data from both objects by using a Linq query that returns anonymous class instances:
var query = from e in scope.Extent<Animal>() 
            select new { SomeProperty = e.SomeProperty, 
                               StrainProperty = e.Strain.SomeStrainProperty }; 
This way you can wrap all necessary data into one object and expose it in a GridView. Note that the data will be read-only. If you want to use update/delete operations you will not be able to use a single grid. In this case each class should have a separate one. Hope that helps.

Sincerely yours,
Alexander
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
0
jon
Top achievements
Rank 1
answered on 20 May 2009, 01:52 PM
We did define our foreign key as the Animal.IdStrain = Strain.IdStrain, but the Reverse Mapper is not adding this type of property to my persistant class for some reason... Ah, I was just able to get it to generate the "Strain strain" property in my Animal class - I just had to physically delete my Animal.cs file first because the generator was not overwritting it even though it said the generator was successful.

So now this is working:

 

result = 

 

from a in scope.Extent<Animal>() 
select new

{ a.IdAnimal, a.AnimalId, a.Sex, a.CoatColor, a.DatetimeBirth,

StrainId = a.Strain.StrainId  };

 

Some animals can be assigned to multiple cages, so now I need to grab data from the Location.  Is this possible to include this many-to-many Location data in my result set?  Thanks.

 

 

 

 

 

 

 

 

0
Ady
Telerik team
answered on 20 May 2009, 03:52 PM
Hello jon,

 This is possible if you have mapped the tables to classes appropriately, in the Reverse mapping wizard.
You must be having a join table that stores the information about the many-to-many collection between Animal and Location.
  • You need to select this table in the wizard and select the 'Collection' radio button.
  • You then can specify the owner and value type of the collection
  • Clicking the 'Many to many' radio button will create the collection field in both the classes.

 Hope this helps.
All the best,
Ady
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
0
jon
Top achievements
Rank 1
answered on 20 May 2009, 07:48 PM
Glad that it can be done, but I still don't quite see how to do that.  So I have the "Animal" table and the "Location" table which I need to create classes for.  Then are you saying that I need to create another table which will be output as a collection?

Is there a Support document or Example that steps through how to do this?
Thanks.
0
Ady
Telerik team
answered on 22 May 2009, 04:12 PM
Hi jon,

 If an 'Animal' can be assigned to multiple locations I assume you have a foreign key constraint from the Location class to the 'Animal' class.
 If you reverse engineer these tables the wizard will create the 'Animal' and Location classes and a reference field - 'animal', in the Location class. You can select this field and check the 'Create one-to-many' list checkbox to create an array of 'Location' instances in the Animal class.

Hope this helps

All the best,
Ady
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
0
jon
Top achievements
Rank 1
answered on 22 May 2009, 07:31 PM
Hmm, well as previously stated, this is a many-to-many relationship.  Multiple animals can be in a location, and a single animal can be in multiple locations.  How would we setup many-to-many relationships?  Thanks again.
0
Accepted
Ady
Telerik team
answered on 25 May 2009, 01:06 PM
Hi jon,

 I assume you have generated the 'Animal' and 'Location' classes using the Reverse mapping wizard.
To maintain an m:n relation between to types OpenAccess needs a join table. This join table stores the information contained in the collection.
The join table will consist of the PK columns of each of the 2 types.
If you do not already have such a table that has the values of the collection, you can let OA create this table for you as follows:
  1.  Add a collection field to both the classes -
    1. 'IList<Animal> animals' in Location class
    2. 'IList<Location> locations' in Animal class
  2. Select the fields mentioned in the above step, in the forward mapping dialog and select the 'm:n' radio button. This specifies that an M:N relation exists between these 2 types
  3. Now you need to create the join table. For this select any 1 of the type in the forward mapping and check the 'Create/Update Table' checkbox
  4. Select the project in the Solution Explorer and press F4. Set the 'UpdateDatabase' property to True
  5. Build the project and OA will create the Join table in the post build step

 NOTE: You should switch off the 'Create/Update table' and 'UpdateDatabase' option after the join table is created. If this is left on OA will try to update the database whenever you make changes to the class model (add,remove fields)
 
 Hope this helps.
Regards,
Ady
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
0
Andreas Kaech
Top achievements
Rank 1
answered on 23 Jun 2009, 05:10 PM
Hi Telerik,
I have a similar problem:

public ? GetAnimals()
{
    var 
query = from e in scope.Extent<Animal>()    
            select new { SomeProperty = e.SomeProperty,    
                               StrainProperty = e.Strain.SomeStrainProperty }; 
    return query;
}   
 

What is the return type?

Best regards,
Andreas
0
Ady
Telerik team
answered on 25 Jun 2009, 04:13 PM
Hi Andreas Kaech,

 The return type of this query would be an Anonymous type with 2 properties - SomeProperty and StrainProperty.
Note that you cannot have 'var' as a return type of a method and hence will need to process the query result to obtain the values from the Anonymous type.

Greetings,
Ady
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
0
Gerrit du Preez
Top achievements
Rank 1
answered on 16 Sep 2009, 08:31 PM

Hi there

I also have a problem with a many-to-many relationship, using OpenAccess and reverse mapping.

I did the reverse mapping and set up the tables as suggested in your documentation and this thread.

I have a table for Contracts, one for Groups and a table joining the two. 

In my query, I want to select the "Contract Title" from the Contracts table and then also the "Group Name" from the Groups table linked to the Contracts table with the reference of "groupRef" as set up in the many to many relationship. 

The query should look like this:

        var query = from c in scope.Extent<Contract>()
                          select new                          
                           { c.ContractTitle, 
                             c.groupRef.GroupName };
                              
However, it is not possible for me to add the final "GroupName" part of the code in the last line: c.groupRef.GroupName

It allows c.groupRef but "GroupName" is not in the list that opens up, after I type the "." after groupRef.  In fact, none of the fields of the Groups table are listed, it only gives a list begnning with "Add, Agregate<>, All<>, Any<> etc.)

Am I missing something?

Your advice will be much appreciated.

Kind regards

Gerrit

 

0
Gerrit du Preez
Top achievements
Rank 1
answered on 17 Sep 2009, 08:00 AM
 

Hi there

 

To add to my previous question:

 

I manage to get the 1:n relationships going, it is just the m:n one that would not allow me to find columns joined to the main table by means of a Linq query.

 

Let me explain what I am trying to do, maybe you can suggest a more elegant solution.

 

I am working on an Contract Management system. I have Users and Contracts and then also Groups.  Users can belong to a Group(s) and Contracts can belong to the same Group(s) as well.  When a User logs in, he or she should only be able to view only Contracts that belong to the same Group or Groups the logged in User belongs to.

 

My tables are:

 

Users (user table)

   UserID

   UserName

 

GroupUsers  (join table)

   UserID

   GroupID

 

 

Groups (groups table)

   GroupID

   GroupName

 

GroupContracts (join table)

   GroupID

   ContractID

 

Contracts (contracts table)

   ContractID

   ContractName

 

 

I want to accomplish the following:

 

A logged in User should be able to list only Contracts belonging to the same Groups as the User.

 

In SQL I would have written a query like (if the user's ID is e.g. 39):

 

Select Contract.ContractName FROM Contracts, Groups

WHERE Contracts.ContractID = GroupContracts.ContractID

AND GroupContract.GroupID IN

(Select GroupID FROM GroupUsers WHERE GroupUsers.UserID = 39 )

 

I am using OpenAccess (reverse mapping), how would I accomplish the same using Linq?

 

Thanks

 

Gerrit

0
Zoran
Telerik team
answered on 17 Sep 2009, 11:53 AM
Hello Gerrit du Preez,

As you said, this is a many-to-many relation. It means that one Contact may be part of multiple Groups. In the object representation, one Contact object has a relation with a Collection of Group objects. That is why you get the properties and methods of a persistent collection. If you try to access for example contact.groupRef[0].Something - then you get the properties of a group object(the first one in the sequence) because the elements of the groupRef are Group objects.

Kind regards,
Zoran
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
Zoran
Telerik team
answered on 17 Sep 2009, 12:21 PM
Hello Gerrit du Preez,

Regarding your second post - the desired result set can be obtained with Linq, but the query should be executed in two parts. The first part will be obtaining the contacts on the server using the Telerik OpenAccess ORM Linq implementation. The second part will be executed on the client where results are filtered based on the content of the 'Groups' collection of each 'Contract'.

Here is a sample implementation(you can use it as reference in your case):
var contracts = scope.Extent<Contract>().ToList(); 
 
var contractsInGroups = contracts.Where(c => c.Groups.Contains(gr=>gr.UserID == 39)).Select(c => c.ContractName); 

Here we select all the 'Contract' objects from the database server. Then we filter that result set based on the Groups that are related to a 'Contract'. We filter by those groups that have a reference to a User with ID = 39. From the returned set of contacts we just select the names of those contacts(as in your SQL query).

Best wishes,
Zoran
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
Gerrit du Preez
Top achievements
Rank 1
answered on 17 Sep 2009, 12:37 PM
Hi Zoran 

Thanks for the speedy reply and the advice.

Will try it out.

Greetings

Gerrit
0
Gerrit du Preez
Top achievements
Rank 1
answered on 18 Sep 2009, 09:15 AM
Hi Zoran

I tried your code, but get this error:

Cannot convert lambda expression to type 'scope.GroupsMain' because it is not a delegate type

 


It is the gr=>gr. part of the code that gives the error:

var contractscontractsInGroups = contracts.Where(c => c.Groups.Contains(gr=>gr.UserID == 39)).Select(c => c.ContractName);  

Any advise?

Thanks

Gerrit

0
Gerrit du Preez
Top achievements
Rank 1
answered on 19 Sep 2009, 07:47 AM
Hi there

 

It seems as if I got it going in one query.

I set up four 1:n references between the 5 tables and playing around with your example and the "101 Linq to OpenAccess Queries" examples, I came up with the following.

 

 
var ListOfContracts = from c in scope.Extent<Contract>()  
 
    where c.Groups.Any(g => g.GroupMainContract.GroupUsers.Any(u => u.UsersLinked.Id == 39))   
                                       
    select new {c.ContractID, c.ContractTitle};  
 

Will still have to test it extensively, but so far the results are consistent.

Any comments will be appreicated.

I would still also like to know why I got the previous "Cannot convert lambda expression to type 'scope.GroupsMain' because it is not a delegate type" error.

Greetings

Gerrit

0
Gerrit du Preez
Top achievements
Rank 1
answered on 21 Sep 2009, 07:41 PM
Hi there

I now have a question regarding adding data to the tables where I set up the one-to-many references.

I have the Contract ID in a session. When I want to add a new record with a Linq query to a table containing a one-to-many reference to the Contract ID, I get these type of errors:

Unable to cast object of type 'System.Int32' to type 'CMSdbMap.Contract'. 

  and

Invalid cast from 'System.Int32' to 'CMSdbMap.Contract'

How do I go about populating a table with data when using one-to-many references in OpenAccess?

Thanks

Gerrit
0
Gerrit du Preez
Top achievements
Rank 1
answered on 22 Sep 2009, 09:30 AM

Me again

I found your article:

How to: Make direct access to foreign key fields of persistent classes

http://www.telerik.com/support/kb/orm/general/direct-access-to-foreign-keys.aspx

It solved the problem.

Thanks

Gerrit

0
Zoran
Telerik team
answered on 22 Sep 2009, 01:08 PM
Hello Gerrit du Preez,

Nice to know our knowledge base is proving to be helpful to our customers. I would like to note that the solution you have found in the KB is a temporary one. These procedure will not be required in the future because OpenAccess will give access to foreign keys out of the box. This is part of our planned improvements for the future.

If you have any more issues, please contact us back.

Regards,
Zoran
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
Rafael
Top achievements
Rank 1
answered on 30 Sep 2009, 04:39 PM

Hello,

I’m having problems with this type of code. If I try to use a sentence like the example, I obtain “Fiel dStrain not found on class Animal” (obviously my classes are different).

But if(continuing with the example) I try a sentence like that.

   var query = from e inscope.Extent<Animal>() select e;

I can access Animal.Strain.SomStrainProperty perfectly, so I think that the references between classes are correct. By the way, I’m using forward mapping.

Any idea about where is the error?

Thank you in advance.

0
Zoran
Telerik team
answered on 02 Oct 2009, 06:07 AM
Hello Rafael,

Can you give us some more details on the exception that you are getting? Maybe you can share the query where you get it as well as a snippet of your class-model. That would give us some more overview of the situation so we can provide you with a decent answer.

Greetings,
Zoran
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
Rafael
Top achievements
Rank 1
answered on 02 Oct 2009, 10:50 AM

Hi Zoran,

First of all, thank you for your replay. I think that best way to explain my situation is with a little Visual Studio solution that reproduces the exception.

That solution is based in two different classes (Employee and Department) an Employee belongs to a Department and I try to show in a RadGrid the Employee ID, employee name and the name of the department associated. When the page is loaded, I use and auxiliary class to show that I can access Employee.Departament.NameDepartment without problems. But if you click in button “Load objects” I’m using a LINQ sentence like.

        var rs = from emp in scope.Extent<Employee>()

                 select new { emp.IdEmployee, emp.NameEmployee, NameDepartment = emp.Department.NameDepartment };

        RadGrid1.DataSource = rs;

        RadGrid1.DataBind();

The system throws an exception like:

Field 'Department' not found on class 'ExampleLib.Employee'.

By the way, there’s a button “Create objects” that create objects to test the problem. The RadGrid refresh is not so good, but reloading the page you can see the effect.

You can download a ZIP file with the example solution from this link:

 http://svn.myariadna.com/Example.zip

Thank you again.

Best regards.

0
Zoran
Telerik team
answered on 06 Oct 2009, 08:14 AM
Hi Rafael,

The reason for the behavior you have experienced is a missing [FieldAlias] attribute on the Department property of your Employee class. The declaration of the field and property for the department in Employee.cs should have the following form:
        private Department department; 
 
        [FieldAlias("department")] 
        public Department Department 
        { 
            get { return department; } 
            set { department = value; } 
        } 

I guess you are already aware of it and it is just a small lapse on your side. The FieldAlias attribute enables a property to be part of a LINQ or OQL query. The fact you were missing that prevented OpenAccess properly processing the LINQ query you had written.

Best wishes,
Zoran
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
Rafael
Top achievements
Rank 1
answered on 06 Oct 2009, 08:36 AM

Hi Zoran,

Thanks a lot for your help. Now it works perfectly.

Yes I was aware about FieldAlias attribute, but I’ve missed it in this case. I very silly error, but I couldn’t see it.

Best wishes.

0
jon
Top achievements
Rank 1
answered on 27 Jan 2010, 11:30 PM

We recently upgrade to the latest version of ORM and are having trouble with grabbing the values from the AnonymousType#1 that our LINQ query is returning:

var resultAnimal = from c in scope.Extent<Animal>()
where a.IsActive == true
orderby a.Location.Building, a.Location.Room, a.Location.Rack
select new
{
a.IdAnimal
,a.AnimalType
,a.Location.IdLocation

};

foreach (var obj in resultAnimal)

{

}

The error is "Cannot convert type Anonymous#1".  You mention in the thread above that "we cannot have 'var' as a return type of a method and hence will need to process the query result to obtain the values from the Anonymous type".

How would we go about doing this as we do need to loop through the results of the query.  Thanks.

0
Alexander
Telerik team
answered on 28 Jan 2010, 06:47 PM
Hello jon,

You have two options. You can write a simple class that contains all fields returned from the query and directly return instances of this class:
List<AnimalProjection> resultAnimal = (from c in scope.Extent<Animal>()
where a.IsActive == true
orderby a.Location.Building, a.Location.Room, a.Location.Rack
select new AnimalProjection()
{
 IdAnimal = a.IdAnimal,
 AnimalType = a.AnimalType,
 IdLocation = a.Location.IdLocation
}).ToList();

or just return the "var" result from the current query. In this case the method return type should be object. It should not be a problem if you are using this method to bind a UI control - the control will find the properties of the anonymous class automatically. We use this approach in our Linq 101 demo application. You can have a look at this application for more details.

All the best,
Alexander
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 02 Feb 2010, 07:40 PM

Its odd, most of the the code runs fine, but sporadically I get this error:

Unable to cast object of type '<>f__AnonymousType2`2[System.String,System.Int32]' to type '<>f__AnonymousType2`2[System.String,System.Int32]'.

When I run this code:

// Get Status data
var resultStatus = from s in scope.Extent<LKUPAnimalStatus>() select new { s.AnimalStatusDescription, s.IdAnimalStatus };
if (resultStatus != null && resultStatus.Count() > 0)
{
foreach (var obj in resultStatus)
{
ListItem i = new ListItem(obj.AnimalStatusDescription.ToString(), obj.IdAnimalStatus.ToString());
ddlStatus.Items.Add(i);
}

Most of the time when I run the code its fine, but maybe 15% of the time I gives me that "unalbe to case object" error.  I triple checked that I have the latest DLLs:  Telerik.OpenAccess 2009.3.1119.2 

Ideas?

0
Alexander
Telerik team
answered on 04 Feb 2010, 07:53 PM
Hi jon,

This exception most likely means that there are two similar anonymous classes in two different assemblies, thereby instance of the first type cannot be cast to the other type. However, this does not explain why this is happening only occasionally or why there are two assemblies containing the same anonymous type. This problem has already been reported once by another customer but it is really not trivial to represent and find the actual cause.
I truly recommend you to use an explicitly defined type which contains the fields returned from the query. This would be much more secure than passing anonymous instances along the application.

All the best,
Alexander
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 04 Feb 2010, 08:21 PM
OKay, and FYI, 

I find that when I remove the IF statement before the FOREACH statement then it seems the errors may not occur at all.  So maybe using resultStatus.Count does something to the anonymous type.  Hopefully that's part of the answer to this at least.

if (resultStatus != null && resultStatus.Count() > 0)
{
foreach (var obj in resultStatus)
{

0
Alexander
Telerik team
answered on 08 Feb 2010, 11:39 AM
Hello jon,

Thank you for the information, we will have it in mind if we manage to reproduce the error.

Regards,
Alexander
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, 11:49 PM

We continue to get AnonyousType issues sporadically.  I believe this happens when the page does not fully unload.  I tried using the ScopeFactory to help manage my scope, but that does not solve the problem.

I agree that its best not to pass Anonymous types around, so I'm going to create a bunch of classes.  Do you have a sample showing how best to create a class like the List<AnimalProjection> that you mentioned.

0
Accepted
Alexander
Telerik team
answered on 11 Feb 2010, 02:01 PM
Hello jon,

You actually need to create only the AnimalProjection class and slightly modify the Linq query to return a List of such instances:
List<AnimalProjection> resultAnimal = (from c in scope.Extent<Animal>()
where a.IsActive == true
orderby a.Location.Building, a.Location.Room, a.Location.Rack
select new AnimalProjection()
{
 IdAnimal = a.IdAnimal,
 AnimalType = a.AnimalType,
 IdLocation = a.Location.IdLocation
}).ToList();

The AnimalProjection class itself is nothing special, just a class with the necessary properties that will hold the values of the projection:
public class AnimalProjection
{
    public int IdAnimal {set;get;}
    public AnimalType AnimalType {set;get;}
    public int IdLocation {set;get;}
}
Hope that helps.

All the best,
Alexander
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
jon
Top achievements
Rank 1
Answers by
Alexander
Telerik team
jon
Top achievements
Rank 1
Ady
Telerik team
Andreas Kaech
Top achievements
Rank 1
Gerrit du Preez
Top achievements
Rank 1
Zoran
Telerik team
Rafael
Top achievements
Rank 1
Share this question
or