locked
need help in LINQ sub queries RRS feed

  • Question

  • User-1106823036 posted

    my issue is like this:

    I have segments (circles on google maps), in each segment I have users (customers) I want to calculate the total profit of each segment by summing up the profit of each user

    here is my database tables:

    Table user : custId, segId

    Table sess-cust : custId, sessId

    Tbale session: sessId, profit

    1---> need to get for each segment the custIds

    2--->need to get the sessId per segment (depending on the users in this segment)

    3---> need to sum up the profit of these sessId of each segment

    the first part is easy

    var custIdsInSeg = from a in db.users
                                      
                                       group a by a.segId into grouped
                                       select new
                                       {
                                           segId = grouped.Key,
                                           custIds = from item in grouped
                                                     select item.custId
                                       };

    this gives me for each segId the custIds in that seg

    now I want 2 and 3

    how can I get for each segId the sessIds?

    and how can I sum up for each segment the profit

    so at the end this is the output that I want to have:

    segid   profit

     1          10000

     2           20000

    Anyone please?

    thank you in advance

    Tuesday, July 7, 2015 2:54 AM

Answers

  • User-84896714 posted

    Hi lolo512,

    so I think there is a missing part of what you wrote

    That is full code, and works fine on my side, did you use entity framework to generate entities? Below is my generated code.

        public partial class session
        {
            public session()
            {
                this.users = new HashSet<user>();
            }
        
            public int sessId { get; set; }
            public decimal profit { get; set; }
        
            public virtual ICollection<user> users { get; set; }
        }
    
        public partial class user
        {
            public user()
            {
                this.sessions = new HashSet<session>();
            }
        
            public int custId { get; set; }
            public int segId { get; set; }
        
            public virtual ICollection<session> sessions { get; set; }
        }

    Best Regards,
    Wang Li

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 9, 2015 4:21 AM

All replies

  • User-84896714 posted

    Hi lolo512,

    Thank you for your post.
    I wrote demo for your reference.

    DBEntities db = new DBEntities();
    var data = from u in db.users group u by u.segId into g select new { segId = g.Key, totalProfit = g.Sum(s => s.sessions.Sum(p => p.profit)) };

    Best Regards,
    Wang Li

    Wednesday, July 8, 2015 4:50 AM
  • User-1106823036 posted

    thank you for your reply

    but what is sessions supposed to be in 

    g.Sum(s => s.sessions.Sum(p => p.profit))

    session is not an attribute of table users, there is a many to many relation between users and table session which is user=session

    so I think there is a missing part of what you wrote

    Wednesday, July 8, 2015 5:07 AM
  • User-84896714 posted

    Hi lolo512,

    so I think there is a missing part of what you wrote

    That is full code, and works fine on my side, did you use entity framework to generate entities? Below is my generated code.

        public partial class session
        {
            public session()
            {
                this.users = new HashSet<user>();
            }
        
            public int sessId { get; set; }
            public decimal profit { get; set; }
        
            public virtual ICollection<user> users { get; set; }
        }
    
        public partial class user
        {
            public user()
            {
                this.sessions = new HashSet<session>();
            }
        
            public int custId { get; set; }
            public int segId { get; set; }
        
            public virtual ICollection<session> sessions { get; set; }
        }

    Best Regards,
    Wang Li

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 9, 2015 4:21 AM