0

I have a query against a database which is meant to return a list of entities, which have first been grouped by PortfolioId and Instrument ID as shown below.

List<Holding>holdings= _opsRepo.TableHoldings
                    .GroupBy(th =>new { th.PortfolioId, th.InstrumentId })
                    .Select(g => g.OrderByDescending(tr => tr.TradeDate).First())
                    .ToList();

The Holding entity has the following:

public class Holding
    {
        public int Id { get; set; }
        public DateTime TradeDate { get; set; }
        public int PortfolioId { get; set; }
        public int InstrumentId { get; set; }
        public decimal MarketValue { get; set; }
        public decimal UnitPrice { get; set; }
        public decimal Nominal { get; set; }
        public DateTime LastUpdatedTime { get; set; }

        public Portfolio Portfolio { get; set; }
        public Instrument Instrument { get; set; }
    }

The issue that I'm getting is that the query is being evaluated locally, and cannot be executed on the server. The exact message is:

The LINQ expression 'GroupBy(new <>f__AnonymousType5`2(PortfolioId = [h].PortfolioId, InstrumentId = [h].InstrumentId), [h])' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'."}

We have the application configured such that it will throw an error and fail if a query is evaluated locally. Is there a way to re-write this query such that it will group and select the entities as I've written it?

A temporary solution that we've found is to first .ToList() the data set, and then run the grouping and select commands, but that would be bringing back a large amount of data as the table grows.

Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
Adhikar Hariram
  • 5
  • 1
  • Check [this my. answer](https://stackoverflow.com/a/69357710/10646316) It is how workaround such query. Anyway in EF Core 6 your query should work. – Svyatoslav Danyliv Feb 28 '22 at 10:53
  • Hi, I've tried the same bit of code, and still having the same errors: var portfolioAndInstrumentGrouping = _opsRepo.FpmHoldings.Select(h => new { h.PortfolioId, h.InstrumentId }).Distinct(); var query = from pi in portfolioAndInstrumentGrouping from ho in _opsRepo.FpmHoldings .Where(h => h.InstrumentId == pi.InstrumentId && h.PortfolioId == pi.PortfolioId) .OrderByDescending(tr => tr.TradeDate) .Take(1) select ho; var foo = query.ToList(); – Adhikar Hariram Feb 28 '22 at 11:51
  • I can't believe, which exception do you have? – Svyatoslav Danyliv Feb 28 '22 at 13:56
  • "Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'where (([h].InstrumentId == [pi].InstrumentId) AndAlso ([h].PortfolioId == [pi].PortfolioId))' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'." The issue is getting the code to run on the DB and not in code; we are on EF Core 2.2 – Adhikar Hariram Feb 28 '22 at 14:04
  • 1
    Oh man, always specify which EF Core version do you use. Unfortunately I cannot help here, EFC 2.2 is EOL. But if you are ok, I can post how to solve that by third party extension. – Svyatoslav Danyliv Feb 28 '22 at 14:10
  • I think the suggested workaround is to create a local list of desired `TableHoldings` IDs and then query to retrieve just those records. – NetMage Feb 28 '22 at 19:34
  • Apologies for not mentioning the version, still quite new to the field. Thanks for the help, if you could, please do send the 3rd party extension way to solve this just so that we can be aware of the option. – Adhikar Hariram Mar 01 '22 at 07:16

0 Answers0