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

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'.
*/

CodePudding user response：

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

CodePudding user response：

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