none
Foreign key list to class RRS feed

  • Question

  • Okay, I've seen the solution to this somewhere ... but I can't find it anymore :s ...

    Here goes:

    There are 2 Tables.

    Order with OrderID/Contact/Adres/Status
    OrderLine with OrderLineID/OrderID/ActionInfo

    Now I have an Order class and an OrderLine class with above properties and the Order class has a List of OrderLine objects.

    Now I'd like a LINQ query which pulls all Orders & OrderLines and creates the above classes.

     

    Many thanks in advance,
    Yannick

    Wednesday, July 14, 2010 7:11 AM

Answers

  • If you save this in a IEnumerable, IQueryable ... it will have the Count property, also ...

    There are other ways but this is simpler and although you will iterate the collection is easier than querying the database twice.

    Minor clarification: it should be && not & and you can use == instead of Equals when not using an interfase.

    Regards

    Wednesday, July 14, 2010 6:11 PM
  • Hi yannick,

    Based on my understanding, your Order and OrderLine tables have a parent - child relationship. You want to get the number of OrderLine for each Order in the code you post. If my understanding is right, you can refer to following example: (Orders table corresponds to your Order table and Order_Details table corresponds to your OrderLine table)

    NorthwindDataContext db = new NorthwindDataContext();
    var results = from p in db.Orders
           where p.CustomerID == "VINET" 
           orderby p.OrderDate descending 
           select new
           {
             CustomerID = p.CustomerID,
             OrderDate = p.OrderDate,
             ShipCountry = p.ShipCountry, 
             OrderDetails = from a in db.Order_Details 
                     where a.OrderID == p.OrderID 
                     select new 
                     {
                       ProductID = a.ProductID ,
                       UnitPrice = a.UnitPrice ,
                       Quantity = a.Quantity 
                     },
             Count = p.Order_Details.Count        
           };
    
    
    Best regards,
    Alex Liang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, July 16, 2010 8:06 AM
    Moderator

All replies

  • Ok I didn't know you could so it like this ( see below ), I'm a total LINQ newbie ... glad I found it out, but I should also store a Count of the OrderLines which I'm unable to put in the LINQ statement. See commented lines where the Count should be stored. I'm just doing the query twice now to get a count .. but I'm sure this can be done in a better way.

    var leads = from p in leadsET.tblleads
                where p.am.Equals("CUE") & p.status.Equals("open")
                orderby p.datum descending
                select new LeadDOM
                {
                  am = p.am,
                  datum = p.datum,
                  leadid = p.leadid,
                  leadvoor = p.leadvoor,
                  omschrijving = p.omschrijving,
                  leadacties = from a in leadsET.tblleadacties
                         where a.leadid == p.leadid
                         select new LeadActieDOM
                        {
                          actieid = a.actieid,
                          actie = a.actie,
                          leadid = a.leadid,
                          datum = a.datum,
                          status = a.status
                        },
                  //Querying the same as above.
                  aantalActies = (from b in leadsET.tblleadacties
                          where b.leadid == p.leadid select b).Count()
                  //
                };
    Wednesday, July 14, 2010 8:10 AM
  • If you save this in a IEnumerable, IQueryable ... it will have the Count property, also ...

    There are other ways but this is simpler and although you will iterate the collection is easier than querying the database twice.

    Minor clarification: it should be && not & and you can use == instead of Equals when not using an interfase.

    Regards

    Wednesday, July 14, 2010 6:11 PM
  • Hi yannick,

    Based on my understanding, your Order and OrderLine tables have a parent - child relationship. You want to get the number of OrderLine for each Order in the code you post. If my understanding is right, you can refer to following example: (Orders table corresponds to your Order table and Order_Details table corresponds to your OrderLine table)

    NorthwindDataContext db = new NorthwindDataContext();
    var results = from p in db.Orders
           where p.CustomerID == "VINET" 
           orderby p.OrderDate descending 
           select new
           {
             CustomerID = p.CustomerID,
             OrderDate = p.OrderDate,
             ShipCountry = p.ShipCountry, 
             OrderDetails = from a in db.Order_Details 
                     where a.OrderID == p.OrderID 
                     select new 
                     {
                       ProductID = a.ProductID ,
                       UnitPrice = a.UnitPrice ,
                       Quantity = a.Quantity 
                     },
             Count = p.Order_Details.Count        
           };
    
    
    Best regards,
    Alex Liang
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, July 16, 2010 8:06 AM
    Moderator