tknudsen-qec
asked on
LINQ Query
I can't seem to wrap my brain around what strikes me as very simple.
Assume I have these two simple tables (IEnumerables really)
Table1: Authorities (IEnumerable<AuthorityRow> )
Position CanAuthorize
======================
CEO MedicalTravel
CEO BusinessTravel
CFO MedicalTravel
CFO BusinessTravel
HRLeader MedicalTravel
Table2: MatchList (IEnumerable<MatchListRow> )
Authority
=============
MedicalTravel
BusinessTravel
How can I produce a list of Positions who have all the Authorities listed in MatchList?
In the example above, CFO and CEO should be produced, but not HR Leader.
I normally prefer the method-based syntax but I'm not too particular and would be just as pleased with query-based.
tk
Assume I have these two simple tables (IEnumerables really)
Table1: Authorities (IEnumerable<AuthorityRow>
Position CanAuthorize
======================
CEO MedicalTravel
CEO BusinessTravel
CFO MedicalTravel
CFO BusinessTravel
HRLeader MedicalTravel
Table2: MatchList (IEnumerable<MatchListRow>
Authority
=============
MedicalTravel
BusinessTravel
How can I produce a list of Positions who have all the Authorities listed in MatchList?
In the example above, CFO and CEO should be produced, but not HR Leader.
I normally prefer the method-based syntax but I'm not too particular and would be just as pleased with query-based.
tk
ASKER
That was pretty much identical to my first attempt naspinski, but it fails because "HR Leader" produces one match on "Medical" and therefore ends up in the results list with the others. Tested as attached.
public class Authority
{
public Authority(string pos, string canauth)
{
Position = pos;
CanAuthorize = canauth;
}
public string Position { get; set; }
public string CanAuthorize { get; set; }
}
public class MatchList
{
public MatchList(string auth)
{
Authority = auth;
}
public string Authority { get; set; }
}
protected void Page_Load(object sender, EventArgs e)
{
List<Authority> Authorities = new List<Authority>();
Authorities.Add(new Authority("CEO", "Medical"));
Authorities.Add(new Authority("CEO", "Business"));
Authorities.Add(new Authority("CFO", "Medical"));
Authorities.Add(new Authority("CFO", "Business"));
Authorities.Add(new Authority("HR Leader", "Medical"));
List<MatchList> MatchLists = new List<MatchList>();
MatchLists.Add(new MatchList("Medical"));
MatchLists.Add(new MatchList("Business"));
var matches = MatchLists.Select(x => x.Authority);//gets a IEnumerable<string>
var results = Authorities.Where(x => matches.Contains(x.CanAuthorize));
}
ASKER
This is how I'd do it in T-SQL but I'm unsure of the translation to LINQ:
SELECT DISTINCT A.Position
FROM #Authorities A
INNER JOIN
(
SELECT Distinct Position, Authority
FROM #Authorities A
CROSS JOIN #MatchList M
) M ON M.Authority = A.CanAuthorize AND M.Position = A.Position
SELECT DISTINCT A.Position
FROM #Authorities A
INNER JOIN
(
SELECT Distinct Position, Authority
FROM #Authorities A
CROSS JOIN #MatchList M
) M ON M.Authority = A.CanAuthorize AND M.Position = A.Position
ASKER
Scratch that, my query doesn't work either.
ASKER
This seems to work but its not pretty.
It emulates this SQL query:
Which, since it works and nobody has an alternative, will serve as the "answer".
Thx go to naspinski for making an effort however.
// create a cartesian product of all possible position/authority combinations
IEnumerable<Authority> product =
from M in MatchLists
from A in Authorities.Select( x => x.Position).Distinct()
select new Authority(A, M.Authority);
// get a list of positions that dont match back to our list of valid combinations
IEnumerable<string> invalidpositions =
from p in product
join a in Authorities on new { p.CanAuthorize, p.Position } equals new { a.CanAuthorize, a.Position } into ps
from a in ps.DefaultIfEmpty()
where a == null
select p.Position;
// get a list of positions that arent in our list of non-matches
IEnumerable<string> validpositions = (from a in Authorities
where !invalidpositions.Any(x => x == a.Position)
select a.Position).Distinct();
It emulates this SQL query:
SELECT DISTINCT Position
FROM #Authorities
WHERE Position NOT IN
(
SELECT DISTINCT M.Position
FROM
(
SELECT Distinct Position, Authority
FROM #Authorities A
CROSS JOIN #MatchList M
) M
LEFT OUTER JOIN #Authorities A ON M.Authority = A.CanAuthorize AND M.Position = A.Position
WHERE A.Position IS NULL
)
Which, since it works and nobody has an alternative, will serve as the "answer".
Thx go to naspinski for making an effort however.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for tknudsen-qec's comment http:/Q_27312340.html#36551199
for the following reason:
Own solution works, no working alternatives provided.<br /><br />I'd be pleased to re-offer points (if permitted) if a better solution provided.
Accepted answer: 0 points for tknudsen-qec's comment http:/Q_27312340.html#36551199
for the following reason:
Own solution works, no working alternatives provided.<br /><br />I'd be pleased to re-offer points (if permitted) if a better solution provided.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
nixkuroi's solution works. Please cancel the close-request so I can offer points.
ASKER
@nixkuroi:
Thanks, that seems to work fine. Thanks for not pointing out that I invented a "string" class for MatchLists. Not sure what I was thinking.
Thanks, that seems to work fine. Thanks for not pointing out that I invented a "string" class for MatchLists. Not sure what I was thinking.
Open in new window