How to get data from a table matched with a list in asp.net linq?

Question

I have a database table AdvisorComments where I have advisorID and studentID along with other columns. There can be multiple records for same advisorID and studentID. Also, an advisorID can have different studentIds and vice versa.

I have another table Students where I have stored only current advisorId which can be updated to different advisor.

I want to get all the students (current: Students table; previous: AdvisorComments table) for a particular advisor from Students table.

Here's what I have tried:

this.advisorID = 1;
var advisorComments = _context.AdvisorComments.Where(x => x.advisorID == this.advisorID).GroupBy(x=>x.studentID).Select(m => new { m.First().studentID}).ToList();

/* output:
0: studentID 1
1: studentID 4
*/
var studentList = _context.Students.Where(x => x => x.advisorID == this.advisorID || advisorComments.Any(a => a.studentID== x.studentID)).ToList();

/*output:
Getting error

System.InvalidOperationException: 'The LINQ expression 'a => a.studentID == EntityShaperExpression: 
    Project.Models.Student
    ValueBufferExpression: 
        ProjectionBindingExpression: EmptyProjectionMember
    IsNullable: False
.studentID' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.
*/

Can you show Student class? Does it have navigation property pointed to `AdvisorComments`? — Svyatoslav Danyliv, Oct 05 '22 at 15:10

score 1 · Accepted Answer · answered Oct 05 '22 at 21:07

1

Declared advisorComments as List type and it's working.

List<int> advisorComments = _context.AdvisorComments.Where(x => x.advisorID == this.advisorID).Select(m => studentID).Distinct().ToList();

answered Oct 05 '22 at 21:07

imn

23
5

Hogan · Answer 2 · 2022-10-05T15:24:29.667

0

this.advisorID = 1;
var advisorComments = _context.AdvisorComments
       .Where(x => x.advisorID == this.advisorID)
       .DistinctBy(z => z.studentID)
       .Select(m => new { m.studentID}).ToList();

/* output:
0: studentID 1
1: studentID 4
*/
var studentList = _context.Students
    .Where(x => x.advisorID == this.advisorID || 
           advisorComments.Any(z => z.studentID == x.studentID))
    .ToList();

DistinctBy is not standard linq but a helper function -- here is the code

public static IEnumerable<TSource> DistinctBy<TSource, TKey>
    (this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
{
    HashSet<TKey> seenKeys = new HashSet<TKey>();
    foreach (TSource element in source)
    {
        if (seenKeys.Add(keySelector(element)))
        {
            yield return element;
        }
    }
}

hat tip > https://stackoverflow.com/a/489421/215752

edited Oct 05 '22 at 15:24

answered Oct 04 '22 at 23:38

Hogan

69,564
10
76
117

z.studentID inside Distinct() showing error. CS1660: Can't override lambda expression to type 'IEualityComparer' because it's not a delegate type. – imn Oct 05 '22 at 14:14
please see the error in the question from the 2nd linq – imn Oct 05 '22 at 14:21
@imn -- yep. It should be DistinctBy not Distinct -- my bad – Hogan Oct 05 '22 at 15:18
yeah I did get it and tried using distinctBy, the first linq is generating correct result as it was doing with groupby. But my issue is with the 2nd linq. Please see the error in the question. Thanks – imn Oct 05 '22 at 19:07
ok what are the fields in Students? I can't help you if you don't show the data model – Hogan Oct 05 '22 at 20:01
Students[studentID(PK), name, email phone, advisorID]. AdvisorComments[advisorCommentID(PK), studentID, advisorID, comments] – imn Oct 05 '22 at 20:30
ok the error message is saying that studentID does not exist in one of those -- run it in the debugger and look to see which one. – Hogan Oct 05 '22 at 20:32
Solved the issue. Any() operator isn't working with the advisorComments as it's a var type. Declared it as List<> type and it works! – imn Oct 05 '22 at 21:05

How to get data from a table matched with a list in asp.net linq?

2 Answers2