Linq nested property

Question

I have this nested for loop which I'd like to elminate by modifying the original query:

foreach (var record in records)
{    
  foreach (var ap in record.approverList)
  {
    var approval = (from c in dataContext.approvals
                    where c.recordId == record.recordId
                    && c.personId == ap.personId
                    orderby c.modified descending
                    select c).FirstOrDefault();

    if (approval != null)
    {
      ap.vote = approval.vote;
    }

  }

}

Edit

Ideally, I want to modify the original query for records, which looks something like this:

var records = (
  from record in dataContext.Records
  where record.statusId == RecordStatus.Submitted
).Include(e => e.approverList.Select(d => d.approver))
.ToList()

Is there a navigation property on an approver for all their approvals? — Maess
– Maess, Commented May 12, 2014 at 17:29
@arserbin3 yes. sorry, missed that. personId should be ap.personId — Jacob Eggers
– Jacob Eggers, Commented May 12, 2014 at 17:33

Servy · Accepted Answer · 2014-05-12 18:11:56Z

1

So the real issue here is that you're executing a query for every single approval in every single record. You don't want to be doing that. What you want to be doing is joining your records on the database side with your approvals to create a query of pairs. Selecting out just the one matching approval from all of the joined approvals can be done with just a simple let.

Once you've queried all of the approvals and the approval list from the record that pairs with it, updating each item is simple enough.

var query = from record in records
            from recordApproval in record.approverList
            join approval in approvals
            on new { record.recordId, recordApproval.personId } equals
            new { approval.recordId, approval.personId }
            into approvalMatches
            where approvalMatches.Any()
            let approval = approvalMatches.OrderByDescending(a => a.modified)
                .FirstOrDefault()
            select new
            {
                recordApproval,
                approval,
            };

foreach (var result in query)
    result.recordApproval.vote = result.approval.vote;

Additionally it's worth noting that you shouldn't materialize records into a result set; you should keep it as an unmaterialized query so that the join can happen on the database site of things.

edited May 12, 2014 at 18:11

answered May 12, 2014 at 17:38

Servy

204k27 gold badges347 silver badges470 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Jacob Eggers Over a year ago

No, that's not quite right. It's not selecting the personId of approver when selecting the approvals.

Servy Over a year ago

@JacobEggers It was before you edited the question to change it...It's a pretty trivial alteration though

Jacob Eggers Over a year ago

That looks better, but I'm getting this

Error	2	The type of one of the expressions in the join clause is incorrect.  Type inference failed in the call to 'GroupJoin'.

, Also, anyway to modify the original query to for records that you know of? (I appologize, I am very new to linq queries)

Servy Over a year ago

@JacobEggers Then, as the errors indicate, the types of your columns don't match. Without knowing what your column types are, I couldn't say which ones don't match. You'll need to figure out why they are different, and how to convert them to be the same.

Jacob Eggers Over a year ago

recordId on approval is nullable. which makes new { record.recordId} equals new { approval.recordId } fail, even though ` record.recordId equals approval.recordId` works fine. (It shouldn't be nullable, but I can't change it because bad data causes runtime errors.)

|

arserbin3 · Accepted Answer · 2014-05-12 19:20:37Z

Looking at your code the desired logic seems to be:

For each approval person of each record, get their latest vote.

The clearest way (readable/maintainable) to accomplish that, and with only one call to the database, would look something like this:

Note: approvals.recordId should be foreign keyed to record.recordId, or the code is slightly more complex (would require an explicit join)

// get all the latest votes for submitted records
var votes = dataContext.approvals
    .Where(x => x.record.statusId == RecordStatus.Submitted)
    .OrderByDescending(x => x.modified) // only the latest vote counts, per person
    .GroupBy(x => new { x.recordID, x.personID })
    .Select(x => x.First())
    .ToList();

After that, you can utilize the list like so:

// this lists all the distinct records
var recordIds = votes.Select(x => x.recordId).Distinct();

// see the votes for each record
foreach(var recordId in recordIds)
{
     var thisRecordsVotes = votes.Where(x => x.recordId = recordID).ToList();
}

Collectives™ on Stack Overflow

Linq nested property

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related