select first child of entity

4 posts, 0 answers
  1. Issam
    Issam avatar
    18 posts
    Member since:
    Sep 2008

    Posted 03 Feb 2014 Link to this post

    hi,

    let's say that i have a customers and invoices tables

    what i want is to select all the customers with their last invoice based on descending date



    thanks and good day
  2. Boyan
    Admin
    Boyan avatar
    100 posts

    Posted 06 Feb 2014 Link to this post

    Hello Issam,

    Lets assume you have the following set up - tables named Customers and Invoices with one-to-many relationship between them. Additionally the Invoice table has a date column.
    In order to select all customers along with the newest invoice you could use the following LINQ query:

    var customers = context.Customers.Include(c => c.Invoices).Select(c => new
    {
        Customer = c,
        LatestInvoice = c.Invoices.OrderByDescending(i=>i.Date).FirstOrDefault()
    });

    Please note that the above is not an optimal solution as the Include() method will load all invoices as well but could be easily applied if you do not expect a large number of invoices. On the other hand you could omit the Include() method resulting in a query execution per customer which could be still applicable in case of a small amount of customers for example if customers are displayed in small groups (pages). 

    If both of the above solutions does not meet your requirements, you could make native SQL query or stored procedure in order to retrieve just the right data from the database in a single query. If you are interested in such solution we would be happy to assist you.

    Please let us know if you have any further questions.

    Regards,
    Boyan
    Telerik
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
  3. DevCraft banner
  4. Issam
    Issam avatar
    18 posts
    Member since:
    Sep 2008

    Posted 12 Feb 2014 in reply to Boyan Link to this post

    Thanks Boyan,

    it worked just fine !,
    in my form i have 2 panels, a search and a detail one,
    in the the search i have a listbox binded to a minimal patients informations

    yes actually it's a medical app,
    and when the user select a patient the detail panel shows all the details of the selected one with a new query that look like the one you showed me, and afect the datacontext of the detail panel with

    so at any time the de details (includes) are fetched from  database for a single patient

    now this seem to work  with this scenario, and i am interested on your opinion about this configuration

    thanks again
  5. Boyan
    Admin
    Boyan avatar
    100 posts

    Posted 17 Feb 2014 Link to this post

    Hi Issam,

    From the additional information you have provided it seems that the most appropriate solution for you would be to populate your search listbox with one query, for examples:

    var customers = context.Customers.Where(x=>x.FirstName == firstName);

    Once you have the list of customers that satisfy a criteria and would like to display the details panel with additional information, you could create a second query selecting the latest invoice for a customer (querying by the customer's primary key) like so:
     
    var latestInvoice = customers.First(x=>x.Id == selectedCustomerId).Invoices.FirstOrDefault();

    Writing your queries this way (avoiding projection in anonymous types)  would help you minimize the database queries and traffic.
    In case you have a desktop application and have a read-only search-details form (no transactions are expected) it would be feasible to use a single context on that page this way taking further advantage of the Telerik Data Access Level One Cache. On the other hand if you have a web application and your context is disposed per request it is possible to enable the Telerik Data Access Second Level Cache gaining additional performance boast. More information on how to work with the cache and what benefits it provides could be found in this documentation article

    I hope this helps you. Do not hesitate to contact us with any further questions. 

    Regards,
    Boyan
    Telerik
    OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
Back to Top