Results from Multiple Tables

33 posts, 2 answers
  1. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 19 May 2009 Link to this post

    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.
  2. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 20 May 2009 Link to this post

    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.
  3. DevCraft banner
  4. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 20 May 2009 Link to this post

    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.

     

     

     

     

     

     

     

     

  5. Ady
    Admin
    Ady avatar
    589 posts

    Posted 20 May 2009 Link to this post

    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.
  6. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 20 May 2009 Link to this post

    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.
  7. Ady
    Admin
    Ady avatar
    589 posts

    Posted 22 May 2009 Link to this post

    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.
  8. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 22 May 2009 Link to this post

    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.
  9. Answer
    Ady
    Admin
    Ady avatar
    589 posts

    Posted 25 May 2009 Link to this post

    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.
  10. Andreas Kaech
    Andreas Kaech avatar
    121 posts
    Member since:
    Sep 2004

    Posted 23 Jun 2009 Link to this post

    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
  11. Ady
    Admin
    Ady avatar
    589 posts

    Posted 25 Jun 2009 Link to this post

    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.
  12. Gerrit du Preez
    Gerrit du Preez avatar
    35 posts
    Member since:
    Aug 2012

    Posted 16 Sep 2009 Link to this post

    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

     

  13. Gerrit du Preez
    Gerrit du Preez avatar
    35 posts
    Member since:
    Aug 2012

    Posted 17 Sep 2009 Link to this post

     

    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

  14. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 17 Sep 2009 Link to this post

    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.
  15. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 17 Sep 2009 Link to this post

    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.
  16. Gerrit du Preez
    Gerrit du Preez avatar
    35 posts
    Member since:
    Aug 2012

    Posted 17 Sep 2009 Link to this post

    Hi Zoran 

    Thanks for the speedy reply and the advice.

    Will try it out.

    Greetings

    Gerrit
  17. Gerrit du Preez
    Gerrit du Preez avatar
    35 posts
    Member since:
    Aug 2012

    Posted 18 Sep 2009 Link to this post

    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

  18. Gerrit du Preez
    Gerrit du Preez avatar
    35 posts
    Member since:
    Aug 2012

    Posted 19 Sep 2009 Link to this post

    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

  19. Gerrit du Preez
    Gerrit du Preez avatar
    35 posts
    Member since:
    Aug 2012

    Posted 21 Sep 2009 Link to this post

    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
  20. Gerrit du Preez
    Gerrit du Preez avatar
    35 posts
    Member since:
    Aug 2012

    Posted 22 Sep 2009 Link to this post

    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

  21. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 22 Sep 2009 Link to this post

    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.
  22. Rafael
    Rafael avatar
    8 posts
    Member since:
    Dec 2007

    Posted 30 Sep 2009 Link to this post

    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.

  23. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 02 Oct 2009 Link to this post

    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.
  24. Rafael
    Rafael avatar
    8 posts
    Member since:
    Dec 2007

    Posted 02 Oct 2009 Link to this post

    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.

  25. Zoran
    Admin
    Zoran avatar
    534 posts

    Posted 06 Oct 2009 Link to this post

    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.
  26. Rafael
    Rafael avatar
    8 posts
    Member since:
    Dec 2007

    Posted 06 Oct 2009 Link to this post

    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.

  27. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 27 Jan 2010 Link to this post

    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.

  28. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 28 Jan 2010 Link to this post

    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.
  29. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 02 Feb 2010 Link to this post

    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?

  30. Alexander
    Admin
    Alexander avatar
    727 posts

    Posted 04 Feb 2010 Link to this post

    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.
  31. jon
    jon avatar
    33 posts
    Member since:
    Dec 2008

    Posted 04 Feb 2010 Link to this post

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

Back to Top
DevCraft banner