Question 201330 in ASP.NET Question posted in
The official documentation can be found here.

Asp.net – How to get one of each type returned from an EF Core query

I’m trying to get a list of one of each of the type of notes of a client ordered by desc. In SQL Server, I wrote the following query

SELECT  
    note_id, st_id, note_type, signature_date 
FROM 
    (SELECT
         note_id, st_id, note_type, signature_date
         ROW_NUMBER() OVER (PARTITION BY note_type ORDER BY signature_dateDESC) AS row_num
     FROM 
         notes_log
     WHERE 
         cl_id = 1
         AND svce_type = 'service type 1'
         AND (note_type = 'note type 1' OR note_type = 'note type 2' OR note_type = 'note type 3')
     ) AS subquery
WHERE row_num = 1
ORDER BY signature_date DESC;

this query works fine but when converting the above query to Linq im getting some erorrs. Here’s what I did

 var lastNotes = await dbContext.Set<NotesLog>()
            .Where(s => s.ClientId == id && s.ServiceType == serviceType && (s.NoteType == "type 1" || s.NoteType == "type 2" || s.NoteType == "type 3"))
            .OrderByDescending(s => s.Signaturedate)
            .GroupBy(s => s.NoteType)
            .SelectMany(group => group.Take(1))
            .Select(s => new
            {
                s.NoteId,
                s.StateId,
                s.NoteType,
                s.Signaturedate
            })
            .ToListAsync();

I also tried swapping the GroupBy and Selectmany with .DistinctBy(s => s.NoteType)

both of them returned similar errors

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’.

I know I can first get all notes then query the list to get what I need (I found some such solutions online) it just doesn’t make much sense to me to do that. so is there a better way?

Thanks in advance

Tags:

2

Answers


  1. 0

    You have several alternatives:

    1. Since you already composed the SQL query you can execute it over the recordset and don’t crack your head with Linq.
    2. You can divide your Linq query and estimate the amount of the intermediate data. If it’s acceptable you can process by this way.
    3. I didn’t think too much on your Linq expression but try to use the ‘First’ method.

    Find more in Linq Query Group By and Selecting First Items

    Login or Signup to reply.
  2. 0

    AFAIK EF Core is not great in terms of translation of GroupBy, but you can try first projecting to anonymous type and then using First on group:

    var lastNotes = await dbContext.Set<NotesLog>()
    .Where(s => s.ClientId == id && s.ServiceType == serviceType && (s.NoteType == "type 1" || s.NoteType == "type 2" || s.NoteType == "type 3"))
    .Select(s => new
    {
        s.NoteId,
        s.StateId,
        s.NoteType,
        s.Signaturedate
    })
    .OrderByDescending(s => s.Signaturedate)
    .GroupBy(s => s.NoteType)
    .Select(group => group.First())
    .ToListAsync();

    Alternative approache:

    Move the SQL into user-defined table-valued function and map it to some method so it is supported by SQL

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search