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
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 }; |
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.
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.
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,
Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
Is there a Support document or Example that steps through how to do this?
Thanks.
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,
Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
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:
- Add a collection field to both the classes -
- 'IList<Animal> animals' in Location class
- 'IList<Location> locations' in Animal class
- 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
- 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
- Select the project in the Solution Explorer and press F4. Set the 'UpdateDatabase' property to True
- 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,
Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
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
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,
Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.
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
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
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.
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.
Thanks for the speedy reply and the advice.
Will try it out.
Greetings
Gerrit
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
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
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
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
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.
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.
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.
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.
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.
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.
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.
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.
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?
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.
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)
{
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.
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.
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
;}
}
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.