Solved

Linq query for parent & Child tables with aggregates

Posted on 2010-11-11
3
617 Views
Last Modified: 2012-05-10
Hey,

I have 2 tables (PTO [Parent] &  PTODetails [Child]) that have a 1 to many relationship between them.  I would like to get the information from the parent table and some aggregated information from the child table using linq.  

If i where using SQL i would write something like this:

Select PTO.ID, Max(PTODetails.RequestDate), Min(PTODetails.RequestDate), PTO.PersonID
From  PTO Inner Join PTODetails on PTO.ID = PTODetails.PTOID
Where PTO.RequestDate > GetDate()

How would i go about writting something like that in LINQ?

Thanks in advance...

0
Comment
Question by:MRS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 250 total points
ID: 34119837
There are a whole heap of resources on LINQ, so you just need to find the right one.

101 LINQ Samples
http://msdn.microsoft.com/en-us/vcsharp/aa336746.aspx

Converting SQL to LINQ, Part 5: GROUP BY and HAVING (Bill Horst)
http://blogs.msdn.com/b/vbteam/archive/2007/12/18/converting-sql-to-linq-part-5-group-by-and-having-bill-horst.aspx

From Shipment In OrderTable 
Group OrderCost = Shipment.Cost By Shipment.ShippingZip 
Into Total = Sum(OrderCost), Average(OrderCost)

Open in new window

0
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 250 total points
ID: 34119891
Here's a simple example
Dim Customers As New List(Of Customer)
    Dim Orders As New List(Of Order)
    Private Sub LoadCustomers()
        Customers.Add(New Customer With {.CustomerID = 1, .Name = "AAA"})
        Customers.Add(New Customer With {.CustomerID = 2, .Name = "BBB"})
        Customers.Add(New Customer With {.CustomerID = 3, .Name = "CCC"})
        Orders.Add(New Order With {.CustomerID = 2, .OrderID = 23})
        Orders.Add(New Order With {.CustomerID = 1, .OrderID = 11})
        Orders.Add(New Order With {.CustomerID = 1, .OrderID = 13})
        Orders.Add(New Order With {.CustomerID = 2, .OrderID = 27})
        Orders.Add(New Order With {.CustomerID = 2, .OrderID = 24})
        Orders.Add(New Order With {.CustomerID = 2, .OrderID = 26})
        Orders.Add(New Order With {.CustomerID = 2, .OrderID = 25})
        Orders.Add(New Order With {.CustomerID = 3, .OrderID = 39})
        Orders.Add(New Order With {.CustomerID = 1, .OrderID = 12})
        Orders.Add(New Order With {.CustomerID = 3, .OrderID = 31})
        Orders.Add(New Order With {.CustomerID = 3, .OrderID = 32})
        Orders.Add(New Order With {.CustomerID = 2, .OrderID = 22})
    End Sub

    Public Sub GroupLinq()
        LoadCustomers()
        Dim Q = From Cust In Customers, Ord In Orders
                Where Cust.CustomerID = Ord.CustomerID
                Group Ord By Cust Into
                MaxID = Max(Ord.OrderID),
                MinID = Min(Ord.OrderID)

        For Each obj In Q
            Console.WriteLine(String.Format("Customer: {0}, Min: {1}, Min: {2}", obj.Cust.CustomerID, obj.MinID, obj.MaxID))
        Next
        Console.ReadLine()
    End Sub

Open in new window

0
 

Author Closing Comment

by:MRS
ID: 34120594
Thank you both.  I am not sure what exactly i was missing, but both of your examples got me what i needed..

Thanks again
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question