Selecting all rows based on a distinct column in Entity Framework Core

asp.net-core c# entity-framework-core linq

Question

I have a table with some data which I want to select rows with unique dates using the .NET core entity framework. I want to return the results as a list of the unique rows. Here is the code.

public async Task<IEnumerable<Records>> GetUniqueRecordsByDate(int bId)
{
     var rowsToReturn = await _context.Records
                .Where(b => b.SId == bId)
                .Select(d => d.Date)
                .Distinct()
                .OrderBy(d => d)
                .ToListAsync();
     return rowsToReturn;
} 

I'm trying to apply the above code but I get this error

Cannot implicitly convert type 'System.Collections.Generic.List< System.Linq.IGrouping< System.DateTime, Test.API.Models.Records>>'to 'System.Collections.Generic.IEnumerable< Test.API.Models.Record>'. An explicit conversion exists (are you missing a cast?)

Is there any other way that I can implement to get my expectations or I'm I missing something in my code?

1
1
2/1/2019 6:21:35 AM

Accepted Answer

First make a DTO as follows:

public class RecordsByDateDTO
{
    public DateTime Date {get; set;}
    public List<Record> Records {get; set;}
}

To select rows with unique dates, you have to use .GroupBy and write your GetUniqueRecordsByDate method as follows:

public async Task<List<RecordsByDateDTO>> GetUniqueRecordsByDate(int bId)
{
     var recordsByDate = await _context.Records.Where(r => r.SId == bId).GroupBy(r => r.Date)
                              .Select(group => new RecordsByDateDTO
                              {
                                   Date =  group.Key,
                                   Records = group.ToList()
                              }).OrderBy(r => r. Date).ToListAsync();

    return recordsByDate;
} 
0
2/1/2019 6:27:11 AM

Popular Answer

var rowsToReturn = await _context.Records
            .Where(b => b.SId == bId)
            .Select(d => d.Date)
            .Distinct()
            .OrderBy(d => d)
            .AsEnumerable()
            .ToListAsync();
return rowsToReturn;

Use AsEnumerable() before ToListAsync(). It will work.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow