[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

LINQ Query

Posted on 2011-09-16
9
Medium Priority
?
359 Views
Last Modified: 2013-11-11
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

0
Comment
Question by:tknudsen-qec
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
9 Comments
 
LVL 21

Expert Comment

by:naspinski
ID: 36550139
Untested, but this should work:
var matches = MatchList.Select(x => x.Authority);//gets a IEnumerable<string>
var results = Authorities.Where(x => matches.Contains(x.CanAuthorize));

Open in new window

0
 
LVL 3

Author Comment

by:tknudsen-qec
ID: 36550335
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));

    }

Open in new window


0
 
LVL 3

Author Comment

by:tknudsen-qec
ID: 36550352
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:tknudsen-qec
ID: 36551034
Scratch that, my query doesn't work either.
0
 
LVL 3

Author Comment

by:tknudsen-qec
ID: 36551199
This seems to work but its not pretty.

 
// 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();

Open in new window


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
)

Open in new window



Which, since it works and nobody has an alternative, will serve as the "answer".

Thx go to naspinski for making an effort however.

0
 
LVL 3

Author Comment

by:tknudsen-qec
ID: 36560149
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.
0
 
LVL 3

Accepted Solution

by:
nixkuroi earned 2000 total points
ID: 36558469
Try this one:

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<string> MatchLists = new List<string>();
      MatchLists.Add("Medical");
      MatchLists.Add("Business");


      List<string> authsWithAll = Authorities.GroupBy(i => i.Position, (key, group) => group.First()).ToDictionary(d => d.Position, d => d).Keys.ToList().ToDictionary(d => d, d => Authorities.Where(w => w.Position == d).ToList().ConvertAll(c => c.CanAuthorize).ToList()).Where(w => MatchLists.Except((List<string>)w.Value).ToList().Count == 0 && ((List<string>)w.Value).Except(MatchLists).ToList().Count == 0).ToDictionary(d=>d.Key, d=>d.Value).Keys.ToList();
0
 
LVL 3

Author Comment

by:tknudsen-qec
ID: 36560150
nixkuroi's solution works.  Please cancel the close-request so I can offer points.
0
 
LVL 3

Author Closing Comment

by:tknudsen-qec
ID: 36560168
@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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

656 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