Entity Framework - Group and Min

Question

The below code is the second run in an Single Transfer Vote(STV) System.

' Elected Officials From Previous Count with excess votes
Dim Officials = From o In PlatoDB.ElectionCounts
                Where o.Count = ElectionCount - 1 AndAlso o.Votes > Quota
                Order By o.Votes Descending

If ElectionCount - 1 = 1 Then
   For Each o In Officials
       Dim oVotes = GetVotes(ElectionId)
       Dim oInheritableVotes = From vote In oVotes
                               Where vote.Preference = 1 AndAlso
                                     vote.LinkId = o.CandidateId AndAlso
                                     vote.Vote_Headers.Used = False
                               Select vote.Vote_Headers

My current problem is here under where I need to group by Vote_HeaderID and the retrieve the vote with the min preference. The best I've gotten so far is the min preference and the vote's parent(Vote_HeaderId).

       ' Gets the next available preference which hasn't been elected
       Dim NextPreference = From inheritedVote In oInheritableVotes
                            From v In PlatoDB.Votes
                            Where v.Vote_HeaderId = inheritedVote.Id AndAlso
                                  v.Preference > 1 AndAlso
                                  Not Officials.Select(Function(f) f.CandidateId).Equals(v.LinkId)
                            Group v By v.Vote_HeaderId Into Group
                            Select Vote_HeaderId, MinVote = Group.Min(Function(f) f.Preference)).ToList
    Next
Else

End If

Does anyone have a suggestion how I could get the vote_id directly rather having to use the current list to get the actual votes.

http://stackoverflow.com/questions/9997001/linq-group-on-multiple-fields-vb-net-anonymous-key — CheGueVerra, Aug 28 '14 at 22:38
@CheGueVerra I have an idea of what I need to do I just need a little push since I'm close just not there yet. — Enzero, Aug 29 '14 at 06:45

CheGueVerra · Answer 1 · 2014-08-29T18:58:26.157

The way I understand Groups is something like this:

Group x By Key = new with { Key .FirstKey = Entity.Property, .EntityFk = Entity.PropertyB,  [.KeyName = Entity.PropertyX]} Into GroupName = Group
Select new [Type Or Anonymous] With { .EntityId = Key.FirstKey, .EntityFk = }

So, your code should look something like this:

Dim NextPreference = From inheritedVote In oInheritableVotes
                            From v In PlatoDB.Votes
                            Where v.Vote_HeaderId = inheritedVote.Id AndAlso
                                  v.Preference > 1 AndAlso
                                  Not Officials.Select(Function(f) f.CandidateId).Equals(v.LinkId)
                            Group v By Key = new with {Key .VoteHeaderId = v.Vote_HeaderId} Into VoteHeader = Group
                            Select new With {.VoteHeaderId = Key.VoteHeader, .MinVote = Group.Min(Function(f) f.Preference)}.ToList

That should get you started

Cheers!

UPDATE

Keep in mind that without the full structure it's kinda hard to test out but I think you need to change the grouping like this

   Group v By v.Vote_HeaderId} Into VoteHeader = Group
   let MinVote = Group.Min(Function(f) f.Preference)
   from g in VoteHeader
   Select new With {.VoteId = g.vote_id, .MinVote = MinVote }

To help you more, I would need the structure ;)

Thats the same code as mine exactly you didn't do anything but rephrase it. — Enzero, Aug 29 '14 at 16:24
I thought the v.Vote_HeaderId was the one you were having trouble with, so in which table is this vote_Id iin your sample — CheGueVerra, Aug 29 '14 at 16:27
It the 'From v In PlatoDB.Votes' it would be v.Id. The thing that is my problem is how do I get the min and the voteid. — Enzero, Aug 29 '14 at 16:58

score 0 · Accepted Answer · answered Sep 06 '14 at 14:15

I was able to get the proper MIN as the one that was being retrieved in the available answer and my question where returning the min in general but I needed the min for the specific ID.

Dim NextPreference = (From inheritedVote In oInheritableVotes
                      From v In PlatoDB.Votes
                      Where v.Vote_HeaderId = inheritedVote.Id AndAlso
                            v.Preference > 1 AndAlso
                            Not Officials.Select(Function(f) f.CandidateId).Contains(v.LinkId)
                      Group v By v.Vote_HeaderId Into Group
                      Select Vote_HeaderId, 
                             CandidateID = Group.Where(Function(f) f.Vote_HeaderId = Vote_HeaderId AndAlso
                                                                                           f.Preference = (Group.Where(Function(vh) vh.Vote_HeaderId = Vote_HeaderId).Min(Function(p) p.Preference))
                                                                                           ).Select(Function(c) c.LinkId).Single
                                                                                       ).ToList

Although I must still thank CheGueVerra for the help he provided nonetheless.

Entity Framework - Group and Min

2 Answers2