adskarcox
asked on
Linq Query - Multiple Table Selection
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
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
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.
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());
}
}
}
}
ASKER
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.
Almost 100 lines of code to accomplish this seems very excessive.
There has got to be a more efficient way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This resolved my issue.
ASKER