Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Linq Query - Multiple Table Selection

Posted on 2012-03-15
5
Medium Priority
?
427 Views
Last Modified: 2012-04-02
Hello.  Let's say we have the tables below:

PROJECT
ID    Name
1      Roadwork

PROJECTMEMBERS
ID           Name    
11           John  
22           Sally
33           Jane
44           Joan          

SIGNOFFS
ID             PROJECTID   MEMBERID   DATEOFSIGNOFF
111           1                  11                1/5/2012
222           1                  22                1/6/2012
333           2                  44                1/9/2012

Each project will have 2 members and each member will sign off on the project.  I need a Linq query that will select a project and also the two project members that have signed off on the project (if any).

The required dataset for this example is below:


DATASET:
PROJID   PROJNAME   MEMBERID1   DATE1SIGNOFF    MEMBERID2   DATE2SIGNOFF
1            Roadwork     11                     1/5/2012                 22                   1/6/2012

This dataset will be sent to a dataGrid for display.  At this point, I'm not certain how to select multiple rows from the PROJECTMEMBERS table.  A full query would be appreciated.

Environment:  C#.NET 4.0
0
Comment
Question by:adskarcox
  • 4
5 Comments
 

Author Comment

by:adskarcox
ID: 37726362
Note: if there is a better way to do this, than through a single Linq query, I am open to suggestions.
0
 
LVL 6

Expert Comment

by:Triskelion
ID: 37727603
I made a mock-up of the data containers.
The linq generates an ILookup with the project as the key and a key/value pair of the project-member and the signoff objects.
The printing might need a little filtering if the number of members on the project is not as expected.
using System;
using System.Collections.Generic;
using System.Linq;

namespace EE_27633596_CS_CON
{
   public class CProject
   {
      public int intProjectId { get; set; }
      public string strProjectName { get; set; }
      public override string ToString()
      {
         return intProjectId.ToString() + '-' + strProjectName;
      }

      public override bool Equals(object obj)
      {
         return this.ToString().Equals(((CProject)obj).ToString());
      }

      public override int GetHashCode()
      {
         return this.ToString().GetHashCode();
      }
   }

   public class CProjectMember
   {
      public int intMemberId { get; set; }
      public string strMemberName { get; set; }
   }

   public class CSignOff
   {
      public int intSignOffID { get; set; }
      public int intProjectId { get; set; }
      public int intMemberId { get; set; }
      public DateTime dtSignOffDate { get; set; }
   }

   class Program
   {
      static void Main(string[] args)
      {
         List<CProject> lstProjects = new List<CProject>()
         {
            new CProject(){intProjectId=0, strProjectName="Roadwork"},
            new CProject(){intProjectId=1, strProjectName="Crosswalk"},
            new CProject(){intProjectId=2, strProjectName="WaterMain"}
         };

         List<CProjectMember> lstMembers = new List<CProjectMember>()
         {
            new CProjectMember(){intMemberId=100, strMemberName="Tom"},
            new CProjectMember(){intMemberId=101, strMemberName="Dave"},
            new CProjectMember(){intMemberId=102, strMemberName="Joe"}
         };

         List<CSignOff> lstSignOffs = new List<CSignOff>()
         {
            new CSignOff() {intSignOffID=0, intMemberId=100, intProjectId=0, dtSignOffDate=DateTime.Now},
            new CSignOff() {intSignOffID=1, intMemberId=101, intProjectId=0, dtSignOffDate=DateTime.Now},
            new CSignOff() {intSignOffID=2, intMemberId=100, intProjectId=1, dtSignOffDate=DateTime.Now},
            new CSignOff() {intSignOffID=3, intMemberId=102, intProjectId=1, dtSignOffDate=DateTime.Now},
            new CSignOff() {intSignOffID=4, intMemberId=101, intProjectId=2, dtSignOffDate=DateTime.Now},
            new CSignOff() {intSignOffID=5, intMemberId=102, intProjectId=2, dtSignOffDate=DateTime.Now}
         };

         ILookup<CProject, KeyValuePair<CProjectMember, CSignOff>> lkup =
         (
            from so in lstSignOffs
            join p in lstProjects on so.intProjectId equals p.intProjectId
            join m in lstMembers on so.intMemberId equals m.intMemberId
            select new
            {
               k = p,
               v = new KeyValuePair<CProjectMember, CSignOff>(m, so)
            }
         ).ToLookup(k => k.k, v => v.v);

         foreach (var x in lkup)
         {
            Console.WriteLine("{0}: {1}\t{2}\t{3}\t{4}\t{5}\t{6}\t{7}",
               x.Key.intProjectId,
               x.Key.strProjectName,
               x.ElementAt(0).Key.intMemberId,
               x.ElementAt(0).Key.strMemberName,
               x.ElementAt(0).Value.dtSignOffDate.ToShortDateString(),
               x.ElementAt(1).Key.intMemberId,
               x.ElementAt(1).Key.strMemberName,
               x.ElementAt(1).Value.dtSignOffDate.ToShortDateString());
         }
      }
   }
}

Open in new window

0
 

Author Comment

by:adskarcox
ID: 37768059
Why can't this be accomplished with one query?  Or even with a few queries working together?

Almost 100 lines of code to accomplish this seems very excessive.

There has got to be a more efficient way.
0
 

Accepted Solution

by:
adskarcox earned 0 total points
ID: 37776580
I found a way using an IN( ) clause in the linq query.  Inside each IN clause, I put a separate linq query (similar to how it is done in SQL).
0
 

Author Closing Comment

by:adskarcox
ID: 37795071
This resolved my issue.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

581 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